select for update引发死锁分析

古城微笑少年丶 2022-10-10 01:49 241阅读 0赞

# 问题复现 #

读写锁冲突

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

![在这里插入图片描述][20210625190954244.png]

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

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

![在这里插入图片描述][20210625191001403.png]

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

执行如下命令：

set @@autocommit=0;

SHOW VARIABLES like ‘%autocommit%’;

START transaction ;

SELECT \* FROM `xxl_job_user` where role =4 for update;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L051YW5fRmVuZw_size_16_color_FFFFFF_t_70]

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

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

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

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L051YW5fRmVuZw_size_16_color_FFFFFF_t_70 1]

特别注意：

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L051YW5fRmVuZw_size_16_color_FFFFFF_t_70 2]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L051YW5fRmVuZw_size_16_color_FFFFFF_t_70 3]

# 相关排查指令 #

\#查看当前在锁的事务  
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

![在这里插入图片描述][20210625191054813.png]

# 解决方法 #

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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L051YW5fRmVuZw_size_16_color_FFFFFF_t_70 4]

还是会阻塞  
![在这里插入图片描述][2021062519110944.png]

SET session TRANSACTION ISOLATION LEVEL Repeatable read;

参考：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html  
https://www.cnblogs.com/micrari/p/8029710.html

[20210625190954244.png]: /images/20221005/d7d6c1837b5c4c539d765ec42b0f4ad8.png
[20210625191001403.png]: /images/20221005/dd6b72aec2e54f3d86c334872e4ef913.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L051YW5fRmVuZw_size_16_color_FFFFFF_t_70]: /images/20221005/20fa96cc5e0b406dbc5a0ecbab35a12e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L051YW5fRmVuZw_size_16_color_FFFFFF_t_70 1]: /images/20221005/c5e5ba32f5664fa6b78ff4de64005ee8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L051YW5fRmVuZw_size_16_color_FFFFFF_t_70 2]: /images/20221005/88be3bc7c3a94ad5a7981f532b499583.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L051YW5fRmVuZw_size_16_color_FFFFFF_t_70 3]: /images/20221005/11464d185bb64076859c0e5ee320f739.png
[20210625191054813.png]: /images/20221005/ffeed86f6ad843fdaf7cb29c90c8b7cd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L051YW5fRmVuZw_size_16_color_FFFFFF_t_70 4]: /images/20221005/5ff49d4b2e4a4b4e94a073ff200dfdde.png
[2021062519110944.png]: /images/20221005/47622e9b32764ea999e21f088ffb63b3.png