日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

RELATEED CONSULTING
相關咨詢
選擇下列產品馬上在線溝通
服務時間：8:30-17:00
你可能遇到了下面的問題
關閉右側工具欄

新聞中心

這里有您想知道的互聯網營銷解決方案
記一次MySQL死鎖排查過程

背景

讓客戶滿意是我們工作的目標，不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領域值得信任、有價值的長期合作伙伴，公司提供的服務項目有：申請域名、網頁空間、營銷軟件、網站建設、港閘網站維護、網站推廣。

以前接觸到的數據庫死鎖，都是批量更新時加鎖順序不一致而導致的死鎖，但是上周卻遇到了一個很難理解的死鎖。借著這個機會又重新學習了一下 mysql 的死鎖知識以及常見的死鎖場景。在多方調研以及和同事們的討論下終于發(fā)現了這個死鎖問題的成因，收獲頗多。雖然是后端程序員，我們不需要像 DBA 一樣深入地去分析與鎖相關的源碼，但是如果我們能夠掌握基本的死鎖排查方法，對我們的日常開發(fā)還是大有裨益的。

PS：本文不會介紹死鎖的基本知識，mysql 的加鎖原理可以參考本文的參考資料提供的鏈接。

死鎖起因

先介紹一下數據庫和表情況，因為涉及到公司內部真是的數據，所以以下都做了模擬，不會影響具體的分析。

我們采用的是 5.5 版本的 mysql 數據庫，事務隔離級別是默認的 RR（Repeatable-Read），采用 innodb 引擎。假設存在 test 表：

 
 
 
      
  
  
  1. CREATE TABLE `test` ( 
    2.   
  
  
  2.  `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
    3.   
  
  
  3.  `a` int(11) unsigned DEFAULT NULL, 
    4.   
  
  
  4.  PRIMARY KEY (`id`), 
    5.   
  
  
  5.  UNIQUE KEY `a` (`a`) 
    6.   
  
  
  6. ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;    
    7.

表的結構很簡單，一個主鍵 id，另一個***索引 a。表里的數據如下：

 
 
 
      
  
  
  1. mysql> select * from test; 
    2.   
  
  
  2.  
    3.   
  
  
  3. +----+------+ 
    4.   
  
  
  4.  
    5.   
  
  
  5. | id | a    | 
    6.   
  
  
  6.  
    7.   
  
  
  7. +----+------+ 
    8.   
  
  
  8.  
    9.   
  
  
  9. |  1 |    1 | 
    10.   
  
  
  10.  
    11.   
  
  
  11. |  2 |    2 | 
    12.   
  
  
  12.  
    13.   
  
  
  13. |  4 |    4 | 
    14.   
  
  
  14.  
    15.   
  
  
  15. +----+------+ 
    16.   
  
  
  16.  
    17.   
  
  
  17. 3 rows in set (0.00 sec)    
    18.

出現死鎖的操作如下：

步驟事務 1事務 2
1 begin
2 delete from test where a = 2;
3begin 
4delete from test where a = 2; （事務 1 卡?。?/td> 
5提示出現死鎖：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactioninsert into test (id, a) values (10, 2);

然后我們可以通過 SHOW ENGINE INNODB STATUS; 來查看死鎖日志：

 
 
 
      
  
  
  1. ------------------------ 
    2.   
  
  
  2.  
    3.   
  
  
  3. LATEST DETECTED DEADLOCK 
    4.   
  
  
  4.  
    5.   
  
  
  5. ------------------------ 
    6.   
  
  
  6.  
    7.   
  
  
  7. 170219 13:31:31 
    8.   
  
  
  8.  
    9.   
  
  
  9. *** (1) TRANSACTION: 
    10.   
  
  
  10.  
    11.   
  
  
  11. TRANSACTION 2A8BD, ACTIVE 11 sec starting index read 
    12.   
  
  
  12.  
    13.   
  
  
  13. mysql tables in use 1, locked 1 
    14.   
  
  
  14.  
    15.   
  
  
  15. LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) 
    16.   
  
  
  16.  
    17.   
  
  
  17. MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating 
    18.   
  
  
  18.  
    19.   
  
  
  19. delete from test where a = 2 
    20.   
  
  
  20.  
    21.   
  
  
  21. *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 
    22.   
  
  
  22.  
    23.   
  
  
  23. RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting 
    24.   
  
  
  24.  
    25.   
  
  
  25. Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
    26.   
  
  
  26.  
    27.   
  
  
  27. 0: len 4; hex 00000002; asc     ;; 
    28.   
  
  
  28.  
    29.   
  
  
  29. 1: len 4; hex 00000002; asc     ;; 
    30.   
  
  
  30.  
    31.   
  
  
  31. *** (2) TRANSACTION: 
    32.   
  
  
  32.  
    33.   
  
  
  33. TRANSACTION 2A8BC, ACTIVE 18 sec inserting 
    34.   
  
  
  34.  
    35.   
  
  
  35. mysql tables in use 1, locked 1 
    36.   
  
  
  36.  
    37.   
  
  
  37. 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2 
    38.   
  
  
  38.  
    39.   
  
  
  39. MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update 
    40.   
  
  
  40.  
    41.   
  
  
  41. insert into test (id,a) values (10,2) 
    42.   
  
  
  42.  
    43.   
  
  
  43. *** (2) HOLDS THE LOCK(S): 
    44.   
  
  
  44.  
    45.   
  
  
  45. RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap 
    46.   
  
  
  46.  
    47.   
  
  
  47. Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
    48.   
  
  
  48.  
    49.   
  
  
  49. 0: len 4; hex 00000002; asc     ;; 
    50.   
  
  
  50.  
    51.   
  
  
  51. 1: len 4; hex 00000002; asc     ;; 
    52.   
  
  
  52.  
    53.   
  
  
  53. *** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
    54.   
  
  
  54.  
    55.   
  
  
  55. RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting 
    56.   
  
  
  56.  
    57.   
  
  
  57. Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
    58.   
  
  
  58.  
    59.   
  
  
  59. 0: len 4; hex 00000002; asc     ;; 
    60.   
  
  
  60.  
    61.   
  
  
  61. 1: len 4; hex 00000002; asc     ;; 
    62.   
  
  
  62.  
    63.   
  
  
  63. *** WE ROLL BACK TRANSACTION (1)  
    64.

分析

閱讀死鎖日志

遇到死鎖，***步就是閱讀死鎖日志。死鎖日志通常分為兩部分，上半部分說明了事務 1 在等待什么鎖：

 
 
 
      
  
  
  1. 170219 13:31:31 
    2.   
  
  
  2.  
    3.   
  
  
  3. *** (1) TRANSACTION: 
    4.   
  
  
  4.  
    5.   
  
  
  5. TRANSACTION 2A8BD, ACTIVE 11 sec starting index read 
    6.   
  
  
  6.  
    7.   
  
  
  7. mysql tables in use 1, locked 1 
    8.   
  
  
  8.  
    9.   
  
  
  9. LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) 
    10.   
  
  
  10.  
    11.   
  
  
  11. MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating 
    12.   
  
  
  12.  
    13.   
  
  
  13. delete from test where a = 2 
    14.   
  
  
  14.  
    15.   
  
  
  15. *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 
    16.   
  
  
  16.  
    17.   
  
  
  17. RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting 
    18.   
  
  
  18.  
    19.   
  
  
  19. Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
    20.   
  
  
  20.  
    21.   
  
  
  21. 0: len 4; hex 00000002; asc     ;; 
    22.   
  
  
  22.  
    23.   
  
  
  23. 1: len 4; hex 00000002; asc     ;;    
    24.

從日志里我們可以看到事務 1 當前正在執(zhí)行 delete from test where a = 2，該條語句正在申請索引 a 的 X 鎖，所以提示 lock_mode X waiting。

然后日志的下半部分說明了事務 2 當前持有的鎖以及等待的鎖： 

 
 
 
      
  
  
  1. *** (2) TRANSACTION: 
    2.   
  
  
  2.  
    3.   
  
  
  3. TRANSACTION 2A8BC, ACTIVE 18 sec inserting 
    4.   
  
  
  4.  
    5.   
  
  
  5. mysql tables in use 1, locked 1 
    6.   
  
  
  6.  
    7.   
  
  
  7. 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2 
    8.   
  
  
  8.  
    9.   
  
  
  9. MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update 
    10.   
  
  
  10.  
    11.   
  
  
  11. insert into test (id,a) values (10,2) 
    12.   
  
  
  12.  
    13.   
  
  
  13. *** (2) HOLDS THE LOCK(S): 
    14.   
  
  
  14.  
    15.   
  
  
  15. RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap 
    16.   
  
  
  16.  
    17.   
  
  
  17. Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
    18.   
  
  
  18.  
    19.   
  
  
  19. 0: len 4; hex 00000002; asc     ;; 
    20.   
  
  
  20.  
    21.   
  
  
  21. 1: len 4; hex 00000002; asc     ;; 
    22.   
  
  
  22.  
    23.   
  
  
  23. *** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
    24.   
  
  
  24.  
    25.   
  
  
  25. RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting 
    26.   
  
  
  26.  
    27.   
  
  
  27. Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 
    28.   
  
  
  28.  
    29.   
  
  
  29. 0: len 4; hex 00000002; asc     ;; 
    30.   
  
  
  30.  
    31.   
  
  
  31. 1: len 4; hex 00000002; asc     ;;    
    32.

從日志的 HOLDS THE LOCKS(S) 塊中我們可以看到事務 2 持有索引 a 的 X 鎖，并且是記錄鎖（Record Lock）。該鎖是通過事務 2 在步驟 2 執(zhí)行的 delete 語句申請的。由于是 RR 隔離模式下的基于***索引的等值查詢（Where a = 2），所以會申請一個記錄鎖，而非 next-key 鎖。

從日志的 WAITING FOR THIS LOCK TO BE GRANTED 塊中我們可以看到事務 2 正在申請 S 鎖，也就是共享鎖。該鎖是 insert into test (id,a) values (10,2) 語句申請的。insert 語句在普通情況下是會申請排他鎖，也就是 X 鎖，但是這里出現了 S 鎖。這是因為 a 字段是一個***索引，所以 insert 語句會在插入前進行一次 duplicate key 的檢查，為了使這次檢查成功，需要申請 S 鎖防止其他事務對 a 字段進行修改。

那么為什么該 S 鎖會失敗呢？這是對同一個字段的鎖的申請是需要排隊的。S 鎖前面還有一個未申請成功的 X 鎖，所以 S 鎖必須等待，所以形成了循環(huán)等待，死鎖出現了。

通過閱讀死鎖日志，我們可以清楚地知道兩個事務形成了怎樣的循環(huán)等待，再加以分析，就可以逆向推斷出循環(huán)等待的成因，也就是死鎖形成的原因。

死鎖形成流程圖

為了讓大家更好地理解死鎖形成的原因，我們再通過表格的形式闡述死鎖形成的流程：

步驟
事務 1事務 2
1 begin
2 delete from test where a = 2; 執(zhí)行成功，事務 2 占有 a=2 下的 X 鎖，類型為記錄鎖。
3begin 
4delete from test where a = 2; 事務 1 希望申請 a=2 下的 X 鎖，但是由于事務 2 已經申請了一把 X 鎖，兩把 X 鎖互斥，所以 X 鎖申請進入鎖請求隊列。 
5出現死鎖，事務 1 權重較小，所以被選擇回滾（成為犧牲品）。insert into test (id, a) values (10, 2); 由于 a 字段建立了***索引，所以需要申請 S 鎖以便檢查 duplicate key，由于插入的 a 的值還是 2，所以排在 X 鎖后面。但是前面的 X 鎖的申請只有在事務 2commit 或者 rollback 之后才能成功，此時形成了循環(huán)等待，死鎖產生。

拓展

在排查死鎖的過程中，有個同事還發(fā)現了上述場景會產生另一種死鎖，該場景無法通過手工復現，只有高并發(fā)場景下才有可能復現。

該死鎖對應的日志這里就不貼出了，與上一個死鎖的核心差別是事務 2 等待的鎖從 S 鎖換成了 X 鎖，也就是 lock_mode X locks gap before rec insert intention waiting。我們還是通過表格來詳細說明該死鎖產生的流程：

步驟
事務 1事務 2
1 begin
2 delete from test where a = 2; 執(zhí)行成功，事務 2 占有 a=2 下的 X 鎖，類型為記錄鎖。
3begin 
4 【insert 第 1 階段】insert into test (id, a) values (10, 2); 事務 2 申請 S 鎖進行 duplicate key 進行檢查。檢查成功。
5delete from test where a = 2; 事務 1 希望申請 a=2 下的 X 鎖，但是由于事務 2 已經申請了一把 X 鎖，兩把 X 鎖互斥，所以 X 鎖申請進入鎖請求隊列。 
6出現死鎖，事務 1 權重較小，所以被選擇回滾（成為犧牲品）。【insert 第 2 階段】insert into test (id, a) values (10, 2); 事務 2 開始插入數據，S 鎖升級為 X 鎖，類型為 insert intention。同理，X 鎖進入隊列排隊，形成循環(huán)等待，死鎖產生。


總結

排查死鎖時，首先需要根據死鎖日志分析循環(huán)等待的場景，然后根據當前各個事務執(zhí)行的 SQL 分析出加鎖類型以及順序，逆向推斷出如何形成循環(huán)等待，這樣就能找到死鎖產生的原因了。

PS：上述分析都是基于經驗的推斷，希望其他小伙伴們能夠指出當中的錯誤以及不足指出，謝謝！


當前標題：記一次MySQL死鎖排查過程
文章鏈接：http://m.5511xx.com/article/dhdcdgi.html