MYSQLRC和RR隔离级别差异性(有合适索引)

继续就上一篇比较RC 和RR隔离级别的差异性，有合适索引的比较：

1、隔离级别是RR，在t_test4表上面添加合适的索引即name列添加二级索引

会话158 查看隔离级别和在name 列创建索引

MySQL> show variables like '%iso%';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| tx_isolation | REPEATABLE-READ |

+---------------+-----------------+

1 row in set (0.01 sec)

mysql> select * from t_test4 order by name;

+------+-------+

| id | name |

+------+-------+

| 6 | hubei |

| 5 | wuhan |

| 2 | zhej |

| 4 | zhej |

| 4 | zhej |

| 4 | zhej |

| 5 | zhej |

+------+-------+

7 rows in set (0.00 sec)

mysql> create index idx_name on t_test4(name);

Query OK, 0 rows affected (0.16 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from t_test4;

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| t_test4 | 1 | idx_name | 1 | name | A | 7 | NULL | NULL | YES | BTREE | | |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.01 sec)

---查看UPDATE语句执行计划是否走了新创建的索引idx_name

mysql> explain update id=7 where name='hubei';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '7 where name='hubei'' at line 1

mysql> explain update t_test4 set id=7 where name='hubei';

+----+-------------+---------+-------+---------------+----------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------+-------+---------------+----------+---------+-------+------+-------------+

| 1 | SIMPLE | t_test4 | range | idx_name | idx_name | 23 | const | 1 | Using where |

+----+-------------+---------+-------+---------------+----------+---------+-------+------+-------------+

1 row in set (0.01 sec)

--开启事务

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update t_test4 set id=7 where name='hubei';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

会话159执行INSERT INTO SQL 等待超时报错

mysql> insert into t_test4 values(8,'hubei');

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

查看锁信息：可见158会话堵塞了159会话

mysql> SELECT

-> r.trx_id waiting_trx_id,

-> r.trx_mysql_thread_id waiting_thread,

-> r.trx_query waiting_query,

-> b.trx_id blocking_trx_id,

-> b.trx_mysql_thread_id blocking_thread,

-> b.trx_query blocking_query

-> FROM information_schema.innodb_lock_waits w

-> INNER JOIN information_schema.innodb_trx b

-> ON b.trx_id = w.blocking_trx_id

-> INNER JOIN information_schema.innodb_trx r

-> ON r.trx_id = w.requesting_trx_id;

+----------------+----------------+---------------------------------------+-----------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |

+----------------+----------------+---------------------------------------+-----------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 579773 | 159 | insert into t_test4 values(8,'hubei') | 579770 | 158 | SELECT

r.trx_id waiting_trx_id,

r.trx_mysql_thread_id waiting_thread,

r.trx_query waiting_query,

b.trx_id blocking_trx_id,

b.trx_mysql_thread_id blocking_thread,

b.trx_query blocking_query

FROM information_schema.innodb_lock_waits w

INNER JOIN information_schema.innodb_trx b

ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodb_trx r

ON r.trx_id = w.requesting_trx_id |

查看158会话事务信息：

mysql> select * from information_schema.innodb_trx\G

*************************** 1. row ***************************

trx_id: 579770

trx_state: RUNNING

trx_started: 2017-09-03 03:49:43

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 5

trx_mysql_thread_id: 158

trx_query: select * from information_schema.innodb_trx

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 4

trx_lock_memory_bytes: 1184

trx_rows_locked: 3--锁定了3条记录

trx_rows_modified: 1

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

1 row in set (0.00 sec)

原因是什么呢？是因为在RR隔离级别下，为了保证可重复读，MySQL引入了GAP锁，什么是GAP锁呢？先来看看定义：

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

gap是索引记录之间的锁，在第一个满足索引记录之前和最后一个满足索引记录之后。如下图（测试例子）这里重点仔细看哦

所以我插入hubei插入不了，另外

下面来看看GAP是否如上图所示，hubei之前无法插入数据,hubei和wuhan之间无法插入数据，wuhan之后可以正常插入：

mysql> insert into t_test4 values(8,'hu'); --失败

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into t_test4 values(8,'hubei');--失败

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into t_test4 values(8,'wuhan'); --成功

Query OK, 1 row affected (0.01 sec)

下面来看看RC隔离级别是否会出现这种情况（修改隔离级别之后记得退出重新登录）

会话1：

mysql> set global tx_isolation='READ-COMMITTED';

Query OK, 0 rows affected (0.00 sec)

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> update t_test4 set id=8 where name='hubei';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

会话2

mysql> insert into t_test4 values(8,'hu');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t_test4 values(8,'hubei');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_test4 values(8,'hubei1');

Query OK, 1 row affected (0.00 sec)
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 579785
                 trx_state: RUNNING
               trx_started: 2017-09-03 04:29:57
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 4
       trx_mysql_thread_id: 168
                 trx_query: select * from information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 3
     trx_lock_memory_bytes: 360
           trx_rows_locked: 2
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

可见RC隔离不存在这种情况。

小结：

隔离级别	无合适索引	有合适索引
RC	只锁定需要更新的记录	只锁定需要更新的记录
RR	会锁定所有的记录	由于GAP锁所以需要锁定索引记录之间的锁，会多锁定记录