MYSQL RC 和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鎖所以需要鎖定索引記錄之間的鎖，會多鎖定記錄
|
.png)
