RC级别下MySQL死锁问题的解决

RC级别下MySQL死锁问题的解决 目录

• 背景
• 死锁分析
• 死锁解决

背景

在工作中碰到一次死锁问题，业务背景是在mq接收商品主数据时会更新商品其他数据，由于商品主数据和商品其他信息是一对多的关系，所以采用先删后增的方式，结果异常监管平台报出来死锁警告。

这是商品其他信息表，数据库隔离级别是RC,表有一个唯一联合索引，这个唯一索引就是引起死锁的关键。

死锁分析

下面是线上的一个死锁日志

2021-03-15 16:40:49 0x7f17e97ff700
*** (1) TRANSACTION:
TRANSACTION 2120576727, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 9384894, OS thread handle 139741055362816, query id 309547615 10.96.197.241 nsfbususr update
INSERT INTO MD_CMMDTY_OTHER19(             cmmdty_code, 			business_field,             business_field_desc,             keyword_code,             lastmodifier,             lastmodified 			) VALUES 			( 			'12256633711', 			'TAX_CODE', 			'1040201230000000000', 			'000001', 			'sys',             now() 			)  ON DUPLICATE KEY UPDATE              business_field = 'TAX_CODE',               business_field_desc = '1040201230000000000',               keyword_code = '000001',               lastmodifier = 'sys',              lastmodified = now()
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 306 page no 1310102 n bits 496 index idx_cmmdty_code_business_field of table `nsfbusprd`.`md_cmmdty_other19` trx id 2120576727 lock_mode X waiting

*** (2) TRANSACTION:
TRANSACTION 2120576728, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 9481029, OS thread handle 139740678452992, query id 309547616 10.98.61.213 nsfbususr update
INSERT INTO MD_CMMDTY_OTHER19(             cmmdty_code, 			business_field,             business_field_desc,             keyword_code,             lastmodifier,             lastmodified 			) VALUES 			( 			'12256633763', 			'TAX_CODE', 			'1040201230000000000', 			'000001', 			'sys',             now() 			)  ON DUPLICATE KEY UPDATE              business_field = 'TAX_CODE',               business_field_desc = '1040201230000000000',               keyword_code = '000001',               lastmodifier = 'sys',              lastmodified = now()
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 306 page no 1310102 n bits 496 index idx_cmmdty_code_business_field of table `nsfbusprd`.`md_cmmdty_other19` trx id 2120576728 lock_mode X locks rec but not gap  //持有记录锁
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 306 page no 1310102 n bits 496 index idx_cmmdty_code_business_field of table `nsfbusprd`.`md_cmmdty_other19` trx id 2120576728 lock_mode X waiting  //等待X锁
*** WE ROLL BACK TRANSACTION (2)

RC级别下对于唯一索引的插入只会锁定记录，是可以并发插入的，所以应该不是两个insert 语句并发产生的问题。

之后查看代码发现插入之前有一个delete *** 作，而且查看数据发现这两条数据是相邻的。

之后我在本地复现了一下整个过程。

查看加锁信息

这里当时有两个疑惑
1.为什么在RC级别下会有间隙锁
2.为什么两个事务会同时去等待12256633763记录上的X锁

对于第一个问题，网上很多博客视频都会说RC下间隙锁会失效，然后搬出官网的原话

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated).

但后面还有一句

In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

意思是RC情况下间隙锁会用于外键和唯一键检查。
而且就算通过innodb_locks_unsafe_for_binlog = 1配置将间隙锁关闭也不影响唯一索引对间隙锁的需要。
但这里又会有个疑问，为什么并发插入不加间隙锁，而先删后增就会加。
我看到一篇博客中的源码分析解释了这个问题

此刻又有个疑惑，为什么唯一冲突检查一定要在标有delete-marked的记录之后加间隙锁，我翻了很多博客资料，包括MySQL官方文档，都没有给出明确的解释。
我思考了很久，间隙锁是防止插入问题，那可能是为了在回滚时防止将其他事务的记录回滚掉，但这种情况不会只出现在唯一索引上，为什么只有在唯一校验时会加间隙锁。后来我又觉得应该是防止其他事务在区间插入 相同记录影响唯一检验，然而经过测试，在delete之后，其他事务插入根本无法获得当前记录的X锁，所以根本不存在对间隙锁的需要。
所以这个疑惑至今没有得到解决，如果有大佬知道的话欢迎在评论区评论。

至少现在我们从源码的层面知道了为什么在RC级别下为什么会有间隙锁存在。

现在还有第二个问题，为什么两个事务会同时等待12256633763记录上的X锁，在delete时，事务2已经获取了12256633763的记录锁，自身在获取X锁时应该不会发生冲突。

这里我也找到了加锁源码

按照源码理解，事务1需要锁住11-63记录的间隙以及63记录本身，相当于next-key,在对63加X锁时，由于事务2已经持有了63的记录锁，这两个锁的都属于排他锁但锁的模式不同，从加锁记录中也可以看出。所以事务1会创建一个锁对象，lock_mode X waiting放入请求队列中，等待事务2记录锁释放。
而事务2在对63创建X锁时，发现已经有一个该锁的请求存在队列中，所以也会创建一个锁对象lock_mode X waiting放入请求队列中，而这时触发死锁检查发现有两个事务同时等待同一个锁，发生死锁，默认回滚后请求的事务。

死锁解决

到这里疑惑基本都解决了，而引起该死锁的原因就是先删后增的 *** 作。之后我们优化了代码逻辑，因为我们每次都是下发的全量数据，所以mq下发的记录数据库中已存在的就更新，没有的就新增，而数据库中有的mq下发的没有的记录就删除。至此死锁问题得到了解决。

到此这篇关于RC级别下MySQL死锁问题的解决的文章就介绍到这了,更多相关RC级别下MySQL死锁内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！