Mysql加锁过程详解（6）-初步理解MySQL的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锁的作用

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锁。即锁住其扫描的范围。

 

下面对非唯一索引做一个测试：

创建表 tb2 ，注意，id 不是主键，是非唯一索引。

mysql> CREATE TABLE `tb2` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `c1` int(11) DEFAULT NULL,
    ->   KEY `tb2_idx1` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 表中有3条记录：

mysql> select * from tb2;
+------+------+
| id   | c1   |
+------+------+
|   10 |    0 |
|   20 |    0 |
|   30 |    0 |
+------+------+

 

开启两个事务 t1 和 t2，

在t1中，更新 id=20 的记录

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

 

 在t2中，执行插入操作，发现[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)

 

 再看看更新操作

mysql> update tb2 set c1=4 where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  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, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

可以看到，update除了id=20的记录以外，其他的update操作都成功了。

结论：如果 t1 的表扫描没有用到索引，那么gap或next-key锁住的范围是该记录以及该记录前后的gap（包括该记录前一条记录，但不包括后一条记录），这个范围内不可以插入值，但可以更新值（除了被锁住的那条记录）。

 

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

3. REPEATABLE READ和幻读

在“consistent-read”时，REPEATABLE READ下看到是事务开始时的快照，即使其它事务插入了新行通常也是看不到的，所以在常见的场合可以避免幻读。 但是，"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 |
+----+------+
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 |
+----+------+
row in set (0.00 sec)

mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 |  100 |
+----+------+
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 |
+----+------+
row in set (0.00 sec)

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

5. 其它

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