一次神奇的MySQL死锁排查记录

背景

说起Mysql死锁,之前写过一次有关Mysql加锁的基本介绍,对于一些基本的Mysql锁或者死锁都有一个简单的认识,可以看下这篇文章为什么开发人员需要了解数据库锁。有了上面的经验之后,本以为对于死锁都能手到擒来,没想到再一个阳光明媚的下午报出了一个死锁,但是这一次却没想象的那么简单。

问题初现

在某天下午,突然系统报警,抛出个异常:

仔细一看好像是事务回滚异常,写着的是因为死锁回滚,原来是个死锁问题,由于我对Mysql锁还是有一定了解的,于是开始主动排查这个问题。

首先在数据库中查找Innodb Status,在Innodb Status中会记录上一次死锁的信息,输入下面命令:

SHOW ENGINE INNODB STATUS 

死锁信息如下,sql信息进行了简单处理:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-02-22 15:10:56 0x7eec2f468700
*** (1) TRANSACTION:
TRANSACTION 2660206487, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 31261312, OS thread handle 139554322093824, query id 11624975750 10.23.134.92 erp_crm__6f73 updating
/*id:3637ba36*/UPDATE tenant_config SET
 open_card_point = 0
 where tenant_id = 123
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1322 page no 534 n bits 960 index uidx_tenant of table `erp_crm_member_plan`.`tenant_config` trx id 2660206487 lock_mode X locks rec but not gap waiting
 *** (2) TRANSACTION:
TRANSACTION 2660206486, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 31261311, OS thread handle 139552870532864, query id 11624975758 10.23.134.92 erp_crm__6f73 updating
/*id:3637ba36*/UPDATE tenant_config SET
 open_card_point = 0
 where tenant_id = 123
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1322 page no 534 n bits 960 index uidx_tenant of table `erp_crm_member_plan`.`tenant_config` trx id 2660206486 lock mode S
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1322 page no 534 n bits 960 index uidx_tenant of table `erp_crm_member_plan`.`tenant_config` trx id 2660206486 lock_mode X locks rec but not gap waiting
 *** WE ROLL BACK TRANSACTION (1)
------------ 

给大家简单的分析解释一下这段死锁日志,事务1执行Update语句的时候需要获取uidx_tenant这个索引再where条件上的X锁(行锁),事务2执行同样的Update语句,也在uidx_tenant上面想要获取X锁(行锁),然后就出现了死锁,回滚了事务1。当时我就很懵逼,回想了一下死锁产生的必要条件:

1、互斥。

2、请求与保持条件。

3、不剥夺条件。

4、循环等待。

从日志上来看事务1和事务2都是取争夺同一行的行锁,和以往的互相循环争夺锁有点不同,怎么看都无法满足循环等待条件。经过同事提醒,既然从死锁日志中不能进行排查,那么就只能从业务代码和业务日志从排查。这段代码的逻辑如下:

public int saveTenantConfig(PoiContext poiContext, TenantConfigDO tenantConfig) {
 try {
  return tenantConfigMapper.saveTenantConfig(poiContext.getTenantId(), poiContext.getPoiId(), tenantConfig);
 } catch (DuplicateKeyException e) {
  LOGGER.warn("[saveTenantConfig] 主键冲突,更新该记录。context:{}, config:{}", poiContext, tenantConfig);
  return tenantConfigMapper.updateTenantConfig(poiContext.getTenantId(), tenantConfig);
 }
 } 

这段代码的意思是保存一个配置文件,如果发生了唯一索引冲突那么就会进行更新,当然这里可能写得不是很规范,其实可以用

insert into ...
on duplicate key update 

也可以达到同样的效果,但是就算用这个其实也会发生死锁。看了代码之后同事又给我发了当时业务日志,

可以看见这里有三条同时发生的日志,说明都发生了唯一索引冲突进入了更新的语句,然后发生的死锁。到这里答案终于稍微有点眉目了。

这个时候再看我们的表结构如下(做了简化处理):

CREATE TABLE `tenant_config` (
 `id` bigint(21) NOT NULL AUTO_INCREMENT,
 `tenant_id` int(11) NOT NULL,
 `open_card_point` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uidx_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT 

我们的tenant_id是用来做唯一索引,我们的插入和更新的where条件都是基于唯一索引来操作的。

UPDATE tenant_config SET
 open_card_point = 0
 where tenant_id = 123 

到了这里感觉插入的时候对唯一索引加锁有关系,接下来我们进行下一步的深入剖析。

深入剖析

上面我们说有三个事务进入update语句,为了简化说明这里我们只需要两个事务同时进入update语句即可,下面的表格展示了我们整个的发生过程:

小提示:S锁是共享锁,X锁是互斥锁。一般来说X锁和S,X锁都互斥,S锁和S锁不互斥。

我们从上面的流程中看见发生这个死锁的关键需要获取S锁,为什么我们再插入的时候需要获取S锁呢?因为我们需要检测唯一索引?在RR隔离级别下如果要读取那么就是当前读,那么其实就需要加上S锁。这里发现唯一键已经存在,这个时候执行update就会被两个事务的S锁互相阻塞,从而形成上面的循环等待条件。

小提示: 在MVCC中,当前读和快照读的区别:当前读每次需要加锁(可以使共享锁或者互斥锁)获取到最新的数据,而快照读是读取的是这个事务开始的时候那个快照,这个是通过undo log去进行实现的。

这个就是整个死锁的原因,能出现这种死锁的还有一个情况,就是同一时间来三个插入操作,其中先插入的那个事务如果最后回滚了,其余两个事务也会出现这种死锁。

解决方案

这里的核心问题是需要把S锁给干掉,这里有三个可供参考的解决方案:

  • 将RR隔离级别,降低成RC隔离级别。这里RC隔离级别会用快照读,从而不会加S锁。
  • 再插入的时候使用select * for update,加X锁,从而不会加S锁。
  • 可以提前加上分布式锁,可以利用Redis,或者ZK等等,分布式锁可以参考我的这篇文章。聊聊分布式锁

第一种方法不太现实,毕竟隔离级别不能轻易的修改。第三种方法又比较麻烦。所以第二种方法是我们最后确定的。

总结

说了这么多,最后做一个小小的总结吧。排查死锁这种问题的时候有时候光看死锁日志有时候会解决不了问题,需要结合整个的业务日志,代码以及表结构来进行分析,才能得到正确的结果。当然上面有一些数据库锁的基本知识如果不了解可以查看我的另一篇文章为什么开发人员需要了解数据库锁。

最后这篇文章被我收录于JGrowing-CaseStudy篇,一个全面,优秀,由社区一起共建的Java学习路线,如果您想参与开源项目的维护,可以一起共建,github地址为:https://github.com/javagrowing/JGrowing

好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对我们的支持。

(0)

相关推荐

  • 初学者从源码理解MySQL死锁问题

    通过好多个深夜艰难的单步调试,终于找到了一个理想的断点,可以看到大部分获取锁的过程 代码在lock0lock.c的static enum db_err lock_rec_lock() 函数中,这个函数会显示,获取锁的过程,以及获取锁成功与否. 场景1:通过主键进行删除 表结构 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (

  • MySQL 死锁套路:唯一索引 S 锁与 X 锁的爱恨情仇

    在初学者从源码理解MySQL死锁问题中介绍了使用调试 MySQL  源码的方式来查看死锁的过程,这篇文章来讲讲一个常见的案例. 毫不夸张的说,有一半以上的死锁问题由唯一索引贡献,后面介绍的很多死锁的问题都跟唯一索引有关.这次我们讲一段唯一索引 S 锁与 X 锁的爱恨情仇 我们来看一个简化过的例子 # 构造数据 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10), `level` int(11),

  • 详解MySQL(InnoDB)是如何处理死锁的

    一.什么是死锁 官方定义如下:两个事务都持有对方需要的锁,并且在等待对方释放,并且双方都不会释放自己的锁. 这个就好比你有一个人质,对方有一个人质,你们俩去谈判说换人.你让对面放人,对面让你放人. 二.为什么会形成死锁 看到这里,也许你会有这样的疑问,事务和谈判不一样,为什么事务不能使用完锁之后立马释放呢?居然还要操作完了之后一直持有锁?这就涉及到 MySQL 的并发控制了. MySQL的并发控制有两种方式,一个是 MVCC,一个是两阶段锁协议.那么为什么要并发控制呢?是因为多个用户同时操作 M

  • 一个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死锁套路

    前几篇文章介绍了用源码的方式来调试锁相关的信息,这里同样用这个工具来解决一个线上实际的死锁案例,也是我们介绍的第一个两条 SQL 就造成死锁的情况.因为线上的表结构比较复杂,做了一些简化以后如下 CREATE TABLE `t3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(5), `b` varchar(5), PRIMARY KEY (`id`), UNIQUE KEY `uk_a` (`a`), KEY `idx_b` (`b`)

  • 一次神奇的MySQL死锁排查记录

    背景 说起Mysql死锁,之前写过一次有关Mysql加锁的基本介绍,对于一些基本的Mysql锁或者死锁都有一个简单的认识,可以看下这篇文章为什么开发人员需要了解数据库锁.有了上面的经验之后,本以为对于死锁都能手到擒来,没想到再一个阳光明媚的下午报出了一个死锁,但是这一次却没想象的那么简单. 问题初现 在某天下午,突然系统报警,抛出个异常: 仔细一看好像是事务回滚异常,写着的是因为死锁回滚,原来是个死锁问题,由于我对Mysql锁还是有一定了解的,于是开始主动排查这个问题. 首先在数据库中查找Inn

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

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

  • MySQL删除了记录不生效的原因排查

    线上的一次MySQL事务问题记录 上周五进行了一个大表删除的操作,在删除的过程中,出现了一点小问题,白白花费了两个小时,我这里记录了一下大概的过程,废话不多说了,直接看过程吧. 当时想进行删除,先测试了一下删除语句的语法,删了一条试了一下,如下: mysql ::>>select min(id) from XXXX_user_login; +---------+ | min(id) | +---------+ | | +---------+ row in set (0.00 sec) mysq

  • mysql死锁和分库分表问题详解

    记录生产mysql的问题点. 业务场景与问题描述 请求一个外部接口时,每天的请求量在900万左右. 分为请求项目和回执这两个项目.请求是用来调用外部接口,回执是接收发送的接口. 在发送请求前会先插入数据库. 在请求后,如果接口返回调用失败,会更新数据库状态为失败. 如果发送成功,则会等待上游给出回执消息后,然后更新数据库状态. 而在生产运行过程中,半年出现过两次mysql导致的mq消费者堆积的问题. 问题分析 记录两次不同的原因导致的生产问题及原因分析. mysql死锁问题 查看mq聚合平台TP

  • update.where无索引导致MySQL死锁问题解决

    目录 引言 死锁的日志 分析日志 复盘 总结 引言 随着我被拉入一个新的群聊“生产环境死锁问题排查解决”,打破了午后的悠然惬意,点开群聊秒送了一个648超级大礼包(业务不正常,死锁异常日志输出),领导怒斥并要求赶紧排除解决并总结经验,刚好我略懂略懂一点MySQL锁知识,这不得秀一下自己的实力 死锁的日志 既然死锁已经发生,也完全不要慌啊,按我说着做,一定能找到原因然后解决 触发下面这条命令获取到线索 SHOW ENGINE INNODB STATUS; 执行后你会得到一段让人看了有点迷迷的死锁日

  • MySQL性能瓶颈排查定位实例详解

    本文实例讲述了MySQL性能瓶颈排查定位的方法.分享给大家供大家参考,具体如下: 导读 从一个现场说起,全程解析如何定位性能瓶颈. 排查过程 收到线上某业务后端的MySQL实例负载比较高的告警信息,于是登入服务器检查确认. 1. 首先我们进行OS层面的检查确认 登入服务器后,我们的目的是首先要确认当前到底是哪些进程引起的负载高,以及这些进程卡在什么地方,瓶颈是什么. 通常来说,服务器上最容易成为瓶颈的是磁盘I/O子系统,因为它的读写速度通常是最慢的.即便是现在的PCIe SSD,其随机I/O读写

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

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

  • 记一次tomcat进程cpu占用过高的问题排查记录

    本文主要记录一次tomcat进程,因TCP连接过多导致CPU占用过高的问题排查记录. 问题描述 linux系统下,一个tomcat web服务的cpu占用率非常高,top显示结果超过200%.请求无法响应.反复重启依然同一个现象. 问题排查 1.获取进程信息 通过jdk提供的jps命令可以快速查出jvm进程, jps pid 2.查看jstack信息 jstack pid 发现存在大量log4j线程block,处于waiting lock状态 org.apache.log4j.Category.

  • 通过唯一索引S锁与X锁来了解MySQL死锁套路

    在初学者从源码理解MySQL死锁问题中介绍了使用调试 MySQL  源码的方式来查看死锁的过程,这篇文章来讲讲一个常见的案例. 这次我们讲一段唯一索引 S 锁与 X 锁的爱恨情仇 我们来看一个简化过的例子 # 构造数据 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10), `level` int(11), PRIMARY KEY (`id`), UNIQUE KEY `uk_name` (`na

随机推荐