mysql间隙锁（gap锁）初探

矫情吗；* 2023-01-20 08:05 1阅读 0赞

关于mysql的4种隔离级别，我们可以参考下面文章，已经介绍的很详细了：

[https://www.cnblogs.com/huanongying/p/7021555.html][https_www.cnblogs.com_huanongying_p_7021555.html]

[https://developer.aliyun.com/article/743691][https_developer.aliyun.com_article_743691]

那么在mysql默认的隔离级别，可重复读（通过SELECT @@tx\_isolation查询），探讨一下gap锁的情况：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70][]

1、准备测试数据：

CREATE TABLE `test_gapLock` (
      `id` int(11) DEFAULT NULL,
      `remark` int(11) DEFAULT NULL,
      KEY `testIndex` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    
    INSERT INTO `test_gapLock`(`id`, `remark`) VALUES (10, 1);
    INSERT INTO `test_gapLock`(`id`, `remark`) VALUES (20, 1);
    INSERT INTO `test_gapLock`(`id`, `remark`) VALUES (30, 1);
    INSERT INTO `test_gapLock`(`id`, `remark`) VALUES (40, 1);
    INSERT INTO `test_gapLock`(`id`, `remark`) VALUES (50, 1);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 1][]

2、将**两个session客户端**，都设置为不默认自动提交：

show variables like 'autocommit';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 2][]

set autocommit=OFF

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 3][]

再次查看，已经关闭自动提交：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 4][]

3、开始测试

在客户端A执行sql，先不手动提交，这时候犹豫没有id为11的记录，这时候在区间 \[10,20)，左闭右开加了一把gap锁，所有需要该区间的这把排它锁语句，均不会执行：

select * from test_gapLock where id = 11 for update;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 5][]

客户端B**分别依次**执行如下sql，等待锁超时：

insert into test_gapLock(id,remark) VALUES(10,55);
    insert into test_gapLock(id,remark) VALUES(12,55);
    insert into test_gapLock(id,remark) VALUES(19,55);
    insert into test_gapLock(id,remark) VALUES(20,55);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 6][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 7][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 8][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 9][]

4、在客户端A未提交之前，在客户端B运行如下sql，可以运行，说明gap锁与gap锁之间不是互斥的：

select * from test_gapLock where id = 12 for update;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 10][]

5、如果gap锁与gap锁直接是可以相互访问的，update排它锁与排它锁直接互斥，那么可能造成死锁问题：

如果客户端A获取到了\[10,20)之间的间隙锁，另一个客户端B也可以获取到\[10,20)之间的间隙锁。这时就可能会发生死锁问题，如下案例。  
客户端A获取到了\[10,20)之间的间隙锁不允许其他的DDL操作，在事务提交，间隙锁释放之前，客户端B也获取到了间隙锁\[10,20)；

客户端A，获取锁成功，执行后，没有手动提交：

select * from test_gapLock where id = 11 for update;
    
    insert into test_gapLock(id,remark) VALUES(15,88);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 11][]

客户端B：

select * from test_gapLock where id = 12 for update;
    
    insert into test_gapLock(id,remark) VALUES(16,88);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 12][]

6、show full processlist; 查看，有两个执行力900多秒的任务线程：

show full processlist;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 13][]

SELECT \* FROM information\_schema.INNODB\_TRX;发现两个事务（trx\_mysql\_thread\_id），执行了10多分钟了：

SELECT * FROM information_schema.INNODB_TRX;

![20210511212218146.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 14][]

7、可能是发生了死锁，kill掉死锁线程id（trx\_mysql\_thread\_id）：

kill  16279446;
    
    kill  16279444;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 15][]

8、再次查看，没有了死锁：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 16][]

9、关于gap锁的一个疑问：

现在数据库中数据状态：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 17][]

假设A和B两个客户端，现在依旧是默认不自动提交状态；

如果我在客户端A中加锁，update排它锁（这里不是gap锁），本来id=20的有两行记录，在未提交之前，显示影响2行，但是并没有真正的更新：

update test_gapLock set remark=666 where id=20;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 18][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 19][]

在客户端B中，执行插入操作，发现\[10,30)范围不能插入数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 20][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 21][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 22][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 23][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 24][]

所以，这是神马原因呢，我目前还不知道？

[https_www.cnblogs.com_huanongying_p_7021555.html]: https://www.cnblogs.com/huanongying/p/7021555.html
[https_developer.aliyun.com_article_743691]: https://developer.aliyun.com/article/743691
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70]: /images/20221021/b39a5f4bf5f84a99bb86095bef5a0b95.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 1]: /images/20221021/7fee47343ae349c5aa7146e80deb7f17.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 2]: /images/20221021/779e838950f04d4fa83fba85c6f006ea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 3]: /images/20221021/71ee827c06094e0aa6f32d16ccb03b19.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 4]: /images/20221021/f6320ff379fd4cd9afda18298c4f9c5b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 5]: /images/20221021/8675c8937f1144f9b6840935c880422b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 6]: /images/20221021/0c5f3bcd50e949fa96e166c4cb32637d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 7]: /images/20221021/be1f1152dcf444739c077ae79e73fdd1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 8]: /images/20221021/5f53f08320cd4711b4d47da875f884ee.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 9]: /images/20221021/a0076fd6abd14f1ea8ecb0ce2290ab76.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 10]: /images/20221021/7c6d884d670446b2a4a8e12fe093177b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 11]: /images/20221021/d7a5cef3278b4b9984dccc7f422b230e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 12]: /images/20221021/3f6496bfb81448bfa7df1304ba92cf47.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 13]: /images/20221021/95e50669f62044648fbf943957b2c505.png
[20210511212218146.png]: /images/20221021/04dc8927c5da475da0278c04cab405ba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 14]: /images/20221021/84c159f0f07245d59571ec522023048d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 15]: /images/20221021/ef655c79176c478f9322af7b63aea0b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 16]: /images/20221021/b35f2ed0415b43cba685775981fe3edc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 17]: /images/20221021/27fc1d67b14a45cb996358ae65ff728b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 18]: /images/20221021/ba4031d95bd94608a881bc295e7632c1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 19]: /images/20221021/6a2d71e15c9c4c5fbf88f459c9df3b74.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 20]: /images/20221021/176d1f3535cb4e3383d530f8b713948c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 21]: /images/20221021/ce30042e4aef41cdbf53b7b08413e3b4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 22]: /images/20221021/15a8d6c40b584d12b0bda4118b0c9a65.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 23]: /images/20221021/69e618890e3c4ee1bc22120e82d0a575.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70 24]: /images/20221021/f3b631f59e274639a26911469c390376.png