关于mysql的4种隔离级别,我们可以参考下面文章,已经介绍的很详细了:
https://www.cnblogs.com/huanongying/p/7021555.html
https://developer.aliyun.com/article/743691
那么在mysql默认的隔离级别,可重复读(通过SELECT @@tx_isolation查询),探讨一下gap锁的情况:
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);
2、将两个session客户端,都设置为不默认自动提交:
show variables like 'autocommit';
set autocommit=OFF
再次查看,已经关闭自动提交:
3、开始测试
在客户端A执行sql,先不手动提交,这时候犹豫没有id为11的记录,这时候在区间 [10,20),左闭右开加了一把gap锁,所有需要该区间的这把排它锁语句,均不会执行:
select * from test_gapLock where id = 11 for update;
客户端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);
4、在客户端A未提交之前,在客户端B运行如下sql,可以运行,说明gap锁与gap锁之间不是互斥的:
select * from test_gapLock where id = 12 for update;
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);
客户端B:
select * from test_gapLock where id = 12 for update;
insert into test_gapLock(id,remark) VALUES(16,88);
6、show full processlist; 查看,有两个执行力900多秒的任务线程:
show full processlist;
SELECT * FROM information_schema.INNODB_TRX;发现两个事务(trx_mysql_thread_id),执行了10多分钟了:
SELECT * FROM information_schema.INNODB_TRX;
7、可能是发生了死锁,kill掉死锁线程id(trx_mysql_thread_id):
kill 16279446;
kill 16279444;
8、再次查看,没有了死锁:
9、关于gap锁的一个疑问:
现在数据库中数据状态:
假设A和B两个客户端,现在依旧是默认不自动提交状态;
如果我在客户端A中加锁,update排它锁(这里不是gap锁),本来id=20的有两行记录,在未提交之前,显示影响2行,但是并没有真正的更新:
update test_gapLock set remark=666 where id=20;
在客户端B中,执行插入操作,发现[10,30)范围不能插入数据:
所以,这是神马原因呢,我目前还不知道?