死锁案例之一

一 前言 死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。 二 案例分析 2.1 环境说明 MySQL 5.6 事务隔离级别为RR

  1. CREATE TABLE `ty` (
  2. `id` int(11) NOT NULL AUTO_INCREMENT,
  3. `a` int(11) DEFAULT NULL,
  4. `b` int(11) DEFAULT NULL,
  5. PRIMARY KEY (`id`),
  6. KEY `idxa` (`a`)
  7. ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
  8. insert into ty(a,b) values(2,3),(5,4),(6,7);

2.2 测试用例

T2

T1

begin;

delete from ty where a=5;

begin;

delete from ty where a=5;

insert into ty(a,b) values(2,10);

delete from ty where a=5;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

2.3 死锁日志

  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2017-09-09 22:34:13 7f78eab82700
  5. *** (1) TRANSACTION:
  6. TRANSACTION 462308399, ACTIVE 33 sec starting index read
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
  9. MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating
  10. delete from ty where a=5
  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  12. RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308399 lock_mode X waiting
  13. *** (2) TRANSACTION:
  14. TRANSACTION 462308398, ACTIVE 61 sec inserting, thread declared inside InnoDB 5000
  15. mysql tables in use 1, locked 1
  16. 5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 2
  17. MySQL thread id 3525490, OS thread handle 0x7f78eab82700, query id 780039714 localhost root update
  18. insert into ty(a,b) values(2,10)
  19. *** (2) HOLDS THE LOCK(S):
  20. RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X
  21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  22. RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting
  23. *** WE ROLL BACK TRANSACTION (1)

2.3分析死锁日志 首先要理解的是 对同一个字段申请加锁是需要排队. 其次表ty中索引idxa为非唯一普通索引,我们根据事务执行的时间顺序来解释,这样比较好理解。 a. 根据死锁日志显示 事务2 也即sess1执行的事务,根据 HOLDS THE LOCK(S)显示 sess1 先执行 delete from ty where a=5 ,该事务持有索引a=5 的行锁lock_mode X ,因为是RR隔离级别,所以sess1 还持有两个gap锁[1,2]-[2,5], [2,5]-[3,6] 。 b. 事务1的日志也即sess2执行的事务,申请对 a=5 加锁,一个rec lock 和两个gap锁,因为sess1中delete还没释放,故sess2的事务1等待sess1的事务2释放a=5的锁资源。 c. 然后根据WAITING FOR THIS LOCK TO BE GRANTED,提示事务2 insert语句正在等待 lock_mode X locks gap before rec insert intention waiting, 因为insert语句 [4,2] 介于gap锁[1,2]-[2,5]之间,所以有了提示 "lock_mode X locks gap",insert语句必须等待前面 sess2中delete 获取锁并且释放锁。于是,sess2(delete) 等待sess1(delete) ,sess1(insert)等待sess2(delete),循环等待,造成死锁。 问题 如果sess1 执行 insert into ty(a,b) values(5,10); sess2会遇到死锁吗? 三 案例二 3.1 索引为唯一键 MySQL 5.6 事务隔离级别为RR

  1. CREATE TABLE `t2` (
  2. `id` int(11) NOT NULL AUTO_INCREMENT,
  3. `a` int(11) DEFAULT NULL,
  4. `b` int(11) DEFAULT NULL,
  5. PRIMARY KEY (`id`),
  6. unique KEY `idxa` (`a`)
  7. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
  8. insert into t2(a,b) values(2,3),(5,4),(6,7)

3.2 测试用例

T2

T1

begin;

delete from ty where a=5;

begin;

delete from ty where a=5;

insert into ty(a,b) values(2,10);

delete from ty where a=5;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

3.3 死锁日志

  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2017-09-10 00:03:31 7f78ea936700
  5. *** (1) TRANSACTION:
  6. TRANSACTION 462308445, ACTIVE 9 sec starting index read
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
  9. MySQL thread id 3526009, OS thread handle 0x7f896cc4b700, query id 780047877 localhost root updating
  10. delete from t2 where a=5
  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  12. RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308445 lock_mode X waiting
  13. *** (2) TRANSACTION:
  14. TRANSACTION 462308444, ACTIVE 17 sec inserting, thread declared inside InnoDB 5000
  15. mysql tables in use 1, locked 1
  16. 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
  17. MySQL thread id 3526051, OS thread handle 0x7f78ea936700, query id 780047890 localhost root update
  18. insert t2(a,b) values(5,10)
  19. *** (2) HOLDS THE LOCK(S):
  20. RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444 lock_mode X locks rec but not gap
  21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  22. RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444 lock mode S waiting
  23. *** WE ROLL BACK TRANSACTION (1)

3.4 分析死锁日志 首先我们要特别说明delete的加锁逻辑

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

其次需要大家注意的是对比两个死锁案例会发现,sess1 事务持有的锁类型发生了变化 delete持有的锁变为lock_mode X locks rec but not gap 。 insert语句持有的锁变为 lock mode S waiting。原因是因为测试表结构发生了变化字段a由普通索引变为唯一键,RR模式下对唯一键操作是没有gap锁的,而且insert 写入含有唯一键的数据是会申请GAP锁的特殊情况 Insert Intention Lock. 本例我们依然根据事务执行的时间顺序来解释,这样比较好理解。 a. 根据死锁日志显示 事务2 也即sess1执行的事务,根据 HOLDS THE LOCK(S)显示 sess1 先执行 delete from ty where a=5 ,该事务持有索引a=5 的行锁lock_mode X locks rec but not gap。因为本例中a是唯一键,故没有gap锁。 b. 事务1的日志也即sess2执行的事务,申请对 a=5 加锁(X Next-key Lock),一个rec lock 但是因为sess1中delete 已经执行完成,记录无效没有被删除,锁还没释放,故sess2的事务1等待sess1的事务2释放a=5的锁资源,日志中提示 lock_mode X waiting. c. 然后根据WAITING FOR THIS LOCK TO BE GRANTED,提示事务2 insert语句正在等待 lock mode S waiting,为什么这次是 S 锁呢?因为a字段是一个唯一索引,所以insert语句会在插入前进行一次duplicate key的检查,需要申请S锁防止其他事务对a字段进行重复插入。而插入意向锁与T1已经insert语句必须等待前面 sess2中delete 获取a=5的行锁并且释放锁。 于是,sess2(delete) 等待sess1(delete) ,sess1(insert)等待sess2(delete),循环等待,造成死锁

四 小结 本文研究了RR事务隔离级别下,普通索引与唯一键两种情况的死锁场景。如何避免解决此类死锁?推荐使用RC隔离级别+ ROW BASE BINLOG . 但是对于RC/RR模式下 ,insert 遇到唯一键冲突的时候的死锁不可避免。需要开发在设计表结构的时候 减少unique 索引设计。

原文发表时间:2017-10-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

深入解析:你听说过Oracle数据库的更新重启动吗?

杨廷琨 云和恩墨高级咨询顾问, ITPUB Oracle 数据库管理版版主 ,人称 “杨长老”,十数年如一日坚持进行 Oracle 技术研究与写作,号称 ...

924
来自专栏性能与架构

MySQL 8.0 将结束 MyISAM 引擎

MyISAM 存储引擎已经有了20年的历史,在1995年时,MyISAM 是 MySQL 唯一的存储引擎,服务了20多年,即将退居二线 MySQL 5.7 中...

3266
来自专栏杨建荣的学习笔记

关于primary key和unique index的奇怪问题 (58天)

今天一个dba交给我一个问题,让我帮忙查一下。说有个脚本运行的时候有错,让我看看是什么原因。 脚本的思路如下: 先drop PK,FK之类的constraint...

29012
来自专栏PHP在线

Mysql存储引擎中InnoDB与Myisam的区别

1. 事务处理innodb 支持事务功能,myisam 不支持。 Myisam 的执行速度更快,性能更好。 2. select ,update ,insert...

3095
来自专栏Jerry的SAP技术分享

在ABAP里取得一个数据库表记录数的两种方法

这个函数使用起来很简单,只需要将想查询的数据库表名称维护进输入参数IT_TABLES:

1555
来自专栏杨建荣的学习笔记

MySQL和Oracle的添加字段的处理差别 (r10笔记第73天)

昨天在微信群中有个朋友也是无意中问了一下,说数据库中的表字段想保持一种相对规范的顺序,怎么办?要知道Oracle中这个操作就比较纠结了,因为是按照追加的方式来处...

3526
来自专栏杨建荣的学习笔记

MySQL Online DDL(二)(r11笔记第88天)

对于Online DDL,之前简单分析了一些场景MySQL中的Online DDL(第一篇)(r11笔记第3天),其实有一个很关键的点没提到,那就是online...

3619
来自专栏乐沙弥的世界

临时表空间的管理与受损恢复

      Oracle 临时表空间是Oracle数据库的重要组成部分,尽管该部分并没有cont体系结构上得以展现,但其重要地位也是不容忽视的。尤其是 对于大...

1103
来自专栏禅林阆苑

mysql学习总结08 — 优化(设计层)-索引与分区分表

普通索引:(index) 对关键字没有要求,如果一个索引在多个字段提取关键字,称为复合索引

99140
来自专栏MYSQL轻松学

MYSQL RR隔离级别下MVCC及锁解读

MVCC(Multi-Version Concurrent Control):多版本并发控制,只作用于RC和RR隔离级别,主要是为了避免脏读、非重复读,而非幻读...

4928

扫码关注云+社区