MySQL间隙锁深入分析

概念

什么是间隙锁？
MySQL的间隙锁（gap lock）是一种锁定相邻数据间隔的机制。

触发时机？
当使用SELECT…FOR UPDATE或UPDATE语句时，MySQL会获取一个范围锁，包括指定条件内的所有数据行，并且还会锁定这些数据行之间的间隔（即间隙）。

目的？
这样可以防止其他事务在这个范围内插入新的数据行，从而保证数据的一致性和完整性，避免幻读

分析

锁定相邻数据间隔？指的是表格数据的相邻？还是索引数据的相邻？
表格数据不一定有序，但索引默认情况下是升序排列的，正常情况下，锁定的应该是一个范围，也就是从小到大的范围，所以所指的应该是索引数据，如果是表格数据，那会出现一些情况，比如数据20的上一条数据是15，下一条数据是10，那锁定的是15-20-10？感觉就很怪是不是，下面我们来实战验证下

实战

创建表

CREATE TABLE `gap_lock_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7

准备数据

给age加了普通索引，主要是为了测试间隙锁，因为普通索引加的才是间隙锁，而主键和唯一索引加的是行锁
idx_age索引结构如下：

数据在索引中

窗口1：开启事务，update条件age=25的数据，触发间隙锁

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> update gap_lock_test set name='forlan' where age=25;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
3
4
5
6

窗口2：执行insert操作

mysql> insert into gap_lock_test(name,age) VALUES('forlan',25);
2013 - Lost connection to MySQL server during query
mysql> insert into gap_lock_test(name,age) VALUES('forlan',20);
2013 - Lost connection to MySQL server during query
mysql> insert into gap_lock_test(name,age) VALUES('forlan',30);
Query OK, 1 row affected (0.04 sec)
mysql> insert into gap_lock_test(name,age) VALUES('forlan',19);
Query OK, 1 row affected (0.03 sec)
1
2
3
4
5
6
7
8

注：上面没返回Query OK，表示被锁住了，等待执行

如果锁的是数据相邻间隔，那么锁住的范围应该是[int最小值，30)
如果锁的是索引数据相邻间隔，那么锁住的范围应该是[20，30)

通过验证，20被锁住了，但19和30都可以执行成功，没有被锁住，所以锁的是索引数据相邻间隔，前闭后开

数据在索引头

窗口1：开启事务，update条件age=20的数据，触发间隙锁

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> update gap_lock_test set name='forlan' where age=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
3
4
5
6

窗口2：执行insert操作

mysql> insert into gap_lock_test(name,age) VALUES('forlan',20);
2013 - Lost connection to MySQL server during query
mysql> insert into gap_lock_test(name,age) VALUES('forlan',25);
Query OK, 1 row affected (0.04 sec)
mysql> insert into gap_lock_test(name,age) VALUES('forlan',24);
2013 - Lost connection to MySQL server during query
mysql> insert into gap_lock_test(name,age) VALUES('forlan',0);
2013 - Lost connection to MySQL server during query
mysql> insert into gap_lock_test(name,age) VALUES('forlan',-1);
2013 - Lost connection to MySQL server during query
mysql> insert into gap_lock_test(name,age) VALUES('forlan',-2147483648);
2013 - Lost connection to MySQL server during query
mysql> insert into gap_lock_test(name,age) VALUES('forlan',-2147483649);
1264 - Out of range value for column 'age' at row 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14

上面这种就是锁定是数据在索引头，从结果可以看出，从int的最小值-2147483648开始锁住，锁定的范围为[-2147483648,25)
注：MySQL中int的范围为[-2147483648,2147483647]
同理，如果数据在索引尾，那么锁定的范围为[xxx,2147483647)

多个数据

窗口1：开启事务，update范围条件age的数据，触发间隙锁

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> update gap_lock_test set name='forlan' where age>=10 and age<=22;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
3
4
5
6

窗口2：执行insert操作

mysql> insert into gap_lock_test(name,age) VALUES('forlan',10);
2013 - Lost connection to MySQL server during query
mysql> insert into gap_lock_test(name,age) VALUES('forlan',22);
2013 - Lost connection to MySQL server during query
mysql> insert into gap_lock_test(name,age) VALUES('forlan',25);
Query OK, 1 row affected (0.04 sec)
mysql> insert into gap_lock_test(name,age) VALUES('forlan',24);
2013 - Lost connection to MySQL server during query
mysql> insert into gap_lock_test(name,age) VALUES('forlan',9);
2013 - Lost connection to MySQL server during query
mysql> insert into gap_lock_test(name,age) VALUES('forlan',-2147483648);
2013 - Lost connection to MySQL server during query
1
2
3
4
5
6
7
8
9
10
11
12

从结果可以看出，从int的最小值-2147483648开始锁住，锁定的范围为[-2147483648,25)
age>=10 and age<=22，目前最小索引值为20，所以还不满足<=10，所以从int最小值开始锁，满足age<=22的最近索引值为25，所以锁到25，不包括25；

总结，对于这种范围比较，锁住的是这个范围之外，离最近的两个索引值

不确定范围的数据

窗口1：开启事务，update条件in的数据

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> update gap_lock_test set name='forlan' where age in(10,22);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
1
2
3
4
5

窗口2：执行insert操作

mysql> insert into gap_lock_test(name,age) VALUES('forlan',29);
2013 - Lost connection to MySQL server during query
mysql> insert into gap_lock_test(name,age) VALUES('forlan',100);
2013 - Lost connection to MySQL server during query
1
2
3
4

对于in多个数，无法识别到具体的范围，所以锁的是全表了

总结

间隙锁，针对查询的条件，可以确定范围的条件，取范围之外离得最近的索引值加锁，锁的范围是[范围左边最近的索引值，范围右边最近的索引值），左闭右开