實(shí)戰(zhàn)分享 | 你知道這個(gè)死鎖是怎么產(chǎn)生的嗎？
Part1 背景
鎖作為 MySQL 知識(shí)體系的主要部分之一，是每個(gè) DBA 都需要學(xué)習(xí)和掌握的知識(shí)。鎖保證了數(shù)據(jù)庫(kù)在并發(fā)的場(chǎng)景下數(shù)據(jù)的一致性，同時(shí)鎖沖突也是影響數(shù)據(jù)庫(kù)性能的因素之一。而鎖沖突中，有一類很經(jīng)典的場(chǎng)景經(jīng)常會(huì)拿出來討論：死鎖。最近剛好也遇到了一個(gè)典型的死鎖案例，本文會(huì)基于這個(gè)案例，做一次詳細(xì)的分析與拆解。
Part2 問題
由于innodb engine status會(huì)記錄最近一次死鎖的細(xì)節(jié)信息，因此案例現(xiàn)場(chǎng)的信息是可以完整拿到的。用戶針對(duì)這個(gè)死鎖的問題，提出了疑問：數(shù)據(jù)更新的并不是同一行，使用的也是不同的索引，為什么會(huì)發(fā)生死鎖？（以下細(xì)節(jié)信息均已脫敏）
死鎖的兩個(gè)語(yǔ)句如下：
- 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)
精簡(jiǎn)之后的 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;;
- ...省略無(wú)關(guān)的兩行...
- 3: len 4; hex 005f5434; asc _T4;;
- 4: len 4; hex 00012272; asc "r;;
- ...省略很多行...
- *** WE ROLL BACK TRANSACTION (1)
- ...省略...
Part3 原因分析
首先簡(jiǎn)單了解一下死鎖的幾個(gè)要素：
1. 互斥條件：一個(gè)資源每次只能被一個(gè)進(jìn)程占用。
- MySQL 的鎖機(jī)制天然具備這個(gè)條件。
2. 請(qǐng)求與保持條件：資源請(qǐng)求被阻塞時(shí)，已持有的資源不會(huì)被釋放。
- MySQL 不觸發(fā)死鎖回滾，且未進(jìn)入 lockwait_timeout 的時(shí)候，具備這個(gè)條件。
3. 不剝奪條件：已獲得的資源，在末使用完之前，不能強(qiáng)行剝奪。
- MySQL 的鎖機(jī)制天然具備這個(gè)條件。
4. 循環(huán)等待條件：若干進(jìn)程之間形成一種頭尾相接的循環(huán)等待資源關(guān)系，通常會(huì)表現(xiàn)為有向環(huán)。
由于 MySQL 的鎖機(jī)制的原因，只需要判斷出兩個(gè) SQL 語(yǔ)句的鎖存在循環(huán)等待，那么死鎖的條件就會(huì)成立了。
接下來對(duì) MySQL 記錄的死鎖信息進(jìn)行詳細(xì)的分析，首先觀察死鎖的事務(wù)詳情這一部分信息：
- LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)。......
- 20 lock struct(s), heap size 2936, 40 row lock(s)
可以很明顯可以發(fā)現(xiàn)，這兩個(gè)語(yǔ)句涉及到的數(shù)據(jù)行還是比較多的，用戶的疑問：數(shù)據(jù)更新的并不是同一行，其實(shí)是個(gè)誤解。那么理論上，“循環(huán)等待：互相持有對(duì)方需要的鎖”，這種典型的死鎖場(chǎng)景是可能會(huì)存在的。
接下來，重點(diǎn)放在更細(xì)節(jié)的信息上：
- *** (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;;
- ...省略無(wú)關(guān)的兩行...
- 3: len 4; hex 005f5434; asc _T4;;
- 4: len 4; hex 00012272; asc "r;;
- ...省略很多行...
用戶提出的疑問：使用的也是不同的索引，為什么會(huì)發(fā)送死鎖？實(shí)際上二級(jí)索引上的記錄鎖，最終也會(huì)加到主鍵上。
這個(gè)很好理解，如果二級(jí)索引上，通過搜索商品表的商品名稱索引（二級(jí)索引）搜索“iphone12”，并給這一行數(shù)據(jù)加上了鎖，鎖住了“iphone12”這個(gè)商品的詳情數(shù)據(jù)行，如果別的事務(wù)可以通過搜索主鍵來修改這一行數(shù)據(jù)，明顯是不行的。
因此本案例中，雖然死鎖信息中記錄的索引名稱不一樣，但是鎖爭(zhēng)用的條件是成立的，即：trx1 通過二級(jí)索引向主鍵上執(zhí)行了加鎖操作，而 trx2 在其他的二級(jí)索引上拿到了鎖，但是主鍵鎖拿不到，因此進(jìn)入了等待狀態(tài)。所以只需要定位到具體鎖的數(shù)據(jù)，找到循環(huán)等待的邏輯關(guān)系，就可以完成整個(gè)案例分析了。
參考上文引用的信息，具體發(fā)生死鎖的行的信息都記錄在類似0: len 4; hex 00722663; asc r&c;;的信息中。
trx1 記錄的鎖等待信息是二級(jí)索引 id2，因?yàn)?id2 是一個(gè)單行索引，因此只會(huì)有 0 和 1 兩行信息，0 代表的就是具體的行 id2，1 即為主鍵。通過 16 進(jìn)制轉(zhuǎn)換工具，轉(zhuǎn)成 10 進(jìn)制，可以發(fā)現(xiàn)對(duì)應(yīng)的數(shù)據(jù)如下：
pk = 7479109 and id2 = 74354
那么再看看 trx2 記錄的信息，鎖等待方面，記錄的信息是主鍵，所以這個(gè)地方會(huì)有完整的表數(shù)據(jù)，過濾掉無(wú)效的數(shù)據(jù)之后，留下了三行：0 為主鍵，3 為 id1，4 為 id2。轉(zhuǎn)換進(jìn)制之后，對(duì)應(yīng)的數(shù)據(jù)如下：
pk = 7480931 and id1 = 6247476 and id2 = 74354
可以看到，trx2 等待的鎖，id1 和 id2 剛好滿足 trx1 的查詢條件。而 trx2 持有的鎖信息中，第一個(gè)剛好就是 trx1 等待的：
trx2 持有的鎖
那么關(guān)于這個(gè)死鎖案例的具體場(chǎng)景，就可以用下有向環(huán)的圖例進(jìn)行說明：
死鎖圖例
至此為止，這個(gè)死鎖的案例分析就完成了，從最初的死鎖成立條件分析，到解讀具體的鎖內(nèi)容，最終完成了死鎖的有向環(huán)圖例。
實(shí)際上，自己觀察一下這個(gè)死鎖的有向環(huán)圖例，會(huì)發(fā)現(xiàn)這兩個(gè)語(yǔ)句用到了兩個(gè)單列索引，那么進(jìn)一步思考的話，如果這兩個(gè)列建成了聯(lián)合索引，這個(gè)死鎖的案例是不是就可能不會(huì)發(fā)生了？
Part4 總結(jié)
對(duì)于死鎖的問題，只需要根據(jù)四個(gè)條件，一步一步過濾與分析，通過解讀死鎖現(xiàn)場(chǎng)的詳細(xì)內(nèi)容，就可以準(zhǔn)確的還原整個(gè)死鎖的發(fā)生原因以及涉及到的數(shù)據(jù)行。當(dāng)然，在實(shí)際的業(yè)務(wù)環(huán)境中，可能還會(huì)有更復(fù)雜和隱蔽的死鎖案例，但是不論多么隱蔽和復(fù)雜，死鎖分析的思路和步驟都是相似的。
關(guān)于專欄
《騰訊云數(shù)據(jù)庫(kù)專家服務(wù)》是由騰訊云數(shù)據(jù)庫(kù)技術(shù)服務(wù)團(tuán)隊(duì)維護(hù)的社區(qū)專欄，涵蓋了各類數(shù)據(jù)庫(kù)的實(shí)際案例，最佳實(shí)踐，版本特性等內(nèi)容。
