转自大佬zxcodestudy的并发插入引发的死锁问题排查

一.业务背景
我们现在的业务是一款数据产品，有不少实时计算和爬取来的数据都汇总到大数据仓库、数据挖掘平台ODPS上。然后应用在读取这些数据时，这些数据会先导入到并发读能力更强，适合结构查询的mysql上。数据端开发的同学在跑定时任务时, tps比较高，于是出现了一些线上问题:在开发过程中发现对某一包含unique key(联合的唯一索引)的表进行并发插入的时候，出现大量的死锁，使得插入几乎无法进行。于是为了排查问题，请教了DBA以及数据库事业部的同学，最后发现了问题的所在，特此记录下来

二.死锁现场
1.表结构

CREATE TABLE tkn_tb_cinema_show_data ( 
…… 
cinema_id bigint(20) DEFAULT NULL COMMENT ‘影院ID’, 
show_id bigint(20) DEFAULT NULL COMMENT ‘影片ID’, 
now_date varchar(32) DEFAULT NULL COMMENT ‘当日时间’, 
…… 
PRIMARY KEY (id), 
UNIQUE KEY uid_cinema_show_date (cinema_id,show_id,now_date), 
…… 
) ENGINE=InnoDB AUTO_INCREMENT=2162973490 DEFAULT CHARSET=utf8 COMMENT=’淘宝电影订单影院影片数据统计’ 
2.问题状况 

可以看到出现死锁的原因是因为批量插入的时候,该事务 
持有锁 
index uid_cinema_show_date of table tkn.tkn_tb_cinema_show_data trx id 73278630826 lock_mode X 
等待锁 
index uid_cinema_show_date of table tkn.tkn_tb_cinema_show_data trx id 73278630826 lock_mode X locks gap before rec insert intention waiting 
这样一看确实奇怪，怎么批量插入不同行怎么会有死锁，再看看死锁日志 
（SHOW ENGINE INNODB STATUS;）

  1. transactions deadlock detected, dumping detailed information.
  2. 2017-06-11 08:41:03 2ac742684700
  3. *** (1) TRANSACTION:
  4. TRANSACTION 73278630816, ACTIVE 1 sec inserting
  5. mysql tables in use 1, locked 1
  6. LOCK WAIT 622 lock struct(s), heap size 79400, 743 row lock(s), undo log entries 388
  7. MySQL thread id 13824253, OS thread handle 0x2ac195786700, query id 53621728233 11.227.64.76 dwexp update
  8. INSERT INTO tkn_tb_cinema_show_data ......
  9. *** (1) HOLDS THE LOCK(S):
  10. RECORD LOCKS space id 29500 page no 398903 n bits 336 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630816 lock_mode X
  11. Record lock, heap no 253 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  12. 0: len 8; hex 80000000000035cb; asc 5 ;;
  13. 1: len 8; hex 80000000000356f1; asc V ;;
  14. 2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
  15. 3: len 8; hex 80000000807f52b2; asc R ;;
  16. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  17. RECORD LOCKS space id 29500 page no 423377 n bits 304 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630816 lock_mode X locks gap before rec insert intention waiting
  18. Record lock, heap no 228 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  19. 0: len 8; hex 8000000000006262; asc bb;;
  20. 1: len 8; hex 8000000000035911; asc Y ;;
  21. 2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
  22. 3: len 8; hex 80000000807fdae4; asc ;;
  23. *** (2) TRANSACTION:
  24. TRANSACTION 73278630826, ACTIVE 1 sec inserting, thread declared inside InnoDB 4836
  25. mysql tables in use 1, locked 1
  26. 2425 lock struct(s), heap size 292392, 3363 row lock(s), undo log entries 1804
  27. MySQL thread id 13824252, OS thread handle 0x2ac742684700, query id 53621728249 11.227.64.76 dwexp update
  28. INSERT INTO tkn_tb_cinema_show_data ......
  29. *** (2) HOLDS THE LOCK(S):
  30. RECORD LOCKS space id 29500 page no 423377 n bits 304 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630826 lock_mode X
  31. Record lock, heap no 228 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  32. 0: len 8; hex 8000000000006262; asc bb;;
  33. 1: len 8; hex 8000000000035911; asc Y ;;
  34. 2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
  35. 3: len 8; hex 80000000807fdae4; asc ;;
  36. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  37. RECORD LOCKS space id 29500 page no 398903 n bits 336 index `uid_cinema_show_date` of table `tkn`.`tkn_tb_cinema_show_data` trx id 73278630826 lock_mode X locks gap before rec insert intention waiting
  38. Record lock, heap no 253 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  39. 0: len 8; hex 80000000000035cb; asc 5 ;;
  40. 1: len 8; hex 80000000000356f1; asc V ;;
  41. 2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
  42. 3: len 8; hex 80000000807f52b2; asc R ;;
  43. *** WE ROLL BACK TRANSACTION (1)

三.分析问题
1.阅读死锁日志 
1. 从日志中可以看到两个事务的持锁情况和等待锁情况： 
a. 事务一 
HOLDS THE LOCK(S) …… lock_mode X 持有X锁 
WAITING FOR THIS LOCK TO BE GRANTED …… X locks gap before rec insert intention waiting 等待insert intention lock 
b.事务二 
HOLDS THE LOCK(S) …… lock_mode X 持有X锁 
WAITING FOR THIS LOCK TO BE GRANTED …… X locks gap before rec insert intention waiting 等待insert intention lock

2. 补充关于一些锁方面的知识

  • 当InnoDB在判断行锁是否冲突的时候，除了最基本的IS IX S X锁的冲突判断意外，还有一套更精确的判断逻辑。除了上面说到的锁类型，InnoDB还将锁细分为如下几种子类型： 
  • record lock（RK） 
  • 锁直接加在索引记录上面，锁住的是key
  • gap lock（GK） 
  • 间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
  • next key lock（NK） 
  • 行锁和间隙锁组合起来就叫Next-Key Lock
  • insert intention lock（IK） 
  • 如果插入前，该间隙已经由gap锁，那么Insert会申请插入意向锁。因为了避免幻读，当其他事务持有该间隙的间隔锁，插入意向锁就会被阻塞（不用直接用gap锁，是因为gap锁不互斥）。 
  • 下面画的就是“精确模式”锁兼容矩阵
列相加\行已有 RK GK IK NK
RK 0 1 1 0
GK 1 1 1 1
IK 1 0 1 0
NK 0 1 1 0

 

  • insert中对唯一索引的加锁逻辑 
    • 先做UK冲突检测，如果存在目标行，先对目标行加S NK（S lock中的next key lock，下同），这个锁如果最终插入成功（该记录在等待期间被其他事务删除，此锁被同时删除）
    • 如果1成功，对对应行加X IK
    • 如果2成功，插入记录，并对记录加X RK（有可能是隐式锁）

 

3.锁的细节 
1. 前文已分析，一个insert SQL需要加的锁依次为 S NK, X IK, X RK、那么加XIK前需要GK或NK。而insert不需要加GK，因此两个事务X IK被申请等待的原因是在申请S NK的过程受到阻塞了。 
2. insert完成之后，只会残留X RK锁，这就是两个事务都有X RK的原因，说明它们刚插入完某几条记录。 
3. 由1,2可以推测，死锁是事务1 的S NK被事务2的 X RK所阻塞，说明事务2插入的记录在事务1 S NK的范围内。而事务2的 S NK被 事务1 阻塞的申请S NK给阻塞，说明事务1 S NK的范围要大于事务2 S NK的范围。 
4. 由第3点推断，可以证明出事务2所有的记录范围 REC2 是要在 事务1所有的记录范围 REC1之后的,既REC2 < REC1 
而插入的业务场景的数据是:

  1. 事务1
  2. ('10076','150686','2017-06-11 08:39:15.866') ,
  3. ('10111','150686','2017-06-11 08:39:15.866') ,
  4. ('10133','214563','2017-06-11 08:39:15.866') ,
  5. ('10171','150686','2017-06-11 08:39:15.866')
  6. 事务2
  7. ('15186','150686','2017-06-11 08:39:15.866') ,
  8. ('15186','151509','2017-06-11 08:39:15.866') ,
  9. ('15186','207522','2017-06-11 08:39:15.866') ,
  10. ('15187','151509','2017-06-11 08:39:15.866')
     

实际的插入数据符合我们的预期 
5.由上面的结论，我们可以得到一张死锁循环图  

 

四.预防死锁
死锁发生的条件： 
1、资源不能共享，需要只能由一个进程或者线程使用 
2、请求且保持，已经锁定的资源自给保持着不释放 
3、不剥夺，自给申请到的资源不能被别人剥夺 
4、循环等待 
防止死锁的途径就是避免满足死锁条件的情况发生，适合这个问题解决的方案有: 
1、保持事务简短并在一个批处理中 
在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长，其持有排它锁或更新锁的时间也就越长，从而堵塞了其它活动并可能导致死锁。保持事务在一个批处理中，可以最小化事务的网络通信往返量，减少完成事务可能的延迟并释放锁。 
2、使用低隔离级别 
确定事务是否能在更低的隔离级别上运行。执行提交读允许事务读取另一个事务已读取（未修改）的数据，而不必等待第一个事务完成。使用较低的隔离级别（例如提交读）而不使用较高的隔离级别（例如可串行读）可以缩短持有共享锁的时间，从而降低了锁定争夺（比如这次的S NK和X IK 是InnoDB引擎Repeatable Read级别才有的）。