記一次線上MySQL資料庫死結問題

標籤：ack   申請   b16   type   會同   互斥   engine   一個   l資料庫   

        最近線上項目報了一個MySQL死結（DealLock）錯誤，雖說對業務上是沒有什麼影響的，由於自己對資料庫鎖這塊瞭解不是很多，之前也沒怎麼的線上上碰到過。這次剛好遇到了，便在此記錄一下。 

• 出現死結問題背景

        項目層面：報錯的項目做的是一個批量下單的動作，會同時寫入多條訂單資料，代碼之前寫的是一個事務中一個迴圈一條一條insert到資料庫（至於為啥沒用批量插入就不追究了，曆史原因了）。        資料庫層面：一張test表（非線上真實表），比較重要的是有一個 type 和 name的唯一索引。 交易隔離等級: read commited

CREATE TABLE `test` (`id`  bigint(11) NOT NULL ,`name`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,`type`  tinyint(4) NULL DEFAULT NULL ,`uid`  int(11) NULL DEFAULT NULL ,PRIMARY KEY (`id`),UNIQUE INDEX `uniq_type_name` (`type`, `name`) USING BTREE)ENGINE=InnoDBDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ciROW_FORMAT=COMPACT;

 

    出現死結的情況是批量下單的介面，外部重複請求了兩次，兩次相隔10毫秒。    兩次請求執行的sql（一次請求會執行三條insert）除了主鍵id不一樣，其他都是一樣的：如下 

insert into test(id, name, type, uid) values(1, "DT590", 3,  1001);insert into test(id, name, type, uid) values(2, "DT589", 3,  1001);insert into test(id, name, type, uid) values(3, "DT588", 3,  1001);

 

 報錯的死結日誌：

------------------------LATEST DETECTED DEADLOCK------------------------2018-06-21 10:51:03 2b16deb03700*** (1) TRANSACTION:TRANSACTION 1905650677, ACTIVE 0.001 sec insertingmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1LOCK BLOCKING MySQL thread id: 16983306 block 34208692MySQL thread id 34208692, OS thread handle 0x2b2203b0b700, query id 9093982364 172.24.18.106 app_redcliffc updateINSERT INTO `test` (id, name, type, uid) VALUES (4, ‘DT590‘, 3, 1001)*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 138 page no 16492 n bits 408 index `uniq_type_name` of table `db`.`test` trx id 1905650677 lock mode S waitingRecord lock, heap no 341 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 4; hex 80000048; asc    H;;1: len 10; hex 44543631393230363835; asc DT61920685;;2: len 8; hex 0461116807c09a00; asc  a h    ;; *** (2) TRANSACTION:TRANSACTION 1905650675, ACTIVE 0.004 sec insertingmysql tables in use 1, locked 13 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 2MySQL thread id 16983306, OS thread handle 0x2b16deb03700, query id 9093982366 172.24.18.105 app_redcliffc updateINSERT INTO `test` (id, name, type, uid) VALUES (2, ‘DT589‘, 3, 1001)*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 138 page no 16492 n bits 408 index `uniq_type_name` of table `db`.`test` trx id 1905650675 lock_mode X locks rec but not gapRecord lock, heap no 341 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 4; hex 80000048; asc    H;;1: len 10; hex 44543631393230363835; asc DT61920685;;2: len 8; hex 0461116807c09a00; asc  a h    ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 138 page no 16492 n bits 408 index `uniq_type_name` of table `db`.`test` trx id 1905650675 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 341 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 4; hex 80000048; asc    H;;1: len 10; hex 44543631393230363835; asc DT61920685;;2: len 8; hex 0461116807c09a00; asc  a h    ;; *** WE ROLL BACK TRANSACTION (1)

 

• 問題分析

　　　死結日誌分析

session1		session2
insert into test(id, name, type, uid) values(1, "DT590", 3,  1001);	事務一先到，先插入第一條記錄DT590，成功
insert into test(id, name, type, uid) values(2, "DT589", 3,  1001);	事務一繼續插入第二天DT589記錄，這個時候事務二請求到了，開始插入第一條記錄DT590，然後就報出死結，事務二復原，事務一成功執行	insert into test(id, name, type, uid) values(1, "DT590", 3,  1001);

  

session1	持鎖	session2	持鎖
insert into test(id, name, type, uid) values(1, "DT590", 3,  1001);	插入一條資料庫中沒有的記錄，對DT590這條記錄加了一個x鎖
insert into test(id, name, type, uid) values(2, "DT589", 3,  1001);	這時事務一插入DT589時候，發現這條記錄已經有了一個gap lock（DT589這條記錄剛好被事務二插DT590時候申請的gap lock包含了），會先申請一個insert intention waiting插入意圖鎖定，這個鎖和事務二持有gap lock互斥，發生死結。事務一在等事務二釋放這條記錄gap lock， 事務二在等事務一釋放DT590 X鎖	insert into test(id, name, type, uid) values(1, "DT590", 3,  1001);	事務二插入有唯一索引DT590這條記錄，發現這條記錄上已經有了x鎖，所以會申請一個該條記錄的s鎖和gap lock

 

• 相關一些鎖知識

        InnoDB鎖細分為如下幾種子類型：                  record lock（RK）  鎖直接加在索引記錄上面，鎖住的是key                 gap lock（GK）  間隙鎖，鎖定一個範圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況                 next key lock（NK）  行鎖和間隙鎖組合起來就叫Next-Key Lock                 insert intention lock（IK）  如果插入前，該間隙已經由gap鎖，那麼Insert會申請插入意圖鎖定。因為了避免幻讀，當其他事務持有該間隙的間隔鎖，插入意圖鎖定就會被阻塞（不用直接用gap鎖，是因為gap鎖不互斥）     insert中對唯一索引的加鎖邏輯 : 先做唯一索引衝突檢測，如果存在目標行，會先對目標行加S NK， 

• 總結

        1.保證事務簡短並在一個批處理中，避免出現迴圈插入死結問題         這裡的情境記錄死結是並發插入多條記錄，順序一樣出現的死結，在並發插入中如果順序不一樣出現死結的機率會更大。 

記一次線上MySQL資料庫死結問題