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

前言

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

PS:本文不会介绍死锁的基本知识,mysql的加锁原理可以参考本文的参考资料提供的链接。

死锁起因

先介绍一下数据库和表情况,因为涉及到公司内部真是的数据,所以以下都做了模拟,不会影响具体的分析。

我们采用的是5.5版本的mysql数据库,事务隔离级别是默认的RR(Repeatable-Read),采用innodb引擎。假设存在test表:

CREATE TABLE `test` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `a` int(11) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;

表的结构很简单,一个主键id,另一个唯一索引a。表里的数据如下:

mysql> select * from test;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 4 | 4 |
+----+------+
3 rows in set (0.00 sec)

出现死锁的操作如下:

步骤 事务1 事务2
1 begin
2 delete from test where a = 2;
3 begin
4 delete from test where a = 2; (事务1卡住)
5 提示出现死锁:ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction insert into test (id, a) values (10, 2);

然后我们可以通过SHOW ENGINE INNODB STATUS;来查看死锁日志:

------------------------
LATEST DETECTED DEADLOCK
------------------------
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** WE ROLL BACK TRANSACTION (1)

分析

阅读死锁日志

遇到死锁,第一步就是阅读死锁日志。死锁日志通常分为两部分,上半部分说明了事务1在等待什么锁:

170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;

从日志里我们可以看到事务1当前正在执行delete from test where a = 2,该条语句正在申请索引a的X锁,所以提示lock_mode X waiting

然后日志的下半部分说明了事务2当前持有的锁以及等待的锁:

*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;

从日志的HOLDS THE LOCKS(S)块中我们可以看到事务2持有索引a的X锁,并且是记录锁(Record Lock)。该锁是通过事务2在步骤2执行的delete语句申请的。由于是RR隔离模式下的基于唯一索引的等值查询(Where a = 2),所以会申请一个记录锁,而非next-key锁。

从日志的WAITING FOR THIS LOCK TO BE GRANTED块中我们可以看到事务2正在申请S锁,也就是共享锁。该锁是insert into test (id,a) values (10,2)语句申请的。insert语句在普通情况下是会申请排他锁,也就是X锁,但是这里出现了S锁。这是因为a字段是一个唯一索引,所以insert语句会在插入前进行一次duplicate key的检查,为了使这次检查成功,需要申请S锁防止其他事务对a字段进行修改。

那么为什么该S锁会失败呢?这是对同一个字段的锁的申请是需要排队的。S锁前面还有一个未申请成功的X锁,所以S锁必须等待,所以形成了循环等待,死锁出现了。

通过阅读死锁日志,我们可以清楚地知道两个事务形成了怎样的循环等待,再加以分析,就可以逆向推断出循环等待的成因,也就是死锁形成的原因。

死锁形成流程图

为了让大家更好地理解死锁形成的原因,我们再通过表格的形式阐述死锁形成的流程:

步骤 事务1 事务2
1 begin
2 delete from test where a = 2; 执行成功,事务2占有a=2下的X锁,类型为记录锁。
3 begin
4 delete from test where a = 2; 事务1希望申请a=2下的X锁,但是由于事务2已经申请了一把X锁,两把X锁互斥,所以X锁申请进入锁请求队列。
5 出现死锁,事务1权重较小,所以被选择回滚(成为牺牲品)。 insert into test (id, a) values (10, 2); 由于a字段建立了唯一索引,所以需要申请S锁以便检查duplicate key,由于插入的a的值还是2,所以排在X锁后面。但是前面的X锁的申请只有在事务2commit或者rollback之后才能成功,此时形成了循环等待,死锁产生。

拓展

在排查死锁的过程中,有个同事还发现了上述场景会产生另一种死锁,该场景无法通过手工复现,只有高并发场景下才有可能复现。

该死锁对应的日志这里就不贴出了,与上一个死锁的核心差别是事务2等待的锁从S锁换成了X锁,也就是lock_mode X locks gap before rec insert intention waiting

我们还是通过表格来详细说明该死锁产生的流程:

步骤 事务1 事务2
1 begin
2 delete from test where a = 2; 执行成功,事务2占有a=2下的X锁,类型为记录锁。
3 begin
4 【insert第1阶段】insert into test (id, a) values (10, 2); 事务2申请S锁进行duplicate key进行检查。检查成功。
5 delete from test where a = 2; 事务1希望申请a=2下的X锁,但是由于事务2已经申请了一把X锁,两把X锁互斥,所以X锁申请进入锁请求队列。
6 出现死锁,事务1权重较小,所以被选择回滚(成为牺牲品)。 【insert第2阶段】insert into test (id, a) values (10, 2); 事务2开始插入数据,S锁升级为X锁,类型为insert intention。同理,X锁进入队列排队,形成循环等待,死锁产生。

总结

排查死锁时,首先需要根据死锁日志分析循环等待的场景,然后根据当前各个事务执行的SQL分析出加锁类型以及顺序,逆向推断出如何形成循环等待,这样就能找到死锁产生的原因了。

好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,上述分析都是基于经验的推断,希望其他小伙伴们能够指出当中的错误以及不足指出,谢谢大家对我们的支持。

(0)

相关推荐

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

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

  • 细说MySQL死锁与日志二三事

    最近线上 MySQL 接连发生了几起数据异常,都是在凌晨爆发,由于业务场景属于典型的数据仓库型应用,白天压力较小无法复现.甚至有些异常还比较诡异,最后 root cause 分析颇费周折.那实际业务当中咱们如何能快速的定位线上 MySQL 问题,修复异常呢?下文我会根据两个实际 case,分享下相关的经验与方法. Case1:部分数据更新失败 某天渠道同学反馈某报表极个别渠道数据为 0,大部分渠道数据正常.这个数据是由一个统计程序每天凌晨例行更新的,按理来说,要么全部正常,要么全部失败,那会是什

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

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

  • 查找MySQL线程中死锁的ID的方法

    如果遇到死锁了,怎么解决呢?找到原始的锁ID,然后KILL掉一直持有的那个线程就可以了, 但是众多线程,可怎么找到引起死锁的线程ID呢? MySQL 发展到现在,已经非常强大了,这个问题很好解决. 直接从数据字典连查找. 我们来演示下. 线程A,我们用来锁定某些记录,假设这个线程一直没提交,或者忘掉提交了. 那么就一直存在,但是数据里面显示的只是SLEEP状态. mysql> set @@autocommit=0; Query OK, 0 rows affected (0.00 sec) mys

  • 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

  • 详解MySQL中的死锁情况以及对死锁的处理方法

    当多个事务同时持有和请求同一资源上的锁而产生循环依赖的时候就产生了死锁.死锁发生在事务试图以不同的顺序锁定资源.以StockPrice表上的两个事务为例: 事务1 START TRANSACTION; UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01'; UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '20

  • 查看MySQL的错误日志的方法

    MySQL的错误信息是在data目录下的,且文件名为<hostname>.err(<hostname>指的是主机名),但由于每个人安装的环境不一样,或你忘记了data目录的所在位置,你可以通过下面方法查找. #hostname //获得主机名 <hostname> #find / -name <hostname>.err ..... #cd ... #vi <hostname>.err

  • mysql日志文件在哪 如何修改MySQL日志文件位置

    MySQL日志文件相信大家都有很多的了解,MySQL日志文件一般在:/var/log/mysqld.log,下面就教您修改MySQL日志文件位置的方法,供您参考. 今天需要改MySQL日志文件的位置,发现在/etc/my.cnf中怎么也改不了. 后来发现MySQL日志位是指定的: [root@localhost etc]# ps aux|grep mysqld root 11830 0.5 0.0 4524 1204 pts/0 S 03:03 0:00 /bin/sh /usr/bin/mys

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

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

  • mysql通过查看跟踪日志跟踪执行的sql语句

    在SQL SERVER下跟踪sql采用事件探查器,而在mysql下如何跟踪sql呢? 其实方法很简单,开启mysql的日志log功能,通过查看跟踪日志即可. 开启mysql的日志log方法: windows环境下的配置方法: 我使用的版本:Version: 5.0.37-community-nt-log (MySQL Community Edition (GPL)) 找到my.ini,我的是在"G:\Program Files (x86)\MySQL\MySQL Server 5.0"

随机推荐