一.業(yè)務(wù)背景
我們現(xiàn)在的業(yè)務(wù)是一款數(shù)據(jù)產(chǎn)品，有不少實(shí)時(shí)計(jì)算和爬取來(lái)的數(shù)據(jù)都匯總到大數(shù)據(jù)倉(cāng)庫(kù)、數(shù)據(jù)挖掘平臺(tái)ODPS上。然后應(yīng)用在讀取這些數(shù)據(jù)時(shí)，這些數(shù)據(jù)會(huì)先導(dǎo)入到并發(fā)讀能力更強(qiáng)，適合結(jié)構(gòu)查詢的MySQL上。數(shù)據(jù)端開發(fā)的同學(xué)在跑定時(shí)任務(wù)時(shí), tps比較高，于是出現(xiàn)了一些線上問題:在開發(fā)過程中發(fā)現(xiàn)對(duì)某一包含unique key(聯(lián)合的唯一索引)的表進(jìn)行并發(fā)插入的時(shí)候，出現(xiàn)大量的死鎖，使得插入幾乎無(wú)法進(jìn)行。于是為了排查問題，請(qǐng)教了DBA以及數(shù)據(jù)庫(kù)事業(yè)部的同學(xué)，最后發(fā)現(xiàn)了問題的所在，特此記錄下來(lái)
二.死鎖現(xiàn)場(chǎng)
1.表結(jié)構(gòu)
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 ‘當(dāng)日時(shí)間’,
……
PRIMARY KEY (id),
UNIQUE KEY uid_cinema_show_date (cinema_id,show_id,now_date),
……
) ENGINE=InnoDB AUTO_INCREMENT=2162973490 DEFAULT CHARSET=utf8 COMMENT=’淘寶電影訂單影院影片數(shù)據(jù)統(tǒng)計(jì)’
2.問題狀況
可以看到出現(xiàn)死鎖的原因是因?yàn)榕坎迦氲臅r(shí)候,該事務(wù)
持有鎖
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
這樣一看確實(shí)奇怪，怎么批量插入不同行怎么會(huì)有死鎖，再看看死鎖日志
（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. 從日志中可以看到兩個(gè)事務(wù)的持鎖情況和等待鎖情況：
a. 事務(wù)一
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.事務(wù)二
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. 補(bǔ)充關(guān)于一些鎖方面的知識(shí)
當(dāng)InnoDB在判斷行鎖是否沖突的時(shí)候，除了最基本的IS IX S X鎖的沖突判斷意外，還有一套更精確的判斷邏輯。除了上面說到的鎖類型，InnoDB還將鎖細(xì)分為如下幾種子類型：
record lock（RK）
鎖直接加在索引記錄上面，鎖住的是key
gap lock（GK）
間隙鎖，鎖定一個(gè)范圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務(wù)的兩次當(dāng)前讀，出現(xiàn)幻讀的情況
next key lock（NK）
行鎖和間隙鎖組合起來(lái)就叫Next-Key Lock
insert intention lock（IK）
如果插入前，該間隙已經(jīng)由gap鎖，那么Insert會(huì)申請(qǐng)插入意向鎖。因?yàn)榱吮苊饣米x，當(dāng)其他事務(wù)持有該間隙的間隔鎖，插入意向鎖就會(huì)被阻塞（不用直接用gap鎖，是因?yàn)間ap鎖不互斥）。
下面畫的就是“精確模式”鎖兼容矩陣
列相加行已有RKGKIKNK
RK0110
GK1111
IK1010
NK0110
insert中對(duì)唯一索引的加鎖邏輯
先做UK沖突檢測(cè)，如果存在目標(biāo)行，先對(duì)目標(biāo)行加S NK（S lock中的next key lock，下同），這個(gè)鎖如果最終插入成功（該記錄在等待期間被其他事務(wù)刪除，此鎖被同時(shí)刪除）
如果1成功，對(duì)對(duì)應(yīng)行加X IK
如果2成功，插入記錄，并對(duì)記錄加X RK（有可能是隱式鎖）
3.鎖的細(xì)節(jié)
1. 前文已分析，一個(gè)insert SQL需要加的鎖依次為 S NK, X IK, X RK、那么加XIK前需要GK或NK。而insert不需要加GK，因此兩個(gè)事務(wù)X IK被申請(qǐng)等待的原因是在申請(qǐng)S NK的過程受到阻塞了。
2. insert完成之后，只會(huì)殘留X RK鎖，這就是兩個(gè)事務(wù)都有X RK的原因，說明它們剛插入完某幾條記錄。
3. 由1,2可以推測(cè)，死鎖是事務(wù)1 的S NK被事務(wù)2的 X RK所阻塞，說明事務(wù)2插入的記錄在事務(wù)1 S NK的范圍內(nèi)。而事務(wù)2的 S NK被 事務(wù)1 阻塞的申請(qǐng)S NK給阻塞，說明事務(wù)1 S NK的范圍要大于事務(wù)2 S NK的范圍。
4. 由第3點(diǎn)推斷，可以證明出事務(wù)2所有的記錄范圍 REC2 是要在 事務(wù)1所有的記錄范圍 REC1之后的,既REC2 < REC1
而插入的業(yè)務(wù)場(chǎng)景的數(shù)據(jù)是:
事務(wù)1
('10076','150686','2017-06-11 08:39:15.866') ,
('10111','150686','2017-06-11 08:39:15.866') ,
('10133','214563','2017-06-11 08:39:15.866') ,
('10171','150686','2017-06-11 08:39:15.866')
事務(wù)2
('15186','150686','2017-06-11 08:39:15.866') ,
('15186','151509','2017-06-11 08:39:15.866') ,
('15186','207522','2017-06-11 08:39:15.866') ,
('15187','151509','2017-06-11 08:39:15.866')
實(shí)際的插入數(shù)據(jù)符合我們的預(yù)期
5.由上面的結(jié)論，我們可以得到一張死鎖循環(huán)圖
四.預(yù)防死鎖
死鎖發(fā)生的條件：
1、資源不能共享，需要只能由一個(gè)進(jìn)程或者線程使用
2、請(qǐng)求且保持，已經(jīng)鎖定的資源自給保持著不釋放
3、不剝奪，自給申請(qǐng)到的資源不能被別人剝奪
4、循環(huán)等待
防止死鎖的途徑就是避免滿足死鎖條件的情況發(fā)生，適合這個(gè)問題解決的方案有:
1、保持事務(wù)簡(jiǎn)短并在一個(gè)批處理中
在同一數(shù)據(jù)庫(kù)中并發(fā)執(zhí)行多個(gè)需要長(zhǎng)時(shí)間運(yùn)行的事務(wù)時(shí)通常發(fā)生死鎖。事務(wù)運(yùn)行時(shí)間越長(zhǎng)，其持有排它鎖或更新鎖的時(shí)間也就越長(zhǎng)，從而堵塞了其它活動(dòng)并可能導(dǎo)致死鎖。保持事務(wù)在一個(gè)批處理中，可以最小化事務(wù)的網(wǎng)絡(luò)通信往返量，減少完成事務(wù)可能的延遲并釋放鎖。
2、使用低隔離級(jí)別
確定事務(wù)是否能在更低的隔離級(jí)別上運(yùn)行。執(zhí)行提交讀允許事務(wù)讀取另一個(gè)事務(wù)已讀取（未修改）的數(shù)據(jù)，而不必等待第一個(gè)事務(wù)完成。使用較低的隔離級(jí)別（例如提交讀）而不使用較高的隔離級(jí)別（例如可串行讀）可以縮短持有共享鎖的時(shí)間，從而降低了鎖定爭(zhēng)奪（比如這次的S NK和X IK 是InnoDB引擎Repeatable Read級(jí)別才有的）。
