【MySQL】死鎖案例之二

楊奇龍發表於2017-09-17
MySql
一 前言
    死鎖，其實是一個很有意思，也很有挑戰的技術問題，大概每個DBA都會在工作過程中遇見過 。關於死鎖我會持續寫一個系列的案例分析，希望能夠對想了解死鎖的朋友有所幫助。本文源於我們的生產案例:併發申請gap鎖導致的死鎖案例,與之前的 死鎖案例一不同，本案例是因為RR模式下兩個事務中的sql可以獲取同一個gap鎖，導致對方事務的insert 相互等待，導致死鎖的。
二 案例分析
測試環境準備
Percona server 5.6.24  事務隔離級別為RR
  1. CREATE TABLE `t4` (
  2.   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT ,
  3.   `kdt_id` int(11) unsigned NOT NULL ,
  4.   `admin_id` int(11) unsigned NOT NULL ,
  5.   `biz` varchar(20) NOT NULL DEFAULT '1' ,
  6.   `role_id` int(11) unsigned NOT NULL ,
  7.   `shop_id` int(11) unsigned NOT NULL DEFAULT '0' ,
  8.   `operator` varchar(20) NOT NULL DEFAULT '0' ,
  9.   `operator_id` int(11) NOT NULL DEFAULT '0' ,
  10.   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  11.   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間',
  12.   PRIMARY KEY (`id`),
  13.   UNIQUE KEY `uniq_kid_aid_biz_rid` (`kdt_id`,`admin_id`,`role_id`,`biz`)
  14. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

  16. INSERT INTO `t4` (`id`, `kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
  17. VALUES
  18.  (1,10,1,'retail',1,0,'0',0,'2017-05-09 15:55:26','2017-05-09 15:55:26'),
  19.  (2,20,1,'retail',1,0,'0',0,'2017-05-09 15:55:40','2017-05-09 15:55:40'),
  20.  (3,30,1,'retail',1,0,'0',0,'2017-05-09 15:55:55','2017-05-09 15:55:55'),
  21.  (4,40,1,'retail',1,0,'0',0,'2017-05-09 15:56:06','2017-05-09 15:56:06'),
  22.  (5,50,1,'retail',1,0,'0',0,'2017-05-09 15:56:16','2017-05-09 15:56:16');
2.2 測試用例
 本測試案例場景是兩個事務刪除不存的行，然後在insert記錄。
T2 T1
test [RW] 02:50:27 >begin;
Query OK, 0 rows affected (0.00 sec) 		test [RW] 02:50:27 >begin;
Query OK, 0 rows affected (0.00 sec)
test [RW] 02:50:34 >delete from t4 where kdt_id = 15 and admin_id = 1 
and biz = 'retail' and role_id = '1';
test [RW] 02:50:41 >delete from t4 where kdt_id = 18 and admin_id = 2 and biz = 'retail' and role_id = '1';
test [RW] 02:50:43 >insert into t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
    -> VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);
test [RW] 02:51:02 >INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
    -> VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

2.3 死鎖日誌
  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2017-09-11 14:51:03 7f78eaf25700
  5. *** (1) TRANSACTION:
  6. TRANSACTION 462308535, ACTIVE 20 sec inserting
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
  9. MySQL thread id 3584515, OS thread handle 0x7f78ea5f5700, query id 780258123 localhost root update
  10. insert into t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
  11. VALUES('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
  12. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  13. RECORD LOCKS space id 225 page no 4 n bits 72 index `uniq_kid_aid_biz_rid` of table `test`.`t4` trx id 462308535 lock_mode X locks gap before rec insert intention waiting
  14. *** (2) TRANSACTION:
  15. TRANSACTION 462308534, ACTIVE 29 sec inserting, thread declared inside InnoDB 5000
  16. mysql tables in use 1, locked 1
  17. 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
  18. MySQL thread id 3584572, OS thread handle 0x7f78eaf25700, query id 780258153 localhost root update
  19. INSERT INTO t4(`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)
  20. VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
  21. *** (2) HOLDS THE LOCK(S):
  22. RECORD LOCKS space id 225 page no 4 n bits 72 index `uniq_kid_aid_biz_rid` of table `test`.`t4` trx id 462308534 lock_mode X locks gap before rec
  23. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  24. RECORD LOCKS space id 225 page no 4 n bits 72 index `uniq_kid_aid_biz_rid` of table `test`.`t4` trx id 462308534 lock_mode X locks gap before rec insert intention waiting
  25. *** WE ROLL BACK TRANSACTION (2)
2.4 死鎖日誌分析
   首先根據《死鎖案例一》 和《一個最不可思議的MySQL死鎖分析》中強調 delete 不存在的記錄是要加上GAP鎖，事務日誌中顯示Lock_mode X wait .
a T2 delete from t4 where kdt_id = 15 and admin_id = 1  and biz = 'retail' and role_id = '1'; 符合條件的記錄不存在，導致T2 先持有了(lock_mode X locks gap before rec) 鎖住[(2,20,1,'retail',1,0)-(3,30,1,'retail',1,0)]的區間 ，防止符合條件的記錄插入。
b T1的delete 於T2的delete一樣 同樣申請了 (lock_mode X locks gap before rec) 鎖住[(2,20,1,'retail',1,0)-(3,30,1,'retail',1,0)]的區間 。

  1. It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.
c T1 的insert 語句申請插入意向鎖，但是插入意向鎖和T2持有的X GAP (lock_mode X locks gap before rec) 衝突，故等待T2中的GAP 鎖釋放。
  1. Gap locks in InnoDB are “purely inhibitive”, which means they only stop other transactions from inserting to the gap. They do not prevent different transactions from taking gap locks on the same gap. Thus, a gap X-lock has the same effect as a gap S-lock.
d T2 的insert 語句申請插入意向鎖，但是插入意向鎖和T1持有 X GAP (lock_mode X locks gap before rec) 衝突，故等待T1中的GAP 鎖釋放。
T1（INSERT ）等待T2(DELETE),T2(INSERT)等待T1(DELETE) 故而迴圈等待，出現死鎖。
有興趣的讀者朋友可以測試一下 delete 存在記錄的場景。


2.6 如何解決呢？
   a 先select 檢查一下看看是否存在，然後在刪除。這裡也存在兩個或者多個會話併發執行同一個select where條件的，這裡需要開發同學做處理。
   b insert into on deuplicate key .
三 小結
    RR事務隔離級別和GAP鎖是導致死鎖的常見原因，但是業務邏輯設計不合理也會出發死鎖,本文的案例通過修改業務邏輯最終將死鎖解決。
如果您覺得能從本文收益，可以請北在南方一瓶飲料 ^_^


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-2145072/，如需轉載，請註明出處，否則將追究法律責任。

相關文章