RC级别下MySQL死锁问题的解决_F11 - 专业站长和开发者的学习网站

分享到

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

Mysql 来源：互联网作者：秩名发布时间：2022-03-03 17:22:51 人浏览

摘要

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

背景

在工作中碰到一次死锁问题，业务背景是在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下发的没有的记录就删除。至此死锁问题得到了解决。

您可能感兴趣的文章 :

原文链接 : https://blog.csdn.net/qq_42524262/article/details/123189023

Tag : mysql(199)

深入了解MySQL中的慢查询

一、什么是慢查询什么是MySQL慢查询呢？其实就是查询的SQL语句耗费较长的时间。具体耗费多久算慢查询呢？这其实因人而异，有些公司慢
MySQL中with rollup的用法及说明

MySQL with rollup的用法当需要对数据库数据进行分类统计的时候，往往会用上groupby进行分组。而在groupby后面还可以加入withcube和withrollup等关
mysql分组统计并求出百分比的方法

mysql分组统计并求出百分比 1、mysql 分组统计并列出百分比 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 SELECT point_id, pname_cn, play_
30种SQL语句优化的方法总结

1）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2）应尽量避免在 where 子句中使用!=或操作符
达梦数据库获取SQL实际执行计划的方法

环境说明：操作系统：银河麒麟V10 数据库：DM8 相关关键字：DM数据库、SQL实际执行计划一、set autotrace trace disql下执行set autotrace trace开启
MySQL数据库约束的介绍

基本介绍约束用于确保数据库的数据满足特定的商业规则在mysql中，约束包括：not null，unique，primary key，foreign key 和check5种 1.primary key(主键
MySQL索引的介绍

1. MySQL 索引的最左前缀原则左前缀原则是联合索引在使用时要遵循的原则，查询索引可以使用联合索引的一部分，但是必须从最左侧开始。
windows下Mysql多实例部署的操作方法

当存在多个项目的时候，需要同时部署时，且只有一台服务器时，哪么就需要部署Mysql多个实例，原理很简单，多个mysql服务运行使用不同的
MySQL客户端/服务器运行架构介绍

之前对MySQL的认知只限于会写些SQL，本篇开始进行对MySQL进行深入的学习，记录和整理下自己对MySQL不熟悉的地方。如果有需要可以关注我的
mysql8.0主从复制搭建与配置方案

mysql主从搭建环境：ubuntu20.04.1，mysql：8.0.22。主：192.168.87.3 备：192.168.87.6 安装数据库 1 2 3 sudo apt-get install mysql-server sudo apt-get install mysql