select for update引发死锁分析

问题复现

问题描述：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