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死锁内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • Mysql使用kill命令解决死锁问题(杀死某条正在执行的sql语句)

    在使用mysql运行某些语句时,会因数据量太大而导致死锁,没有反映.这个时候,就需要kill掉某个正在消耗资源的query语句即可, KILL命令的语法格式如下: KILL [CONNECTION | QUERY] thread_id 每个与mysqld的连接都在一个独立的线程里运行,您可以使用SHOW PROCESSLIST语句查看哪些线程正在运行,并使用KILL thread_id语句终止一个线程. KILL允许自选的CONNECTION或QUERY修改符:KILL CONNECTION与不

  • Mysql 数据库死锁过程分析(select for update)

    近期有一个业务需求,多台机器需要同时从Mysql一个表里查询数据并做后续业务逻辑,为了防止多台机器同时拿到一样的数据,每台机器需要在获取时锁住获取数据的数据段,保证多台机器不拿到相同的数据. 我们Mysql的存储引擎是innodb,支持行锁.解决同时拿数据的方法有很多,为了更加简单,不增加其他表和服务的情况下,我们考虑采用select... for update的方式,这样X锁锁住查询的数据段,表里其他数据没有锁,其他业务逻辑还是可以操作. 这样一台服务器比如select .. for upda

  • 一次Mysql死锁排查过程的全纪录

    前言 之前接触到的数据库死锁,都是批量更新时加锁顺序不一致而导致的死锁,但是上周却遇到了一个很难理解的死锁.借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景.在多方调研以及和同事们的讨论下终于发现了这个死锁问题的成因,收获颇多.虽然是后端程序员,我们不需要像DBA一样深入地去分析与锁相关的源码,但是如果我们能够掌握基本的死锁排查方法,对我们的日常开发还是大有裨益的. PS:本文不会介绍死锁的基本知识,mysql的加锁原理可以参考本文的参考资料提供的链接. 死锁起因 先介绍一下数

  • MySQL死锁问题分析及解决方法实例详解

    MySQL死锁问题是很多程序员在项目开发中常遇到的问题,现就MySQL死锁及解决方法详解如下: 1.MySQL常用存储引擎的锁机制 MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-level locking)或表级锁,默认为页面锁 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 2.各种锁特点 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低 行级锁:开销大,加锁慢;

  • 一个mysql死锁场景实例分析

    前言 最近遇到一个mysql在RR级别下的死锁问题,感觉有点意思,研究了一下,做个记录. 涉及知识点:共享锁.排他锁.意向锁.间隙锁.插入意向锁.锁等待队列 场景 隔离级别:Repeatable-Read 表结构如下 create table t ( id int not null primary key AUTO_INCREMENT, a int not null default 0, b varchar(10) not null default '', c varchar(10) not n

  • Mysql 行级锁的使用及死锁的预防方案

    一.前言 mysql的InnoDB,支持事务和行级锁,可以使用行锁来处理用户提现等业务.使用mysql锁的时候有时候会出现死锁,要做好死锁的预防. 二.MySQL行级锁 行级锁又分共享锁和排他锁. 共享锁: 名词解释:共享锁又叫做读锁,所有的事务只能对其进行读操作不能写操作,加上共享锁后其他事务不能再加排他锁了只能加行级锁. 用法: SELECT `id` FROM table WHERE id in(1,2) LOCK IN SHARE MODE 结果集的数据都会加共享锁 排他锁: 名词解释:

  • mysql 数据库死锁原因及解决办法

    死锁(Deadlock) 所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程.由于资源占用是互斥的,当某个进程提出申请资源后,使得有关进程在无外力协助下,永远分配不到必需的资源而无法继续运行,这就产生了一种特殊现象死锁. 一种情形,此时执行程序中两个或多个线程发生永久堵塞(等待),每个线程都在等待被其他线程占用并堵塞了的资源.例如,如果线程A锁住了记

  • MySQL死锁套路之唯一索引下批量插入顺序不一致

    前言 死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况.为了方便演示,把批量插入改写为了多条 insert. 先来做几个小实验,简化的表结构如下 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(5), `b` varchar(5), PRIMARY KEY (`id`), UNIQUE KEY `uk_name` (`a`,`b`) ); 实验1: 在记录不存在的情况下,

  • MySQL Innodb表导致死锁日志情况分析与归纳

    案例描述在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志.两个sql语句如下:(1)insert into backup_table select * from source_table(2)DELETE FROM source_table WHERE Id>5 AND titleWeight<32768 AND joinTime<'$daysago_1week'teamUser表的表结构如下:PRIMARY

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

    目录 背景 死锁分析 死锁解决 背景 在工作中碰到一次死锁问题,业务背景是在mq接收商品主数据时会更新商品其他数据,由于商品主数据和商品其他信息是一对多的关系,所以采用先删后增的方式,结果异常监管平台报出来死锁警告. 这是商品其他信息表,数据库隔离级别是RC,表有一个唯一联合索引,这个唯一索引就是引起死锁的关键. 死锁分析 下面是线上的一个死锁日志 2021-03-15 16:40:49 0x7f17e97ff700 *** (1) TRANSACTION: TRANSACTION 212057

  • Windows下MySql错误代码1045的解决方法

    复制代码 代码如下: 错误代码 1045 Access denied for user 'root'@'localhost' (using password:YES) 解决办法是重新设置root用户密码,在Windows平台下操作步骤如下: 1.以系统管理员身份登录到系统: 2.如果MySQL服务器正在运行,停止它.       如果是作为Windows服务运行的服务器,进入服务管理器:开始菜单->控制面板->管理工具->服务       如果服务器不是作为服务而运行的,可能需要使用任务

  • linux下mysql忘记密码的解决方法

    问题如下:输入命令mysql  -uroot  -p,但是密码忘了. 第一步:停止mysql服务 网上有给出方法打开/etc/my.cnf,在里面修改,但是我压根没在这个目录下找到该文件.可能版本原因吧! 输入命令停止mysql服务:/etc/init.d/mysql stop 第二步:安全启动mysql,且跳过授权表:mysqld_safe --user=mysql --skip-grant-tables --skip-networking & 第三步:重新设置mysql密码 输入:mysql

  • Linux下MySql 1036 错误码解决(1036: Table 'xxxx' is read only)

    这两天在进行网站搬家,这次网站搬家采用直接打包mysql数据库和网页文件的形式进行迁移,上传好mysql data目录里面的网站数据库至VPS上mysql存放数据库的目录里面,解压就行.我的VPS存放数据库的路径是 /usr/local/mysql/var. 上传好网站数据,解压,配置好数据库链接参数就行,网站就能正常连接上了,我本以为这已 经是顺利迁移完成了,但后来操作的时候,发现只能读取数据库的内容,不能更改写入任何信息,提示#1036 – Table '* ' is read only (

  • CentOS 7下MySQL服务启动失败的快速解决方法

    今天,启动MySQL服务器失败,如下所示: [root@spark01 ~]# /etc/init.d/mysqld start Starting mysqld (via systemctl): Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe&qu

  • Windows下MySQL服务无法停止和删除的解决办法

    我在 Windows 操作系统上,使用解压压缩包的方式安装 MySQL.这是安装的具体方法:点击这里.在执行如下命令: mysqld --install MySQL --defaults-file=[ini配置文件绝对路径] 在执行上面那个命令的时候,输入了错误的配置文件路径.虽然系统可能会返回 success .并且命令 mysqld --initialize 也可以执行并生成数据文件.但是当我执行 net start mysql 的时候,系统会一直提示服务正在启动.原因是我的配置文件路径错误

  • ubuntu下磁盘空间不足导致mysql无法启动的解决方法

    前言 最近在数据库的一张表添加两个字段,后来提示什么磁盘空间不足什么什么的,后来数据库就断开连接了,之后就一直连接不上去后来,最后经过思考终于解决了这个问题,这一经历下来真是心惊胆战,本文作为记录一下磁盘空间不足导致的 mysql 无法启动的解决办法. 方法如下 操作系统:ubuntu,磁盘空间不足导致的 mysql 无法启动,会造成如下问题: root@iZ28z558vv0Z:/etc/mysql# mysql -u root -p Enter password: ERROR 2002 (H

  • MAC下MYSQL数据库密码忘记的解决办法

    Mac操作系统下MYSQL数据库密码忘记的快速解决办法 1. 在系统偏好 中,中止MYSQL服务.: 2. cd/usr/local/mysql-...../bin sudo ./mysqld_safe--skip-grant-tables 3. 登录MySQL: mysql 4. 置空root用户的密码: mysql> update mysql.user set password='' whereUser='root'; mysql> flush privileges; mysql>

随机推荐