MySQL间隙锁

gap lock,也就是间隙锁,是innodb行级锁的一种,其他的还有record lock, Next-KeyLocks。

  • 行锁(Record Lock):锁直接加在索引记录上面。
  • 间隙锁(Gap Lock):锁加在不存在的空闲空间,可以是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引之后的空间。
  • Next-Key Lock:行锁与间隙锁组合起来用就叫做Next-Key Lock。

什么时候会取得gap lock

这和隔离级别有关,只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或nextkey lock。
locking reads,UPDATE和DELETE时,除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁。即锁住其扫描的范围。

我们来看看实例:

1
2
3
4
5
mysql> CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`c1` int(11) DEFAULT NULL,
KEY `test_idx1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入3条记录,

1
2
3
4
mysql> 
insert into test values(10,1);
insert into test values(20,2);
insert into test values(30,3);

在REPEATABLE READ下,更新一条记录不提交,然后看看能阻塞另外的会话哪些操作。

SESSION 1:

SESSION 1中更新id=20的记录

1
2
3
4
5
6
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

SESSION 2:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(9,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(10,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(19,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test set c1=22 where id=20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(20,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(21,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(29,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(30,4);
Query OK, 1 row affected (0.01 sec)

SESSION 2中,执行插入操作,发现[10,30)范围不能插入数据。

SESSION 1:

1
2
3
mysql> begin;
mysql> update test set c1=4 where id=21;
Query OK, 0 rows affected (0.00 sec)

SESSION 2:

1
2
3
4
5
6
7
mysql> begin;
mysql> update test set c1=22 where id=20;
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(20,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test set c1=4 where id=30;
Query OK, 0 rows affected (0.00 sec)

这里呢,是去锁住了一条不存在的记录,他会锁住最近的[20,30)区间,然而与前一个例子不同的是,这里update(id = 20)是可以成功的,insert(id = 20)是不可以的。

如果SESSION 1的表扫描没有用到索引,那么gap或next-key锁住的范围是整个表,即任何值都不能插入。

作用

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作,以此来达到防止幻读的发生,所以间隙锁不分什么共享锁与排它锁。

既然知道有gap lock和next key lock,我们开发中就要避免收到其影响,在并发场景下,使用锁尽量走索引,甚至是唯一索引。当然,也可以关闭间隙锁,可以把隔离级别降为读已提交Read Committed,或者开启参数innodb_locks_unsafe_for_binlog。