继续就上一篇比较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: 0Duplicates: 0Warnings: 0mysql> 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_namemysql> 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 1mysql> explain update t_test4 setid=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 setid=7 where name='hubei';Query OK, 1 row affected (0.00 sec)Rows matched: 1Changed: 1Warnings: 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 | SELECTr.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_queryFROM information_schema.innodb_lock_waits wINNER JOIN information_schema.innodb_trx bON b.trx_id = w.blocking_trx_idINNER JOIN information_schema.innodb_trx rON r.trx_id = w.requesting_trx_id |查看158会话事务信息：mysql> select * from information_schema.innodb_trx\G*************************** 1. row ***************************trx_id: 579770trx_state: RUNNINGtrx_started: 2017-09-03 03:49:43trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 5trx_mysql_thread_id: 158trx_query: select * from information_schema.innodb_trxtrx_operation_state: NULLtrx_tables_in_use: 0trx_tables_locked: 0trx_lock_structs: 4trx_lock_memory_bytes: 1184trx_rows_locked: 3--锁定了3条记录trx_rows_modified: 1trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 10000trx_is_read_only: 0trx_autocommit_non_locking: 01 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 of15into columnt.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 transactionmysql> insert into t_test4 values(8,'hubei');--失败ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> 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 namesYou can turn off this feature to get a quicker startup with -A
Database changedmysql> 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: 1Changed: 1Warnings: 0

会话2mysql>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锁所以需要锁定索引记录之间的锁，会多锁定记录