Mysql加锁过程详解（7）-初步理解MySQL的gap锁

刺骨的言语ヽ痛彻心扉 2022-05-30 04:36 200阅读 0赞

# 初步理解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的组合的锁。（r+(r+1)）

## 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.

翻译：

为了防止幻读，InnoDB使用了一种称为next-key锁定的算法，该算法结合了索引行锁和间隙锁定。 InnoDB在搜索时以这种方式执行行级锁定或扫描表索引，它会在遇到的索引记录上设置共享锁或排它锁。因此，行级锁实际上是索引记录锁。 另外，next-key锁定索引记录也会影响该索引记录之前的“缺口”。 也就是说，next-key锁定的是索引记录锁加上索引记录之前的间隔的间隙锁。 如果有一个会话索引中记录R上的共享或排它锁，另一个会话不能插入新的索引记录在索引顺序中R之前的间隙中。

简单讲就是防止幻读。通过锁阻止特定条件的新记录的插入，因为插入时也要获取gap锁(Insert Intention Locks)。

## 3. 什么时候会取得gap lock或next-key lock ##

这和隔离级别有关,只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或next-key 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.

翻译：

为了保证数据一致行的读取，与READ COMMITTED隔离级别有一个重要区别：  
同一事务中的所有一致性读，读取第一次读取建立的快照。...  
对于锁定读取（SELECT FOR FOR UPDATE或LOCK IN SHARE MODE），UPDATE和DELETE语句，  
锁定取决于该语句是否使用具有唯一搜索条件的唯一索引，或范围类型的搜索条件。 对于具有唯一搜索条件的唯一索引，  
InnoDB只锁定找到的索引记录，而不是之前的缺口。 对于其他搜索条件，InnoDB锁定扫描的索引范围，使用间隙锁或下一个锁来阻止通过其他事务回话插入范围内的记录。

locking reads，UPDATE和DELETE时，除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁。即锁住其扫描的范围。

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

表定义如下:

![复制代码][copycode.gif]

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)

![复制代码][copycode.gif]

注意id只是索引，不是主键

表中有3条记录： 10,20,30。

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

在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)范围不能插入数据。

![复制代码][copycode.gif]

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)

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

如果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:

![复制代码][copycode.gif]

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)

![复制代码][copycode.gif]

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:

![复制代码][copycode.gif]

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)

![复制代码][copycode.gif]

上面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

转自：http://blog.chinaunix.net/uid-20726500-id-5749804.html

[copycode.gif]: /images/20220530/57bd0bc837fc4f988845cdc0aa1af9eb.png