问题复现

读写锁冲突

场景：4场直播同时准备作业。首先查询作业是否有这条记录。这里查询后添加写锁。

作业准备前是没有记录的，所以这里就会造成间隙锁。

假设现在只有3条数据。然后通过role查询等于4的。这里查不到数据库，就会锁住3之后的所有数据。

先营造测试环境，给xxl_job_user表role字段添加唯一索引。

执行如下命令：

set @@autocommit=0;

SHOW VARIABLES like ‘%autocommit%’;

START transaction ;

SELECT * FROM xxl_job_user where role =4 for update;

这里就锁住了role>=4之后的所有数据。

但是另外的线程还是可以访问表的。

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。

但是，在这时插入role=4的就会阻塞。

特别注意：

因为是间隙锁，假设我现在插入一条数据，role=8；

在这里执行一条sql，插入role>8的就会被成功。因为这里间隙锁只会在4-8之间生效。

相关排查指令

#查看当前在锁的事务

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

解决方法

1：杀死进程id（就是 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 命令的trx_mysql_thread_id列）

kill 线程ID

2：引入分布式锁解决。

3：修改事务隔离策略。（没用）

这样间隙锁就失效了，从而升级为表锁。会阻塞所有的请求。

show variables like ‘tx_isolation’;

SET session TRANSACTION ISOLATION LEVEL Read committed;

还是会阻塞

SET session TRANSACTION ISOLATION LEVEL Repeatable read;