江苏龙网

  1. 主页 > 生活百科 > >

并发插入引发的死锁问题排查

死锁

一.业务背景
我们现在的业务是一款数据产品 ， 有不少实时计算和爬取来的数据都汇总到大数据仓库、数据挖掘平台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;）
transactions deadlock detected, dumping detailed information.
2017-06-11 08:41:03 2ac742684700
*** (1) TRANSACTION:
TRANSACTION 73278630816, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 622 lock struct(s), heap size 79400, 743 row lock(s), undo log entries 388
MySQL thread id 13824253, OS thread handle 0x2ac195786700, query id 53621728233 11.227.64.76 dwexp update
INSERT INTO tkn_tb_cinema_show_data ......
*** (1) HOLDS THE LOCK(S):
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
Record lock, heap no 253 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 80000000000035cb; asc 5 ;;
1: len 8; hex 80000000000356f1; asc V ;;
2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
3: len 8; hex 80000000807f52b2; asc R ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
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
Record lock, heap no 228 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000006262; asc bb;;
1: len 8; hex 8000000000035911; asc Y ;;
2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
3: len 8; hex 80000000807fdae4; asc ;;
*** (2) TRANSACTION:
TRANSACTION 73278630826, ACTIVE 1 sec inserting, thread declared inside InnoDB 4836
【并发插入引发的死锁问题排查】mysql tables in use 1, locked 1
2425 lock struct(s), heap size 292392, 3363 row lock(s), undo log entries 1804
MySQL thread id 13824252, OS thread handle 0x2ac742684700, query id 53621728249 11.227.64.76 dwexp update
INSERT INTO tkn_tb_cinema_show_data ......
*** (2) HOLDS THE LOCK(S):
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
Record lock, heap no 228 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000006262; asc bb;;
1: len 8; hex 8000000000035911; asc Y ;;
2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
3: len 8; hex 80000000807fdae4; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
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
Record lock, heap no 253 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 80000000000035cb; asc 5 ;;
1: len 8; hex 80000000000356f1; asc V ;;
2: len 10; hex 323031372d30362d3138; asc 2017-06-18;;
3: len 8; hex 80000000807f52b2; asc R ;;
*** WE ROLL BACK TRANSACTION (1)
 
三.分析问题
1.阅读死锁日志
1. 从日志中可以看到两个事务的持锁情况和等待锁情况：


推荐阅读


上一篇：十大长寿食物是什么？

下一篇：世界最深的洋是什么？