死锁案例之二

一 前言 死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。本文源于我们的生产案例:并发申请gap锁导致的死锁案例,与之前的 死锁案例一不同,本案例是因为RR模式下两个事务中的sql可以获取同一个gap锁,导致对方事务的insert 相互等待,导致死锁的。 二 案例分析 2.1 测试环境准备

Percona server 5.6.24 事务隔离级别为RR

  1. CREATE TABLE `t4` (
  2. `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT ,
  3. `kdt_id` int(11) unsigned NOT NULL ,
  4. `admin_id` int(11) unsigned NOT NULL ,
  5. `biz` varchar(20) NOT NULL DEFAULT '1' ,
  6. `role_id` int(11) unsigned NOT NULL ,
  7. `shop_id` int(11) unsigned NOT NULL DEFAULT '0' ,
  8. `operator` varchar(20) NOT NULL DEFAULT '0' ,
  9. `operator_id` int(11) NOT NULL DEFAULT '0' ,
  10. `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  11. `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  12. PRIMARY KEY (`id`),
  13. UNIQUE KEY `uniq_kid_aid_biz_rid` (`kdt_id`,`admin_id`,`role_id`,`biz`)
  14. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  15. INSERT INTO `t4` (`id`, `kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
  16. VALUES
  17. (1,10,1,'retail',1,0,'0',0,'2017-05-09 15:55:26','2017-05-09 15:55:26'),
  18. (2,20,1,'retail',1,0,'0',0,'2017-05-09 15:55:40','2017-05-09 15:55:40'),
  19. (3,30,1,'retail',1,0,'0',0,'2017-05-09 15:55:55','2017-05-09 15:55:55'),
  20. (4,40,1,'retail',1,0,'0',0,'2017-05-09 15:56:06','2017-05-09 15:56:06'),
  21. (5,50,1,'retail',1,0,'0',0,'2017-05-09 15:56:16','2017-05-09 15:56:16');

2.2 本测试案例场景是两个事务删除不存的行,然后在insert记录。

T2

T1

test [RW] 02:50:27 >begin;Query OK, 0 rows affected (0.00 sec)

test [RW] 02:50:27 >begin;Query OK, 0 rows affected (0.00 sec)

test [RW] 02:50:34 >delete from t4 where kdt_id = 15 and admin_id = 1 and biz = 'retail' and role_id = '1';

test [RW] 02:50:41 >delete from t4 where kdt_id = 18 and admin_id = 2 and biz = 'retail' and role_id = '1';

test [RW] 02:50:43 >insert into t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) -> VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

test [RW] 02:51:02 >INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) -> VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

2.3 死锁日志

  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2017-09-11 14:51:03 7f78eaf25700
  5. *** (1) TRANSACTION:
  6. TRANSACTION 462308535, ACTIVE 20 sec inserting
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
  9. MySQL thread id 3584515, OS thread handle 0x7f78ea5f5700, query id 780258123 localhost root update
  10. insert into t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
  11. VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
  12. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  13. RECORD LOCKS space id 225 page no 4 n bits 72 index `uniq_kid_aid_biz_rid` of table `test`.`t4` trx id 462308535 lock_mode X locks gap before rec insert intention waiting
  14. *** (2) TRANSACTION:
  15. TRANSACTION 462308534, ACTIVE 29 sec inserting, thread declared inside InnoDB 5000
  16. mysql tables in use 1, locked 1
  17. 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
  18. MySQL thread id 3584572, OS thread handle 0x7f78eaf25700, query id 780258153 localhost root update
  19. INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
  20. VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
  21. *** (2) HOLDS THE LOCK(S):
  22. RECORD LOCKS space id 225 page no 4 n bits 72 index `uniq_kid_aid_biz_rid` of table `test`.`t4` trx id 462308534 lock_mode X locks gap before rec
  23. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  24. RECORD LOCKS space id 225 page no 4 n bits 72 index `uniq_kid_aid_biz_rid` of table `test`.`t4` trx id 462308534 lock_mode X locks gap before rec insert intention waiting
  25. *** WE ROLL BACK TRANSACTION (2)

2.4 死锁日志分析 首先根据《死锁案例一》 和《一个最不可思议的MySQL死锁分析》中强调 delete 不存在的记录是要加上GAP锁,事务日志中显示Lock_mode X wait . a. T2 delete from t4 where kdt_id = 15 and admin_id = 1 and biz = 'retail' and role_id = '1'; 符合条件的记录不存在,导致T2 先持有了(lock_mode X locks gap before rec) 锁住[(2,20,1,'retail',1,0)-(3,30,1,'retail',1,0)]的区间 ,防止符合条件的记录插入。 b. T1的delete 于T2的delete一样 同样申请了 (lock_mode X locks gap before rec) 锁住[(2,20,1,'retail',1,0)-(3,30,1,'retail',1,0)]的区间 。

  1. It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

c. T1 的insert 语句申请插入意向锁,但是插入意向锁和T2持有的X GAP (lock_mode X locks gap before rec) 冲突,故等待T2中的GAP 锁释放。

  1. Gap locks in InnoDB are “purely inhibitive”, which means they only stop other transactions from inserting to the gap. They do not prevent different transactions from taking gap locks on the same gap. Thus, a gap X-lock has the same effect as a gap S-lock.

d. T2 的insert 语句申请插入意向锁,但是插入意向锁和T1持有 X GAP (lock_mode X locks gap before rec) 冲突,故等待T1中的GAP 锁释放。 T1(INSERT )等待T2(DELETE),T2(INSERT)等待T1(DELETE) 故而循环等待,出现死锁。 有兴趣的读者朋友可以测试一下 delete 存在记录的场景。

2.6 如何解决呢?

a 先select 检查一下看看是否存在,然后在删除。这里也存在两个或者多个会话并发执行同一个select where条件的,这里需要开发同学做处理。

b 使用insert into on deuplicate key语法不存在则插入,而不是先删除,再插入。

三 小结

RR事务隔离级别和GAP锁是导致死锁的常见原因,但是业务逻辑设计不合理也会出发死锁,本文的案例通过修改业务逻辑最终将死锁解决。

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-10-11

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏码神联盟

mysql数据库常见锁机制

关于互联网常见层次架构,由于小编还没整理完毕(预计周四推送),先来一篇数据库的干货,来满足下大家的胃口,关于mysql的行级锁、表级锁、页级锁的分析,这个在行业...

44490
来自专栏java一日一条

MySQL分页性能优化指南

很多应用往往只展示最新或最热门的几条记录,但为了旧记录仍然可访问,所以就需要个分页的导航栏。然而,如何通过MySQL更好的实现分页,始终是比较令人头疼的问题。虽...

16330
来自专栏JetpropelledSnake

SQL学习笔记之MySQL索引知识点

之前写过一篇Mysql B+树学习,简单的介绍了B+数以及MySql使用B+树的原因, 有了这些基础知识点,对MySql索引的类型以及索引使用的一些技巧,就...

8110
来自专栏Java帮帮-微信公众号-技术文章全总结

【数据库】MySQL进阶一、主外键讲解

MySQL进阶主外键讲解 1.什么是外键: 主键:是唯一标识一条记录,不能有重复的,不允许为空,用来保证数据完整性 外键:是另一表的主键, 外键可...

38470
来自专栏Python

Innodb与Myisam引擎的区别与应用场景

1.区别: (1)事务处理: MyISAM是非事务安全型的,而InnoDB是事务安全型的(支持事务处理等高级处理); (2)锁机制不同: MyISAM是表级锁,...

41770
来自专栏用户画像

sql server 实验5.2 触发器的建立与使用

一、实验目的                                                     

30520
来自专栏技术记录

mySQL优化方案

1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得...

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

生产环境sql语句调优实战第八篇(r3笔记第24天)

生产环境中的sql语句执行时间是很关键的性能指标,如果某个sql语句执行几个小时,优化以后几分钟,几十秒的话。会有很大的成就感,同时如果某个sql语句执行10秒...

28970
来自专栏PHP在线

MYSQL 优化常用方法

1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得...

36340
来自专栏程序猿

MySQL优化方案(一)优化SQL脚本与索引

MySQL的优化方案有哪一些? 本文记录MySQL优化方案 ,梗概如下: 优化SQL 优化索引 (一)优化SQL 1、通过MySQL自有的优化语句 优化SQL语...

46870

扫码关注云+社区

领取腾讯云代金券