【Mysql】兩條insert 語句產生的死鎖

小亮520cl發表於2017-10-24
MySql
背景：檢視status日誌發現兩條insert 出現了死鎖
  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2017-10-24 12:53:56 7f8fbc165700
  5. *** (1) TRANSACTION:
  6. TRANSACTION 596252578, ACTIVE 0 sec inserting
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 14 lock struct(s), heap size 2936, 7 row lock(s), undo log entries 7
  9. MySQL thread id 17066316, OS thread handle 0x7f8eef3cf700, query id 2080508355 192.168.1.13 userservice update
  10. insert into
  11.         svc_order_service_item
  12.          ( work_order_id,
  13.         quantity    
  14.  )
  15.          values ( 2373314，1
  16. )
  17. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  18. RECORD LOCKS space id 388 page no 27032 n bits 616 index `idx_svcorderserviceitem_workorderid_quantity` of table `ecejservice`.`svc_order_service_item` trx id 596252578 lock_mode X insert intention waiting
  19. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
  20.  0: len 8; hex 73757072656d756d; asc supremum;;

  22. *** (2) TRANSACTION:
  23. TRANSACTION 596252580, ACTIVE 0 sec inserting
  24. mysql tables in use 1, locked 1
  25. 15 lock struct(s), heap size 2936, 7 row lock(s), undo log entries 8
  26. MySQL thread id 17061883, OS thread handle 0x7f8fbc165700, query id 2080508367 192.168.1.12 userservice update
  27. insert into
  28.         
  29.         svc_order_service_item
  30.     
  31.          ( work_order_id,
  32.         quantity,
  33.      )
  34.          values ( 2373315,
  35.         1
  36.     
  37.  )
  38. *** (2) HOLDS THE LOCK(S): --持有一個讀鎖
  39. RECORD LOCKS space id 388 page no 27032 n bits 616 index `idx_svcorderserviceitem_workorderid_quantity` of table `ecejservice`.`svc_order_service_item` trx id 596252580 lock_mode X
  40. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
  41.  0: len 8; hex 73757072656d756d; asc supremum;;

  43. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  44. RECORD LOCKS space id 388 page no 27032 n bits 616 index `idx_svcorderserviceitem_workorderid_quantity` of table `ecejservice`.`svc_order_service_item` trx id 596252580 lock_mode X insert intention waiting
  45. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
  46.  0: len 8; hex 73757072656d756d; asc supremum;;

  48. *** WE ROLL BACK TRANSACTION (1)
  49. ------------
注意：在innodb 日誌中如果提示 lock mode S /lock mode X ，其實都是gap鎖，如果是行記錄鎖 會提示but not gap ,請讀者朋友們在自己分析死鎖日誌的時候注意。

分析：

1.表上有聚集索引和二級索引，死鎖發生在二級索引idx_svcorderserviceitem_workorderid_quantity上

2.
  1. RECORD LOCKS space id 388 page no 27032 n bits 616 index `idx_svcorderserviceitem_workorderid_quantity` of table `ecejservice`.`svc_order_service_item` trx id 596252578 lock_mode X insert intention waiting
可以確定，這個x鎖不是由於INSERT產生的，因為 INSERT可能產生的鎖包括檢查dup key時的s鎖，隱式鎖轉換為顯式鎖（not gap，要在二級索引上產生lock_mode為X的LOCK_ORDINARY型別的鎖(包括記錄及記錄前面的gap)，據我所知一般是根據二級索引掃描進行記錄更新導致的。

3. 根據
LOCK WAIT 14 lock struct(s), heap size 2936, 7 row lock(s), undo log entries 7
有7個undo entires，而單純的INSERT一條記錄只有一個undo entry，因此可以推斷除了INSERT，必然還有別的操作

基於以上，事務除了INSERT，可能還存在DELETE/UPDATE，並且這些操作是走的二級索引來查詢更新記錄。

一個簡單但不完全相同的重現步驟：
DROP TABLE t1;
CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB ;
insert into t1(a, b,c) values(1,2,3),(5,4,6),(8, 7,9),(12,12,19),(15,15,11);

session1:

begin;
delete from t1 where b = 12;
//二級索引上lock_mode X、lock_mode X locks gap before rec以及主鍵上的lock_mode X locks rec but not gap
二級索引：heap_no=5, type_mode=3  （12上的LOCK_ORDINARY型別鎖，包括記錄和記錄前的GAP）
聚集索引：heap_no=5，type_mode=1027
二級索引：heap_no=6，type_mode=547(15上的GAP鎖)
session2:
begin;
delete from t1 where b = 7;
//二級索引上lock_mode X、lock_mode X locks gap before rec以及主鍵上的lock_mode X locks rec but not gap
二級索引：heap_no=4，type_mode=3       （7上的LOCK_ORDINARY型別鎖，包括記錄和記錄前的GAP）
聚集索引：heap_no=4，type_mode=1027
二級索引：heap_no=5，type_mode=547    (記錄12上的GAP鎖)
session1:
insert into t1 values (NULL, 6,10);
//新插入記錄聚集索引無衝突插入成功，二級索引等待插入意向鎖（lock_mode X locks gap before rec insert intention waiting）
二級索引,heap_no=4, type_mode=2819 （請求記錄7上面的插入意向鎖LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION, 需要等待session2
session2:
insert into t1 values (NULL, 7,10);
二級索引:heap_no=5,  type_mode=2819  (請求記錄12上的插入意向鎖LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION，需要等待session1)
互相等待，導致發生死鎖
從列印的死鎖資訊來看，基本和線上發生的死鎖現象是一致的。


再舉一個例子
    1. mysql> select * from test01;
    2. +----+-----+
    3. | id | app |
    4. +----+-----+
    5. | 1 | 01 |
    6. | 2 | 02 |
    7. | 5 | 03 |
    8. | 10 | 03 |
    9. | 6 | 04 |
    10. | 7 | 05 |
    11. | 8 | 06 |
    12. | 9 | 06 |
    13. | 11 | 06 |
    14. | 12 | 07 |
    15. | 13 | 08 |
    16. | 14 | 09 |
    17. | 15 | 09 |
    18. +----+-----+
    19. 13 rows in set (0.00 sec)

  2. session1：
  3. mysql> select now();start TRANSACTION;      
  4. +---------------------+ 
  5. | now() |
  6. +---------------------+
  7. | 2018-01-25 16:08:46 |
  8. +---------------------+
  9. 1 row in set (0.00 sec)

  11. Query OK, 0 rows affected (0.00 sec)

  13. mysql> select * from test01 where app='05' for update; ---第1步 鎖住【6.04】-【7.05】以及【7.05】-【8.06】 兩段區間
  14. +----+-----+
  15. | id | app |
  16. +----+-----+
  17. | 7 | 05 |
  18. +----+-----+
  19. 1 row in set (0.00 sec)

  21. mysql> insert into test01(app) values ('07');           --第三步 等待第二步釋放
  22. Query OK, 1 row affected (23.24 sec)



  26. session2：
  27. mysql> select * from test01 where app='08' for update;  --第二步 鎖住【12,07】-【13，08】以及【13,08】-【14,09】兩段區間
  28. +----+-----+
  29. | id | app |
  30. +----+-----+
  31. | 13 | 08 |
  32. +----+-----+
  33. 1 row in set (0.00 sec)

  35. mysql> insert into test01(app) values ('04');                  ----第四步 等待第一步釋放，，於是死鎖
  36. ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction


檢視死鎖日誌:
  1. LATEST DETECTED DEADLOCK
  2. ------------------------
  3. 2018-01-25 16:09:54 0x7f07d23ff700
  4. *** (1) TRANSACTION:
  5. TRANSACTION 5375, ACTIVE 51 sec inserting
  6. mysql tables in use 1, locked 1
  7. LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
  8. MySQL thread id 2294, OS thread handle 139671567841024, query id 42463 localhost root update
  9. insert into test01(app) values ('07')
  10. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  11. RECORD LOCKS space id 64 page no 4 n bits 80 index idx_app of table `devops`.`test01` trx id 5375 lock_mode X locks gap before rec insert intention waiting
  12. Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  13.  0: len 2; hex 3038; asc 08;;
  14.  1: len 4; hex 0000000d; asc ;;

  16. *** (2) TRANSACTION:
  17. TRANSACTION 5376, ACTIVE 38 sec inserting
  18. mysql tables in use 1, locked 1
  19. 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
  20. MySQL thread id 2293, OS thread handle 139671568905984, query id 42464 localhost root update
  21. insert into test01(app) values ('04')

  1. *** (2) HOLDS THE LOCK(S):

  1. RECORD LOCKS space id 64 page no 4 n bits 80 index idx_app of table `devops`.`test01` trx id 5376 lock_mode X
  2. Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  3.  0: len 2; hex 3038; asc 08;;
  4.  1: len 4; hex 0000000d; asc ;;

  6. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  7. RECORD LOCKS space id 64 page no 4 n bits 80 index idx_app of table `devops`.`test01` trx id 5376 lock_mode X locks gap before rec insert intention waiting
  8. Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  9.  0: len 2; hex 3035; asc 05;;
  10.  1: len 4; hex 00000007; asc ;;

  12. *** WE ROLL BACK TRANSACTION (2)
  13. ------------

死鎖日誌是不是和上面的一樣？


參考：
http://blog.itpub.net/22664653/viewspace-2145068/    ----楊奇龍 
  ---insert ..select 語句產生死鎖
http://blog.itpub.net/7728585/viewspace-2146183/   ---insert ..select 語句產生死鎖--八怪

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

相關文章