一个最不可思议的MySQL死锁分析

1月 24th, 2014

 

1    死锁问题背景    1

1.1    一个不可思议的死锁    1

1.1.1    初步分析    3

1.2    如何阅读死锁日志    3

2    死锁原因深入剖析    4

2.1    Delete操作的加锁逻辑    4

2.2    死锁预防策略    5

2.3    剖析死锁的成因    6

3    总结    7

 

 

  1. 死锁问题背景

 

做MySQL代码的深入分析也有些年头了,再加上自己10年左右的数据库内核研发经验,自认为对于MySQL/InnoDB的加锁实现了如指掌,正因如此,前段时间,还专门写了一篇洋洋洒洒的文章,专门分析MySQL的加锁实现细节:《MySQL加锁处理分析》。

 

但是,昨天”润洁”同学在《MySQL加锁处理分析》这篇博文下咨询的一个MySQL的死锁场景,还是彻底把我给难住了。此死锁,完全违背了本人原有的锁知识体系,让我百思不得其解。本着机器不会骗人,既然报出死锁,那么就一定存在死锁的原则,我又重新深入分析了InnoDB对应的源码实现,进行多次实验,配合恰到好处的灵光一现,还真让我分析出了这个死锁产生的原因。这篇博文的余下部分的内容安排,首先是给出”润洁”同学描述的死锁场景,然后再给出我的剖析。对个人来说,这是一篇十分有必要的总结,对此博文的读者来说,希望以后碰到类似的死锁问题时,能够明确死锁的原因所在。

 

 

  1. 一个不可思议的死锁

 

“润洁”同学,给出的死锁场景如下:

 

表结构:

 

CREATE TABLE dltask (

id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT ‘auto id’,

a varchar(30) NOT NULL COMMENT ‘uniq.a’,

b varchar(30) NOT NULL COMMENT ‘uniq.b’,

c varchar(30) NOT NULL COMMENT ‘uniq.c’,

x varchar(30) NOT NULL COMMENT ‘data’,

PRIMARY KEY (id),

UNIQUE KEY uniq_a_b_c (a, b, c)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’deadlock test’;

 

a,b,c三列,组合成一个唯一索引,主键索引为id列。

 

事务隔离级别:

 

RR (Repeatable Read)

 

每个事务只有一条SQL:

 

delete from dltask where a=? and b=? and c=?;

 

SQL的执行计划:

 

执行计划

 

死锁日志:

 

死锁日志

 

  1. 初步分析

 

并发事务,每个事务只有一条SQL语句:给定唯一的二级索引键值,删除一条记录。每个事务,最多只会删除一条记录,为什么会产生死锁?这绝对是不可能的。但是,事实上,却真的是发生了死锁。产生死锁的两个事务,删除的是同一条记录,这应该是死锁发生的一个潜在原因,但是,即使是删除同一条记录,从原理上来说,也不应该产生死锁。因此,经过初步分析,这个死锁是不可能产生的。这个结论,远远不够!

 

  1. 如何阅读死锁日志

 

在详细给出此死锁产生的原因之前,让我们先来看看,如何阅读MySQL给出的死锁日志。

 

以上打印出来的死锁日志,由InnoDB引擎中的lock0lock.c::lock_deadlock_recursive()函数产生。死锁中的事务信息,通过调用函数lock_deadlock_trx_print()处理;而每个事务持有、等待的锁信息,由lock_deadlock_lock_print()函数产生。

 

例如,以上的死锁,有两个事务。事务1,当前正在操作一张表(mysql tables in use 1),持有两把锁(2 lock structs,一个表级意向锁,一个行锁(1 row lock)),这个事务,当前正在处理的语句是一条delete语句。同时,这唯一的一个行锁,处于等待状态(WAITING FOR THIS LOCK TO BE GRANTED)。

 

事务1等待中的行锁,加锁的对象是唯一索引uniq_a_b_c上页面号为12713页面上的一行(注:具体是哪一行,无法看到。但是能够看到的是,这个行锁,一共有96个bits可以用来锁96个行记录,n bits 96:lock_rec_print()方法)。同时,等待的行锁模式为next key锁(lock_mode X)。(注:关于InnoDB的锁模式,可参考我早期的一篇PPT:《InnoDB 事务/锁/多版本 实现分析》。简单来说,next key锁有两层含义,一是对当前记录加X锁,防止记录被并发修改,同时锁住记录之前的GAP,防止有新的记录插入到此记录之前。)

 

同理,可以分析事务2。事务2上有两个行锁,两个行锁对应的也都是唯一索引uniq_a_b_c上页面号为12713页面上的某一条记录。一把行锁处于持有状态,锁模式为X lock with no gap(注:记录锁,只锁记录,但是不锁记录前的GAP,no gap lock)。一把行锁处于等待状态,锁模式为next key锁(注:与事务1等待的锁模式一致。同时,需要注意的一点是,事务2的两个锁模式,并不是一致的,不完全相容。持有的锁模式为X lock with no gap,等待的锁模式为next key lock X。因此,并不能因为持有了X lock with no gap,就可以说next key lock X就一定能够加上。)。

 

分析这个死锁日志,就能发现一个死锁。事务1的next key lock X正在等待事务2持有的X lock with no gap(行锁X冲突),同时,事务2的next key lock X,却又在等待事务1正在等待中的next key锁(注:这里,事务2等待事务1的原因,在于公平竞争,杜绝事务1发生饥饿现象。),形成循环等待,死锁产生。

 

死锁产生后,根据两个事务的权重,事务1的权重更小,被选为死锁的牺牲者,回滚。

 

根据对于死锁日志的分析,确认死锁确实存在。而且,产生死锁的两个事务,确实都是在运行同样的基于唯一索引的等值删除操作。既然死锁确实存在,那么接下来,就是抓出这个死锁产生原因。

 

  1. 死锁原因深入剖析

 

  1. Delete操作的加锁逻辑

 

在《MySQL加锁处理分析》一文中,我详细分析了各种SQL语句对应的加锁逻辑。例如:Delete语句,内部就包含一个当前读(加锁读),然后通过当前读返回的记录,调用Delete操作进行删除。在此文的 组合六:id唯一索引+RR 中,可以看到,RR隔离级别下,针对于满足条件的查询记录,会对记录加上排它锁(X锁),但是并不会锁住记录之前的GAP(no gap lock)。对应到此文上面的死锁例子,事务2所持有的锁,是一把记录上的排它锁,但是没有锁住记录前的GAP(lock_mode X locks rec but not gap),与我之前的加锁分析一致。

 

其实,在《MySQL加锁处理分析》一文中的 组合七:id非唯一索引+RR 部分的最后,我还提出了一个问题:如果组合五、组合六下,针对SQL:select * from t1 where id = 10 for update; 第一次查询,没有找到满足查询条件的记录,那么GAP锁是否还能够省略?针对此问题,参与的朋友在做过试验之后,给出的正确答案是:此时GAP锁不能省略,会在第一个不满足查询条件的记录上加GAP锁,防止新的满足条件的记录插入。

 

其实,以上两个加锁策略,都是正确的。以上两个策略,分别对应的是:1)唯一索引上满足查询条件的记录存在并且有效;2)唯一索引上满足查询条件的记录不存在。但是,除了这两个之外,其实还有第三种:3)唯一索引上满足查询条件的记录存在但是无效。众所周知,InnoDB上删除一条记录,并不是真正意义上的物理删除,而是将记录标识为删除状态。(注:这些标识为删除状态的记录,后续会由后台的Purge操作进行回收,物理删除。但是,删除状态的记录会在索引中存放一段时间。) 在RR隔离级别下,唯一索引上满足查询条件,但是却是删除记录,如何加锁?InnoDB在此处的处理策略与前两种策略均不相同,或者说是前两种策略的组合:对于满足条件的删除记录,InnoDB会在记录上加next key lock X(对记录本身加X锁,同时锁住记录前的GAP,防止新的满足条件的记录插入。) Unique查询,三种情况,对应三种加锁策略,总结如下:

 

  • 找到满足条件的记录,并且记录有效,则对记录加X锁,No Gap锁(lock_mode X locks rec but not gap);

  • 找到满足条件的记录,但是记录无效(标识为删除的记录),则对记录加next key锁(同时锁住记录本身,以及记录之前的Gap:lock_mode X);
  • 未找到满足条件的记录,则对第一个不满足条件的记录加Gap锁,保证没有满足条件的记录插入(locks gap before rec);

 

此处,我们看到了next key锁,是否很眼熟?对了,前面死锁中事务1,事务2处于等待状态的锁,均为next key锁。明白了这三个加锁策略,其实构造一定的并发场景,死锁的原因已经呼之欲出。但是,还有一个前提策略需要介绍,那就是InnoDB内部采用的死锁预防策略。

 

  1. 死锁预防策略

 

InnoDB引擎内部(或者说是所有的数据库内部),有多种锁类型:事务锁(行锁、表锁),Mutex(保护内部的共享变量操作)、RWLock(又称之为Latch,保护内部的页面读取与修改)。

 

InnoDB每个页面为16K,读取一个页面时,需要对页面加S锁,更新一个页面时,需要对页面加上X锁。任何情况下,操作一个页面,都会对页面加锁,页面锁加上之后,页面内存储的索引记录才不会被并发修改。

 

因此,为了修改一条记录,InnoDB内部如何处理:

 

  1. 根据给定的查询条件,找到对应的记录所在页面;

  2. 对页面加上X锁(RWLock),然后在页面内寻找满足条件的记录;

  3. 在持有页面锁的情况下,对满足条件的记录加事务锁(行锁:根据记录是否满足查询条件,记录是否已经被删除,分别对应于上面提到的3种加锁策略之一);

  4. 死锁预防策略:相对于事务锁,页面锁是一个短期持有的锁,而事务锁(行锁、表锁)是长期持有的锁。因此,为了防止页面锁与事务锁之间产生死锁。InnoDB做了死锁预防的策略:持有事务锁(行锁、表锁),可以等待获取页面锁;但反之,持有页面锁,不能等待持有事务锁。

  5. 根据死锁预防策略,在持有页面锁,加行锁的时候,如果行锁需要等待。则释放页面锁,然后等待行锁。此时,行锁获取没有任何锁保护,因此加上行锁之后,记录可能已经被并发修改。因此,此时要重新加回页面锁,重新判断记录的状态,重新在页面锁的保护下,对记录加锁。如果此时记录未被并发修改,那么第二次加锁能够很快完成,因为已经持有了相同模式的锁。但是,如果记录已经被并发修改,那么,就有可能导致本文前面提到的死锁问题。
  1. 以上的InnoDB死锁预防处理逻辑,对应的函数,是row0sel.c::row_search_for_mysql()。感兴趣的朋友,可以跟踪调试下这个函数的处理流程,很复杂,但是集中了InnoDB的精髓。

 

  1. 剖析死锁的成因

 

做了这么多铺垫,有了Delete操作的3种加锁逻辑、InnoDB的死锁预防策略等准备知识之后,再回过头来分析本文最初提到的死锁问题,就会手到拈来,事半而功倍。

 

首先,假设dltask中只有一条记录:(1, ‘a’, ‘b’, ‘c’, ‘data’)。三个并发事务,同时执行以下的这条SQL:

 

delete from dltask where a=’a’ and b=’b’ and c=’c’;

 

并且产生了以下的并发执行逻辑,就会产生死锁:

 

deadlock

 

上面分析的这个并发流程,完整展现了死锁日志中的死锁产生的原因。其实,根据事务1步骤6,与事务0步骤3/4之间的顺序不同,死锁日志中还有可能产生另外一种情况,那就是事务1等待的锁模式为记录上的X锁 + No Gap锁(lock_mode X locks rec but not gap waiting)。这第二种情况,也是”润洁”同学给出的死锁用例中,使用MySQL 5.6.15版本测试出来的死锁产生的原因。

 

  1. 总结

 

行文至此,MySQL基于唯一索引的单条记录的删除操作并发,也会产生死锁的原因,已经分析完毕。其实,分析此死锁的难点,在于理解MySQL/InnoDB的行锁模式,针对不同情况下的加锁模式的区别,以及InnoDB处理页面锁与事务锁的死锁预防策略。明白了这些,死锁的分析就会显得清晰明了。

 

最后,总结下此类死锁,产生的几个前提:

 

  • Delete操作,针对的是唯一索引上的等值查询的删除;(范围下的删除,也会产生死锁,但是死锁的场景,跟本文分析的场景,有所不同)

  • 至少有3个(或以上)的并发删除操作;
  • 并发删除操作,有可能删除到同一条记录,并且保证删除的记录一定存在;
  • 事务的隔离级别设置为Repeatable Read,同时未设置innodb_locks_unsafe_for_binlog参数(此参数默认为FALSE);(Read Committed隔离级别,由于不会加Gap锁,不会有next key,因此也不会产生死锁)
  • 使用的是InnoDB存储引擎;(废话!MyISAM引擎根本就没有行锁)
  1. malasang
    1月 26th, 201410:48

    分析的到位!!

  2. jiahaoli
    1月 27th, 201414:15

    透彻~·

  3. evergreen
    2月 13th, 201411:44

    可否详细说明事务2中的restart主要作了哪些工作?

  4. evergreen
    2月 13th, 201411:57

    假设这里的索引只是个普通的索引:
    事务1等待的是next key
    事务2在事务0提交后,获取了next key, 事务2 restart后所模式会变成gap锁(这个结论成立吗?)
    如果gap锁和next key互斥的话,也产生了死锁。

    如果以上结论成立的话,也就是说mysql在高并发下的索引删除都会产生死锁。

  5. dodo
    2月 20th, 201411:01

    请帮忙看下这个问题,谢谢!
    下面的update语句,vchcode为主键,更新记录最多只可能有一条,但却要在不同页的两条记录上加锁。与另一个update语句死锁了。

    update dlyndx_eshoporder_send set sendBillPrinted=1 where Vchcode=18279284168852278640 and profileid=17190
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 770 page no 1164843 n bits 96 index `PRIMARY` of table `roshall`.`dlyndx_eshoporder_send` trx id 4F2CB261 lock_mode X locks rec but not gap
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 770 page no 1164841 n bits 96 index `PRIMARY` of table `roshall`.`dlyndx_eshoporder_send` trx id 4F2CB261 lock_mode X locks rec but not gap waiting

  6. wutuobang
    5月 15th, 201415:07

    你好,在read-commited隔离级别下,有唯一索引的表,如果两个insert语句插入的唯一索引字段值是一样的,也会发生死锁,请问是什么原因?

  7. 试管婴儿成功日记
    5月 20th, 201416:30

    路过就应该要评论一下.

  8. 试管婴儿网
    6月 8th, 201413:37

    路过一下!!

  9. rc芮娜森面膜
    7月 1st, 201413:56

    博主这个博客弄得不错,可以的话相互评论一下.

  10. java文盲
    7月 7th, 201420:50

    我也想知道restart具体的操作,还有就是第7步为什么说由于事务1在等待?难道我事务2的加锁会受到事务1等待加锁的影响?此时事务1和事务2不是并没有加next-key锁吗? 还是说只是因为不兼容所以等待。。应该也不会吧。。。第7步怎么也想不明白。。还求详解。。。

  11. wuherong2000
    7月 24th, 201409:30

    LATEST DETECTED DEADLOCK
    ————————
    140723 15:49:04
    *** (1) TRANSACTION:
    TRANSACTION 450215, ACTIVE 0 sec starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 30 lock struct(s), heap size 2496, 147 row lock(s), undo log entries 80
    MySQL thread id 268, OS thread handle 0x1268, query id 39232 192.168.192.136 root Updating
    update inventory set waitOutSingleQuan=25 where inventoryId=29
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 5638 n bits 152 index `PRIMARY` of table `tltp`.`inventory` trx id 450215 lock_mode X locks rec but not gap waiting
    Record lock, heap no 83 PHYSICAL RECORD: n_fields 32; compact format; info bits 0
    *** (2) TRANSACTION:
    TRANSACTION 450211, ACTIVE 1 sec starting index read, thread declared inside InnoDB 500
    mysql tables in use 1, locked 1
    30 lock struct(s), heap size 2496, 147 row lock(s), undo log entries 80
    MySQL thread id 266, OS thread handle 0x1a0, query id 39233 192.168.192.136 root Updating
    update inventory set waitOutSingleQuan=25 where inventoryId=29
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 0 page no 5638 n bits 152 index `PRIMARY` of table `tltp`.`inventory` trx id 450211 lock mode S
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
    0: len 8; hex 73757072656d756d; asc supremum;;

  12. sunny
    9月 5th, 201417:12

    博主帮我看看这个死锁吧。 update的批处理。 只是个简单的update.
    ————————
    LATEST DETECTED DEADLOCK
    ————————
    2014-09-05 01:18:57 2a14
    *** (1) TRANSACTION:
    TRANSACTION 71223013, ACTIVE 12 sec fetching rows, thread declared inside InnoDB 4926
    mysql tables in use 6, locked 3
    LOCK WAIT 21 lock struct(s), heap size 3112, 19 row lock(s), undo log entries 18
    MySQL thread id 825, OS thread handle 0x21ac, query id 432440922 localhost 127.0.0.1 root updating
    UPDATE T00_OtherDeviceDisplayed SET StatusCode = 0, CodeArray = ” WHERE OtherDevice_ID = 93
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 354 page no 3 n bits 296 index `PRIMARY` of table `newsolar`.`t00_otherdevicedisplayed` trx id 71223013 lock_mode X locks rec but not gap waiting
    Record lock, heap no 75 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
    0: len 4; hex 0000004b; asc K;;
    1: len 6; hex 0000043ec6e1; asc > ;;
    2: len 7; hex 5c000005150648; asc \ H;;
    3: len 8; hex 000000000000004b; asc K;;
    4: len 4; hex 80000000; asc ;;
    5: len 0; hex ; asc ;;

    *** (2) TRANSACTION:
    TRANSACTION 71223009, ACTIVE 13 sec fetching rows, thread declared inside InnoDB 4905
    mysql tables in use 6, locked 3
    33 lock struct(s), heap size 6960, 30 row lock(s), undo log entries 29
    MySQL thread id 1066, OS thread handle 0x2a14, query id 432440921 localhost 127.0.0.1 root
    UPDATE T00_OtherDeviceDisplayed SET StatusCode = 0, CodeArray = ” WHERE OtherDevice_ID = 75
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 354 page no 3 n bits 296 index `PRIMARY` of table `newsolar`.`t00_otherdevicedisplayed` trx id 71223009 lock_mode X locks rec but not gap
    Record lock, heap no 75 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
    0: len 4; hex 0000004b; asc K;;
    1: len 6; hex 0000043ec6e1; asc > ;;
    2: len 7; hex 5c000005150648; asc \ H;;
    3: len 8; hex 000000000000004b; asc K;;
    4: len 4; hex 80000000; asc ;;
    5: len 0; hex ; asc ;;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 354 page no 3 n bits 296 index `PRIMARY` of table `newsolar`.`t00_otherdevicedisplayed` trx id 71223009 lock_mode X locks rec but not gap waiting
    Record lock, heap no 95 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
    0: len 4; hex 0000005f; asc _;;
    1: len 6; hex 0000043ec6e5; asc > ;;
    2: len 7; hex 5f0000051403cb; asc _ ;;
    3: len 8; hex 000000000000005f; asc _;;
    4: len 4; hex 80000000; asc ;;
    5: len 0; hex ; asc ;;

    *** WE ROLL BACK TRANSACTION (1)

  13. sunny
    9月 5th, 201417:14

    忘了写表结构。补上
    CREATE TABLE `t00_otherdevicedisplayed` (
    `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `OtherDevice_ID` bigint(20) unsigned NOT NULL,
    `StatusCode` int(11) DEFAULT NULL,
    `CodeArray` varchar(500) DEFAULT NULL,
    PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=utf8;

  14. sunny
    9月 5th, 201417:17

    又忘了写,数据库隔离级别,READ-committed

  15. sunny
    9月 6th, 201409:32

    我提交了评论怎么不见了

  16. 姜建
    11月 6th, 201411:09

    2014-11-06 10:20:01 7fcaf229c700InnoDB: transactions deadlock detected, dumping detailed information.
    2014-11-06 10:20:01 7fcaf229c700
    *** (1) TRANSACTION:
    TRANSACTION 20125113169, ACTIVE 0 sec starting index read
    mysql tables in use 2, locked 2
    LOCK WAIT 6 lock struct(s), heap size 1184, 3 row lock(s)
    MySQL thread id 1799660263, OS thread handle 0x7fcaf196c700, query id 23935199756 127.0.0.1 root Sending data
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1194 page no 462292 n bits 88 index `PRIMARY` of table `yc_core`.`service_order` trx id 20125113169 lock mode S locks rec but not gap waiting
    *** (2) TRANSACTION:
    TRANSACTION 20125113146, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4999
    mysql tables in use 1, locked 1
    7 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 6
    MySQL thread id 1799660206, OS thread handle 0x7fcaf229c700, query id 23935200354 172.16.0.167 private-api updating
    UPDATE `service_order` SET `status` = ‘2’, `extension` = ‘{\”user_source\”:\”50369\”,\”app_trade_no\”:\”zhengshi72202\”,\”app_order_id\”:\”zhengshi72202\”,\”oauth_client_id\”:\”baidu_map\”,\”passenger_session_id\”:\”545adad1c49a35ca03000005\”}’, `payable` = ‘1’, `select_car_time` = ‘1415240401’ WHERE (`service_order`.`service_order_id` = 35423917)
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 1194 page no 462292 n bits 88 index `PRIMARY` of table `yc_core`.`service_order` trx id 20125113146 lock_mode X locks rec but not gap
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1194 page no 486202 n bits 736 index `idx_ctime_city_status_ut` of table `yc_core`.`service_order` trx id 20125113146 lock_mode X locks rec but not gap waiting

    请教问题:为什么我的事务1中只显示send data,但看不到具体是哪条SQL,用binlog日志也找不到该查询线程对应的语句。

  17. siyuan
    11月 10th, 201416:44

    博主你好,文章写得很深入,我有个疑问:事务2在第5步restart的时候,为什么没有释放记录锁(X lock not gap)呢?

  18. mdkii
    4月 1st, 201514:45

    能分析出这样的场景很厉害。
    我有一个疑问就是,为什么mysql先获取页面latch再获取事务锁?
    我记得其他主流库都是先获取锁再获取页面latch的吧。

    • hedengcheng
      4月 2nd, 201515:12

      错了。所有数据库,latch都是先于事务锁之前获取。

  19. mdkii
    4月 7th, 201509:04

    我是在一篇论文中看到,“数据库是先获取锁再获取latch的”。
    论文名字是 ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial rollbacks Using Write-Ahead Logging。
    在这个文章中的第五章节 5. NORMAL PROCESSING 中是这么说的:
    If the granularity of locking is a record, then, when an update is to be
    performed on a record in a page, after the record is locked, that page is fixed
    in the buffer and latched in the X mode, the update is performed, a log record
    is appended to the log, the LSN of the log record is placed in the page .LSN
    field of the page and in the transaction table, and the page is unlatched and
    unfixed.
    也许这边论文太老了,现在的数据库不是这么实现的。但我不知道
    为什么要先获取latch再获取锁?我感觉这个场景发生死锁的根源问题就是这个机制造成的。
    换成先获取锁再取latch有什么实现上的问题吗?

    • hedengcheng
      4月 13th, 201520:53

      ARIES不老,这个是数据库最经典的论文,值得多次阅读。
      关于latch和row lock,原则上,为了防止latch和lock之间出现死锁(这个死锁是无法检测到的),我们可以在持有lock的情况下去等待加latch成功。
      但是,在实际的实现中,latch是用来保护页面中数据的一致性的,获得了latch,页面中的数据才不会被并发修改。因此,在实际的实现中,数据库都是持有latch,然后尝试加row lock(此时不等待,try的方式),成功即可。如果失败,那么退化到先释放latch,加lock,再加latch,但是这个时候要重新检查页面在latch释放后是否有被其他事务修改。

      • laimingxing
        5月 18th, 201622:49

        叹为观止!

      • lbxx159753
        7月 21st, 201615:16

        请教个问题啊,按照您说的加锁顺序,第2步中事务2为什么能获得到页面锁呢?此时事务0应该已经获得了记录锁+页面锁,感觉有些矛盾啊。

      • lbxx159753
        7月 21st, 201618:37

        另外还有个疑问啊,第7步中事务2除了释放页面锁外,有没有释放第5步中的记录锁呢?如果没有,那么不释放的做法感觉像是bug,而且事务2应该直接在获取一个gap锁来升级成next_key锁,不应该死锁;如果释放了,那事务1和2都在等待一个没有人占据的锁,感觉这不会产生死锁吧。感觉您分析产生死锁的原因是事务1和2互相等待对方,而非等待那条记录的锁。而互相等待对方又不像是正常加解锁的方式。
        虽然您的分析整体看起来很有道理,但是仔细琢磨又有很多让人疑惑的地方。不知道您能否再进一步的讲解下吗?

      • lbxx159753
        7月 21st, 201618:46

        又仔细看了一遍,事务1持有gap锁等待记录锁和2持有记录锁等待gap锁,两个事务都没有页面锁——即是那条记录向前的gap锁和它的记录锁死锁了,是这样子吗?

  20. mmachera
    7月 7th, 201517:31

    求大侠帮忙分析一个死锁原因。
    背景:
    mysql数据库隔离级别为(READ-COMMITED),有2个并发事务,每个事务都是针对同一张表的操作( file_table),该表只有主键(ID字段)索引,没有其他任何索引。

    每个事务同时执行两组SQL语句,每组SQL语句为先删除一条记录,然后再写入一条记录,也就是同一个事务总共会执行4条语句。

    每个事务执行的SQL语句如下:
    事务一:
    DELETE FROM file_table WHERE SENDER_ID = 201507011326315 AND RECEIVER_ID = 201507011325162 AND FILE_NAME = ‘filename_1’;
    INSERT INTO file_table (ID,SENDER_ID,RECEIVER_ID,FILE_NAME) VALUES (1,201507011326315,201507011325162, ‘filename_1’);
    DELETE FROM file_table WHERE SENDER_ID = 201507011326315 AND RECEIVER_ID = 201507011325162 AND FILE_NAME = ‘filename_2’;
    INSERT INTO file_table (ID,SENDER_ID,RECEIVER_ID,FILE_NAME) VALUES (2,201507011326315,201507011325162, ‘filename_2’);

    事务二:
    DELETE FROM file_table WHERE SENDER_ID = 201507011325413 AND RECEIVER_ID = 201507011325162 AND FILE_NAME = ‘filename_1’;
    INSERT INTO file_table (ID,SENDER_ID,RECEIVER_ID,FILE_NAME) VALUES (3,201507011325413,201507011325162, ‘filename_1’);
    DELETE FROM file_table WHERE SENDER_ID = 201507011325413 AND RECEIVER_ID = 201507011325162 AND FILE_NAME = ‘filename_2’;
    INSERT INTO file_table (ID,SENDER_ID,RECEIVER_ID,FILE_NAME) VALUES (4,201507011325413,201507011325162, ‘filename_2’);

    事务一和事务二是同时并发执行的,在所有4条语句中,其实可以不用关心RECEIVER_ID字段,因为其值都是相同的。每个事务都是根据SENDER_ID、RECEIVER_ID、FILE_NAME可以唯一确定一条记录(没有任何索引,除了主键索引),删除操作也是使用这三个字段作为条件。最终并发执行时,发生死锁,从死锁日志看出,死锁发生在表的主键上。

    如果每个事务只执行一组DELETE、INSERT操作,则不会有死锁。

    死锁日志截取如下(日志显示的SQL可能与上面的SQL语句不一致,但是完全相同的操作产生的):
    LATEST DETECTED DEADLOCK
    ————————
    150707 16:22:12
    *** (1) TRANSACTION:
    TRANSACTION 21647F9, ACTIVE 0 sec fetching rows
    mysql tables in use 1, locked 1
    LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
    MySQL thread id 14991, OS thread handle 0x4308, query id 81304600 localhost 127.0.0.1 root updating
    DELETE FROM FILE_TABLE WHERE SENDER_ID = 201507011326064 AND RECEIVER_ID = 201507011325162 AND FILE_NAME =
    ‘filename_1’
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 12675 n bits 120 index `PRIMARY` of table `bfip_deadlock`.`FILE_TABLE` trx id 21647F9
    lock_mode X locks rec but not gap waiting
    Record lock, heap no 23 PHYSICAL RECORD: n_fields 32; compact format; info bits 32
    0: len 8; hex 9bf6f823dda484ec; asc # ;;
    1: len 6; hex 0000021647f7; asc G ;;
    2: len 7; hex 5a000030822056; asc Z 0 V;;
    3: len 8; hex 8000b74501d46de5; asc E m ;;
    4: len 8; hex 3134363136343130; asc 14616410;;
    5: len 21; hex e6b5b7e58fa3e88194e59088e5869ce59586e8a18c; asc ;;
    6: len 8; hex 8000b74501d46cea; asc E l ;;
    7: len 8; hex 3633303330303030; asc 63030000;;
    8: len 18; hex e4b8ade59bbde58589e5a4a7e993b6e8a18c; asc ;;
    9: len 1; hex 53; asc S;;
    10: len 19; hex 4f46495f3134365f45425f3230313530373033; asc OFI_146_EB_20150703;;
    11: len 3; hex 545854; asc TXT;;
    12: len 2; hex 3030; asc 00;;
    13: len 1; hex 57; asc W;;
    14: len 10; hex 323031352d30372d3033; asc 2015-07-03;;
    15: len 8; hex 32333a31373a3331; asc 23:17:31;;
    16: SQL NULL;
    17: len 8; hex 82cbe593aee48623; asc #;;
    18: len 0; hex ; asc ;;
    19: len 0; hex ; asc ;;
    20: SQL NULL;
    21: SQL NULL;
    22: SQL NULL;
    23: SQL NULL;
    24: len 8; hex 7fffffffffffffff; asc ;;
    25: len 19; hex 323031352d30372d30332032333a31373a3335; asc 2015-07-03 23:17:35;;
    26: SQL NULL;
    27: SQL NULL;
    28: len 4; hex 80000001; asc ;;
    29: len 1; hex 4e; asc N;;
    30: SQL NULL;
    31: SQL NULL;

    *** (2) TRANSACTION:
    TRANSACTION 21647F7, ACTIVE 0 sec, thread declared inside InnoDB 500
    mysql tables in use 1, locked 1
    3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 2
    MySQL thread id 14990, OS thread handle 0x88b8, query id 81304606 localhost 127.0.0.1 root updating
    DELETE FROM FILE_TABLE WHERE SENDER_ID = 201507011325413 AND RECEIVER_ID = 201507011325162 AND FILE_NAME =
    ‘filename_1’
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 0 page no 12675 n bits 120 index `PRIMARY` of table `bfip_deadlock`.`FILE_TABLE` trx id 21647F7
    lock_mode X locks rec but not gap
    Record lock, heap no 23 PHYSICAL RECORD: n_fields 32; compact format; info bits 32
    0: len 8; hex 9bf6f823dda484ec; asc # ;;
    1: len 6; hex 0000021647f7; asc G ;;
    2: len 7; hex 5a000030822056; asc Z 0 V;;
    3: len 8; hex 8000b74501d46de5; asc E m ;;
    4: len 8; hex 3134363136343130; asc 14616410;;
    5: len 21; hex e6b5b7e58fa3e88194e59088e5869ce59586e8a18c; asc ;;
    6: len 8; hex 8000b74501d46cea; asc E l ;;
    7: len 8; hex 3633303330303030; asc 63030000;;
    8: len 18; hex e4b8ade59bbde58589e5a4a7e993b6e8a18c; asc ;;
    9: len 1; hex 53; asc S;;
    10: len 19; hex 4f46495f3134365f45425f3230313530373033; asc OFI_146_EB_20150703;;
    11: len 3; hex 545854; asc TXT;;
    12: len 2; hex 3030; asc 00;;
    13: len 1; hex 57; asc W;;
    14: len 10; hex 323031352d30372d3033; asc 2015-07-03;;
    15: len 8; hex 32333a31373a3331; asc 23:17:31;;
    16: SQL NULL;
    17: len 8; hex 82cbe593aee48623; asc #;;
    18: len 0; hex ; asc ;;
    19: len 0; hex ; asc ;;
    20: SQL NULL;
    21: SQL NULL;
    22: SQL NULL;
    23: SQL NULL;
    24: len 8; hex 7fffffffffffffff; asc ;;
    25: len 19; hex 323031352d30372d30332032333a31373a3335; asc 2015-07-03 23:17:35;;
    26: SQL NULL;
    27: SQL NULL;
    28: len 4; hex 80000001; asc ;;
    29: len 1; hex 4e; asc N;;
    30: SQL NULL;
    31: SQL NULL;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 12675 n bits 120 index `PRIMARY` of table `bfip_deadlock`.`FILE_TABLE` trx id 21647F7
    lock_mode X locks rec but not gap waiting
    Record lock, heap no 34 PHYSICAL RECORD: n_fields 32; compact format; info bits 32
    0: len 8; hex 9bf6f823dda484e6; asc # ;;
    1: len 6; hex 0000021647f9; asc G ;;
    2: len 7; hex 5c00002630248b; asc \ &0$ ;;
    3: len 8; hex 8000b74501d47070; asc E pp;;
    4: len 8; hex 3634373431393131; asc 64741911;;
    5: len 24; hex e5a4a9e9aa84e89299e993b6e69d91e99587e993b6e8a18c; asc ;;
    6: len 8; hex 8000b74501d46cea; asc E l ;;
    7: len 8; hex 3633303330303030; asc 63030000;;
    8: len 18; hex e4b8ade59bbde58589e5a4a7e993b6e8a18c; asc ;;
    9: len 1; hex 53; asc S;;
    10: len 19; hex 4f46495f3738305f45425f3230313530373033; asc OFI_780_EB_20150703;;
    11: len 3; hex 545854; asc TXT;;
    12: len 2; hex 3030; asc 00;;
    13: len 1; hex 57; asc W;;
    14: len 10; hex 323031352d30372d3033; asc 2015-07-03;;
    15: len 8; hex 32333a31373a3331; asc 23:17:31;;
    16: SQL NULL;
    17: len 8; hex 82cbe593aee48624; asc $;;
    18: len 0; hex ; asc ;;
    19: len 0; hex ; asc ;;
    20: SQL NULL;
    21: SQL NULL;
    22: SQL NULL;
    23: SQL NULL;
    24: len 8; hex 7fffffffffffffff; asc ;;
    25: len 19; hex 323031352d30372d30332032333a31373a3335; asc 2015-07-03 23:17:35;;
    26: SQL NULL;
    27: SQL NULL;
    28: len 4; hex 80000001; asc ;;
    29: len 1; hex 4e; asc N;;
    30: SQL NULL;
    31: SQL NULL;

    *** WE ROLL BACK TRANSACTION (2)
    ————
    TRANSACTIONS
    ————
    Trx id counter 2164820
    Purge done for trx’s n:o < 21647FD undo n:o < 0
    History list length 389

    麻烦帮忙分析下原因呢,是不是跟索引有关。我做过一个测试,如果用相同的条件先把主键查出来,再用主键去DELETE,则不会有死锁发生。

  21. whh
    9月 12th, 201511:25

    表结构:
    CREATE TABLE `params` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键ID’,
    `userid` bigint(20) unsigned NOT NULL COMMENT ‘用户ID’,
    `paramkey` varchar(32) DEFAULT NULL COMMENT ‘参数名’,
    `paramvalue` text COMMENT ‘参数值’,
    PRIMARY KEY (`id`),
    KEY `flow_index` (`userid`)
    );

    事务:
    delete from params where userid=’xxx’;
    insert into params(…) values(…);
    insert into params(…) values(…);

    userid是一个普通索引, 不是唯一索引, 但竟然也偶尔有死锁的情况发生。不得其解。。。

  22. test
    10月 19th, 201518:10

    你好 博主:
    请教一个问题
    数据库隔离级别 RR,删除操作的where条件为非唯一索引,现在出现这种情况,
    delete from b where ca = 1;
    如果ca=1对应的记录不存在的时候,两个事务都可以获取对应的 lock mode X 锁,
    如果对应的记录存在,则只能是一个事务可以获取lock mode x 锁,能帮忙分析下么;
    多谢 ~ ~

  23. heh
    11月 18th, 201517:21

    能不能说下解决方法,单条语句都死锁,这不就是个bug么,mysql都保证不了语句的原子性

  24. shshhs
    3月 2nd, 201615:55

    对文章以时间为轴的那个图有个疑问。从6,7步骤中看到 事务1事务2都要等待获取next key 锁,为啥8中说事务1在等事务2,事务2在等事务1,没看出来怎么个死锁法(死锁不是要满足循环等待吗)。

  25. 风叶小劲
    3月 18th, 201618:34

    我的数据库其中一个表被锁住了,这个表是Innodb的,隔离级别是rc级别,,,
    我记得在RC级别下读跟写是不会冲突的啊,怎么执行这个普通的select语句会导致锁等待啊???
    SQL语句是 SELECT count(*) FROM zh_bbs_security_failedlog
    提示 Waiting for table metadata lock

  26. dengqiong
    3月 22nd, 201614:50

    你好,第8点不能理解
    1、首先,您介绍的死锁预防策略里面说明页面锁和事务锁之间存在加锁顺序,不可能造成死锁。所以我理解事务1等待事务2,事务2等待事务1必然与页面锁没有关系。
    2、事务1等待next key锁可以理解,事务2等待谁呢?事务2自己持有记录锁,在此基础上升级成next key锁,应是理所当然,也没有其他事务持有nextkey锁啊

    请求指教,谢谢。

  27. yan
    4月 18th, 201616:22

    分析的很好。博主是否可以把博客字体调整下,字体太小了。

  28. saup007
    6月 23rd, 201621:46

    Read Committed隔离级别,由于不会加Gap锁,不会有next key,因此也不会产生死锁

    建议设置成RC,这样减少死锁的问题?

  29. 战斗菜鸡
    7月 17th, 201609:22

    讲解的非常好,学习到了~

  30. lbxx159753
    7月 21st, 201615:18

    现在不能回复了吗,看不到自己的啊。附上之前的问题:
    “请教个问题啊,按照您说的加锁顺序,第2步中事务2为什么能获得到页面锁呢?此时事务0应该已经获得了记录锁+页面锁,感觉有些矛盾啊。”