作者 王文安，騰訊CSIG資料庫專項的資料庫工程師，主要負責騰訊云資料庫 MySQL 的相關的作業，熱愛技術，歡迎留言進行交流，
Part1 背景
鎖作為 MySQL 知識體系的主要部分之一，是每個 DBA 都需要學習和掌握的知識，鎖保證了資料庫在并發的場景下資料的一致性，同時鎖沖突也是影響資料庫性能的因素之一，而鎖沖突中，有一類很經典的場景經常會拿出來討論：死鎖，最近剛好也遇到了一個典型的死鎖案例，本文會基于這個案例，做一次詳細的分析與拆解，
Part2 問題
由于innodb engine status會記錄最近一次死鎖的細節資訊，因此案例現場的資訊是可以完整拿到的，用戶針對這個死鎖的問題，提出了疑問：資料更新的并不是同一行，使用的也是不同的索引，為什么會發生死鎖？（以下細節資訊均已脫敏）
死鎖的兩個陳述句如下：
UPDATE tbl_deadlock SET col1 = 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6247476) AND (id2 = 74354)
UPDATE tbl_deadlock SET col1 = 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6249219) AND (id2 = 74354)
精簡之后的 MySQL 死鎖資訊如下：
=====================================
2020-10-26 12:14:30 7fd2642f5700 INNODB MONITOR OUTPUT
=====================================
...省略...
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-10-26 12:12:03 7fd2846ed700
*** (1) TRANSACTION:
TRANSACTION 1795660514, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 21829887, OS thread handle 0x7fd28d14a700, query id 178279444 172.21.0.15 username updating
UPDATE tbl_deadlock SET col1= 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6247476) AND (id2 = 74354)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 286947 n bits 1048 index `id2` of table `deadlock`.`tbl_deadlock` trx id 1795660514 lock_mode X waiting
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00012272; asc "r;;
1: len 4; hex 00721f45; asc r E;;
*** (2) TRANSACTION:
TRANSACTION 1795660513, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
20 lock struct(s), heap size 2936, 40 row lock(s)
MySQL thread id 21905203, OS thread handle 0x7fd2846ed700, query id 178279443 172.21.0.15 username updating
UPDATE tbl_deadlock SET col1 = 1, col2 = 1, update_time = 1603685523 WHERE (id1 = 6249219) AND (id2 = 74354)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 8575 page no 286947 n bits 1048 index `id2` of table `deadlock`.`tbl_deadlock` trx id 1795660513 lock_mode X
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00012272; asc "r;;
1: len 4; hex 00721f45; asc r E;;
Record lock, heap no 430 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00012272; asc "r;;
1: len 4; hex 00721fe3; asc r ;;
Record lock, heap no 431 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00012272; asc "r;;
1: len 4; hex 0072218f; asc r! ;;
...省略很多 Record lock...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 344554 n bits 120 index `PRIMARY` of table `deadlock`.`tbl_deadlock` trx id 1795660513 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 44; compact format; info bits 0
0: len 4; hex 00722663; asc r&c;;
...省略無關的兩行...
3: len 4; hex 005f5434; asc _T4;;
4: len 4; hex 00012272; asc "r;;
...省略很多行...
*** WE ROLL BACK TRANSACTION (1)
...省略...
Part3 原因分析
首先簡單了解一下死鎖的幾個要素：
-
互斥條件：一個資源每次只能被一個行程占用，
MySQL 的鎖機制天然具備這個條件， -
請求與保持條件：資源請求被阻塞時，已持有的資源不會被釋放，
MySQL 不觸發死鎖回滾，且未進入 lockwait_timeout 的時候，具備這個條件， -
不剝奪條件：已獲得的資源，在末使用完之前，不能強行剝奪，
MySQL 的鎖機制天然具備這個條件， -
回圈等待條件：若干行程之間形成一種頭尾相接的回圈等待資源關系，通常會表現為有向環，
由于 MySQL 的鎖機制的原因，只需要判斷出兩個 SQL 陳述句的鎖存在回圈等待，那么死鎖的條件就會成立了，
接下來對 MySQL 記錄的死鎖資訊進行詳細的分析，首先觀察死鎖的事務詳情這一部分資訊：
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)，
......
20 lock struct(s), heap size 2936, 40 row lock(s)
可以很明顯可以發現，這兩個陳述句涉及到的資料行還是比較多的，用戶的疑問：資料更新的并不是同一行，其實是個誤解，那么理論上，“回圈等待：互相持有對方需要的鎖”，這種典型的死鎖場景是可能會存在的，
接下來，重點放在更細節的資訊上：
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 286947 n bits 1048 index `id2` of table `deadlock`.`tbl_deadlock` trx id 1795660514 lock_mode X waiting
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00012272; asc "r;;
1: len 4; hex 00721f45; asc r E;;
......
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 344554 n bits 120 index `PRIMARY` of table `deadlock`.`tbl_deadlock` trx id 1795660513 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 44; compact format; info bits 0
0: len 4; hex 00722663; asc r&c;;
...省略無關的兩行...
3: len 4; hex 005f5434; asc _T4;;
4: len 4; hex 00012272; asc "r;;
...省略很多行...
用戶提出的疑問：使用的也是不同的索引，為什么會發送死鎖？實際上二級索引上的記錄鎖，最終也會加到主鍵上，
這個很好理解，如果二級索引上，通過搜索商品表的商品名稱索引（二級索引）搜索“iphone12”，并給這一行資料加上了鎖，鎖住了“iphone12”這個商品的詳情資料行，如果別的事務可以通過搜索主鍵來修改這一行資料，明顯是不行的，
因此本案例中，雖然死鎖資訊中記錄的索引名稱不一樣，但是鎖爭用的條件是成立的，即：trx1 通過二級索引向主鍵上執行了加鎖操作，而 trx2 在其他的二級索引上拿到了鎖，但是主鍵鎖拿不到，因此進入了等待狀態，所以只需要定位到具體鎖的資料，找到回圈等待的邏輯關系，就可以完成整個案例分析了，
參考上文參考的資訊，具體發生死鎖的行的資訊都記錄在類似0: len 4; hex 00722663; asc r&c;;的資訊中，
trx1 記錄的鎖等待資訊是二級索引 id2，因為 id2 是一個單行索引，因此只會有 0 和 1 兩行資訊，0 代表的就是具體的行 id2，1 即為主鍵，通過 16 進制轉換工具，轉成 10 進制，可以發現對應的資料如下：
pk = 7479109 and id2 = 74354
那么再看看 trx2 記錄的資訊，鎖等待方面，記錄的資訊是主鍵，所以這個地方會有完整的表資料，過濾掉無效的資料之后，留下了三行：0 為主鍵，3 為 id1，4 為 id2，轉換進制之后，對應的資料如下：
pk = 7480931 and id1 = 6247476 and id2 = 74354
可以看到，trx2 等待的鎖，id1 和 id2 剛好滿足 trx1 的查詢條件，而 trx2 持有的鎖資訊中，第一個剛好就是 trx1 等待的：
那么關于這個死鎖案例的具體場景，就可以用下有向環的圖例進行說明：
至此為止，這個死鎖的案例分析就完成了，從最初的死鎖成立條件分析，到解讀具體的鎖內容，最終完成了死鎖的有向環圖例，
實際上，自己觀察一下這個死鎖的有向環圖例，會發現這兩個陳述句用到了兩個單列索引，那么進一步思考的話，如果這兩個列建成了聯合索引，這個死鎖的案例是不是就可能不會發生了？
Part4 總結
對于死鎖的問題，只需要根據四個條件，一步一步過濾與分析，通過解讀死鎖現場的詳細內容，就可以準確的還原整個死鎖的發生原因以及涉及到的資料行，當然，在實際的業務環境中，可能還會有更復雜和隱蔽的死鎖案例，但是不論多么隱蔽和復雜，死鎖分析的思路和步驟都是相似的，
本文由博客一文多發平臺 OpenWrite 發布！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/213796.html
標籤：其他