问题复现
问题描述:4场直播同时准备作业的时候,发现有作业准备失败。查看这块逻辑,发现准备作业时会先查询作业是否有这条记录,这里添加了写锁。
而作业准备前是没有记录的,所以这里就会造成间隙锁。
间隙锁
先营造测试环境,给测试表role字段添加唯一索引。
假设现在只有3条数据。
接着执行如下命令:
#设置手动提交
set @@autocommit=0;
#查看是否生效
SHOW VARIABLES like '%autocommit%';
#开启事务
START transaction ;
SELECT * FROM `xxl_job_user` where role =4 for update;
然后通过role查询等于4的,这里查不到数据库,就会锁住role>=4之后的所有数据。
但是另外的线程还是可以访问表的,在这时插入role=4的就会阻塞。
间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作,以此来达到防止幻读的发生,所以间隙锁不分什么共享锁与排它锁。
特别注意:
因为是间隙锁,假设我现在插入一条数据,role=8;
接着执行
SELECT * FROM `xxl_job_user` where role =4 for update;
之后插入role>8的就会被成功。因为这里间隙锁只会在4~8之间生效。
间隙锁扩展
解决方法
第一步:杀死进程id(就是 命令的)
获取trx_mysql_thread_id。
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
kill 线程ID
第二步:引入分布式锁解决。
其他解决方式
修改事务隔离策略。(没用)
show variables like 'tx_isolation';
SET session TRANSACTION ISOLATION LEVEL Read committed;
这样间隙锁就失效了,从而升级为表锁。会阻塞所有的请求。
还是会阻塞
相关排查指令
#查看当前在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
#查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
#查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
show OPEN TABLES where In_use > 0;
SHOW ENGINE INNODB STATUS
参考:https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
https://www.cnblogs.com/micrari/p/8029710.html