MySQL Gap Lock问题

文章来源：http://blog.chinaunix.net/uid-20726500-id-5749804.html
作者：@小桥河西

初识MySQL的gap，觉得这个设计比较独特，和其他数据库的做法不太一样，所以整理一个简单的memo（虽然关于gap锁，相关资料已经很多了）

一、什么是gap

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

说白了gap就是索引树中插入新记录的空隙。相应的gap lock就是加在gap上的锁，还有一个next-key锁，是记录+记录前面的gap的组合的锁。

二、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)。

三、什么时候会取得gap lock或nextkey lock

这和隔离级别有关,只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或nextkey lock。

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

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 transaction
mysql> insert into tb2 values(19,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(20,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(21,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(29,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> 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: 0

mysql> update tb2 set c1=4 where id=20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> 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锁住的范围是整个表，即任何值都不能插入。

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锁。

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锁。

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: 0

mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 | 1101 |
+----+------+
1 row in set (0.00 sec)

上面update的行为违反了REPEATABLE READ的承诺，看到了事务开始后其它事务的并发更新。这对应用开发需要特别注意，这种情况下其它数据库通常都是报错的。

其它

RR和RC相比还有一个重要的区别，RC下，扫描过但不匹配的记录不会加锁，或者是先加锁再释放，即semi-consistent read。但RR下扫描过记录都要加锁。这个差别对有全表扫描的更新的场景影响极大。详细参考http://hedengcheng.com/?p=771，关于MySQL的加锁处理，这篇文章讲得很透彻！

参考

• 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