一.業務背景
我們現在的業務是一款數據產品，有不少實時計算和爬取來的數據都匯總到大數據倉庫、數據挖掘平臺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. 從日志中可以看到兩個事務的持鎖情況和等待鎖情況：
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鎖不互斥）。
下面畫的就是“精確模式”鎖兼容矩陣
列相加行已有RKGKIKNK
RK0110
GK1111
IK1010
NK0110
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
('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')
事務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')
實際的插入數據符合我們的預期
5.由上面的結論，我們可以得到一張死鎖循環圖
四.預防死鎖
死鎖發生的條件：
1、資源不能共享，需要只能由一個進程或者線程使用
2、請求且保持，已經鎖定的資源自給保持著不釋放
3、不剝奪，自給申請到的資源不能被別人剝奪
4、循環等待
防止死鎖的途徑就是避免滿足死鎖條件的情況發生，適合這個問題解決的方案有:
1、保持事務簡短并在一個批處理中
在同一數據庫中并發執行多個需要長時間運行的事務時通常發生死鎖。事務運行時間越長，其持有排它鎖或更新鎖的時間也就越長，從而堵塞了其它活動并可能導致死鎖。保持事務在一個批處理中，可以最小化事務的網絡通信往返量，減少完成事務可能的延遲并釋放鎖。
2、使用低隔離級別
確定事務是否能在更低的隔離級別上運行。執行提交讀允許事務讀取另一個事務已讀取（未修改）的數據，而不必等待第一個事務完成。使用較低的隔離級別（例如提交讀）而不使用較高的隔離級別（例如可串行讀）可以縮短持有共享鎖的時間，從而降低了鎖定爭奪（比如這次的S NK和X IK 是InnoDB引擎Repeatable Read級別才有的）。
