初步理解MySQL的gap鎖

初步理解MySQL的gap鎖

初識MySQL的gap，覺得這個設計比較獨特，和其他數據庫的做法不太一樣，所以整理一個簡單的memo（雖然關於gap鎖，相關資料已經很多了）

1. 什麼是gap

A place in an InnoDB index data structure where new values could be inserted. 

說白了gap就是索引樹中插入新記錄的空隙。相應的gap lock就是加在gap上的鎖，還有一個next-key鎖，是記錄+記錄前面的gap的組合的鎖。

2. gap鎖或next-key鎖的作用

http://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order. 

簡單講就是防止幻讀。通過鎖阻止特定條件的新記錄的插入，因為插入時也要獲取gap鎖(Insert Intention Locks)。

3. 什麼時候會取得gap lock或nextkey lock

這和隔離級別有關,只在REPEATABLE READ或以上的隔離級別下的特定操作才會取得gap lock或nextkey lock。

http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

2.1 REPEATABLE READ

... For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. ...For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. 

locking reads，UPDATE和DELETE時，除了對唯一索引的唯一搜索外都會獲取gap鎖或next-key鎖。即鎖住其掃描的范圍。

下面對非唯一索引做個測試。

表定義如下:

mysql> show create table tb2;+-------+------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                   |+-------+------------------------------------------------------------------------------------------------------------------------------------------------+| tb2   | CREATE TABLE `tb2` (  `id` int(11) DEFAULT NULL,  `c1` int(11) DEFAULT NULL,  KEY `tb2_idx1` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |+-------+------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec) 

表中有3條記錄： 10,20,30。

mysql> select * from tb2;+------+------+| id   | c1   |+------+------+|   10 |    0 ||   20 |    0 ||   30 |    0 |+------+------+3 rows in set (0.01 sec) 

在REPEATABLE READ下，更新一條記錄不提交，然後看看能阻塞另外的會話哪些操作。

SESSION 1:

SESSION 1中更新id=20的記錄

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> update tb2 set c1=2 where id=20;Query OK, 1 row affected (0.04 sec)Rows matched: 1  Changed: 1  Warnings: 0 

SESSION 2:

SESSION 2中，執行插入操作，發現[10,30)范圍不能插入數據。

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> insert into tb2 values(9,4);Query OK, 1 row affected (0.00 sec)mysql> insert into tb2 values(10,4);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> insert into tb2 values(19,4);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> insert into tb2 values(20,4);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> insert into tb2 values(21,4);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> insert into tb2 values(29,4);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> insert into tb2 values(30,4);Query OK, 1 row affected (0.01 sec) 

對於更新操作，僅20這條記錄不能更新，因為更新操作不會去獲取gap鎖。

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> update tb2 set c1=4 where id=10;Query OK, 0 rows affected (0.00 sec)Rows matched: 1  Changed: 0  Warnings: 0mysql> update tb2 set c1=4 where id=20;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> update tb2 set c1=4 where id=30;Query OK, 0 rows affected (0.00 sec)Rows matched: 2  Changed: 0  Warnings: 0 

如果SESSION 1的表掃描沒有用到索引，那麼gap或next-key鎖住的范圍是整個表，即任何值都不能插入。

2.2 READ COMMITTED

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. 

只會鎖住已有記錄，不會加gap鎖。

2.3 SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. 

和REPEATABLE READ的主要區別在於把普通的SELECT變成SELECT ... LOCK IN SHARE MODE，即對普通的select都會獲取gap鎖或next-key鎖。

4. REPEATABLE READ和幻讀

在“consistent-read”時，REPEATABLE READ下看到是事務開始時的快照，即使其它事務插入了新行通常也是看不到的，所以在常見的場合可以避免幻讀。 但是，"locking read"或更新，刪除時是會看到已提交的修改的，包括新插入的行。

http://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html

If you want to see the “freshest” state of the database, use either the READ COMMITTED isolation level or a locking read: 

下面看一個例子

SESSION 1:

mysql> START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql> select id,c1 from tb1 where id=1;+----+------+| id | c1   |+----+------+|  1 |  100 |+----+------+1 row in set (0.00 sec) 

SESSION 2:

mysql> update tb1 set c1=101 where id =1;Query OK, 1 row affected (0.03 sec)Rows matched: 1  Changed: 1  Warnings: 0 

SESSION 1:

mysql> select id,c1 from tb1 where id=1 LOCK IN SHARE MODE;+----+------+| id | c1   |+----+------+|  1 |  101 |+----+------+1 row in set (0.00 sec)mysql> select id,c1 from tb1 where id=1;+----+------+| id | c1   |+----+------+|  1 |  100 |+----+------+1 row in set (0.00 sec)mysql> update tb1 set c1=c1+1000 where id=1;Query OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select id,c1 from tb1 where id=1;+----+------+| id | c1   |+----+------+|  1 | 1101 |+----+------+1 row in set (0.00 sec) 

上面update的行為違反了REPEATABLE READ的承諾，看到了事務開始後其它事務的並發更新。這對應用開發需要特別注意，這種情況下其它數據庫通常都是報錯的。

5. 其它

RR和RC相比還有一個重要的區別，RC下，掃描過但不匹配的記錄不會加鎖，或者是先加鎖再釋放，即semi-consistent read。但RR下掃描過記錄都要加鎖。這個差別對有全表掃描的更新的場景影響極大。詳細參考http://hedengcheng.com/?p=771，關於MySQL的加鎖處理，這篇文章講得很透徹！

6. 參考

• http://hedengcheng.com/?p=771
• http://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html
• http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
• http://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html
• http://blog.chinaunix.net/uid-20726500-id-3902528.html
• http://blog.itpub.net/22664653/viewspace-750824/
• http://www.bitscn.com/pdb/mysql/201405/227973.html