如何解決MySQL中的死鎖問題？
本文轉(zhuǎn)載自微信公眾號「大數(shù)據(jù)DT」，作者肖宇 冰河 。轉(zhuǎn)載本文請聯(lián)系大數(shù)據(jù)DT公眾號。
1 死鎖的產(chǎn)生和預(yù)防
發(fā)生死鎖的必要條件有4個，分別為互斥條件、不可剝奪條件、請求與保持條件和循環(huán)等待條件，如圖1-6所示。
▲圖1-6 死鎖的必要條件
1. 互斥條件
在一段時間內(nèi)，計算機中的某個資源只能被一個進程占用。此時，如果其他進程請求該資源，則只能等待。
2. 不可剝奪條件
某個進程獲得的資源在使用完畢之前，不能被其他進程強行奪走，只能由獲得資源的進程主動釋放。
3. 請求與保持條件
進程已經(jīng)獲得了至少一個資源，又要請求其他資源，但請求的資源已經(jīng)被其他進程占有，此時請求的進程就會被阻塞，并且不會釋放自己已獲得的資源。
4. 循環(huán)等待條件
系統(tǒng)中的進程之間相互等待，同時各自占用的資源又會被下一個進程所請求。例如有進程A、進程B和進程C三個進程，進程A請求的資源被進程B占用，進程B請求的資源被進程C占用，進程C請求的資源被進程A占用，于是形成了循環(huán)等待條件，如圖1-7所示。
▲圖1-7 死鎖的循環(huán)等待條件
需要注意的是，只有4個必要條件都滿足時，才會發(fā)生死鎖。
處理死鎖有4種方法，分別為預(yù)防死鎖、避免死鎖、檢測死鎖和解除死鎖，如圖1-8所示。
▲圖1-8 處理死鎖的方法
- 預(yù)防死鎖：處理死鎖最直接的方法就是破壞造成死鎖的4個必要條件中的一個或多個，以防止死鎖的發(fā)生。
- 避免死鎖：在系統(tǒng)資源的分配過程中，使用某種策略或者方法防止系統(tǒng)進入不安全狀態(tài)，從而避免死鎖的發(fā)生。
- 檢測死鎖：這種方法允許系統(tǒng)在運行過程中發(fā)生死鎖，但是能夠檢測死鎖的發(fā)生，并采取適當?shù)拇胧┣宄梨i。
- 解除死鎖：當檢測出死鎖后，采用適當?shù)牟呗院头椒▽⑦M程從死鎖狀態(tài)解脫出來。
在實際工作中，通常采用有序資源分配法和銀行家算法這兩種方式來避免死鎖，大家可自行了解。
2 MySQL中的死鎖問題
在MySQL 5.5.5及以上版本中，MySQL的默認存儲引擎是InnoDB。該存儲引擎使用的是行級鎖，在某種情況下會產(chǎn)生死鎖問題，所以InnoDB存儲引擎采用了一種叫作等待圖(wait-for graph)的方法來自動檢測死鎖，如果發(fā)現(xiàn)死鎖，就會自動回滾一個事務(wù)。
接下來，我們看一個MySQL中的死鎖案例。
第一步：打開終端A，登錄MySQL，將事務(wù)隔離級別設(shè)置為可重復(fù)讀，開啟事務(wù)后為account數(shù)據(jù)表中id為1的數(shù)據(jù)添加排他鎖，如下所示。
- mysql> set session transaction isolation level repeatable read;
- Query OK, 0 rows affected (0.00 sec)
- mysql> start transaction;
- Query OK, 0 rows affected (0.00 sec)
- mysql> select * from account where id =1 for update;
- +----+--------+---------+
- | id | name | balance |
- +----+--------+---------+
- | 1 | 張三 | 300 |
- +----+--------+---------+
- 1 row in set (0.00 sec)
第二步：打開終端B，登錄MySQL，將事務(wù)隔離級別設(shè)置為可重復(fù)讀，開啟事務(wù)后為account數(shù)據(jù)表中id為2的數(shù)據(jù)添加排他鎖，如下所示。
- mysql> set session transaction isolation level repeatable read;
- Query OK, 0 rows affected (0.00 sec)
- mysql> start transaction;
- Query OK, 0 rows affected (0.00 sec)
- mysql> select * from account where id =2 for update;
- +----+--------+---------+
- | id | name | balance |
- +----+--------+---------+
- | 2 | 李四 | 350 |
- +----+--------+---------+
- 1 row in set (0.00 sec)
第三步：在終端A為account數(shù)據(jù)表中id為2的數(shù)據(jù)添加排他鎖，如下所示。
- mysql> select * from account where id =2 for update;
此時，線程會一直卡住，因為在等待終端B中id為2的數(shù)據(jù)釋放排他鎖。
第四步：在終端B中為account數(shù)據(jù)表中id為1的數(shù)據(jù)添加排他鎖，如下所示。
- mysql> select * from account where id =1 for update;
- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
此時發(fā)生了死鎖。通過如下命令可以查看死鎖的日志信息。
- show engine innodb status\G
通過命令行查看LATEST DETECTED DEADLOCK選項相關(guān)的信息，可以發(fā)現(xiàn)死鎖的相關(guān)信息，或者通過配置innodb_print_all_deadlocks(MySQL 5.6.2版本開始提供)參數(shù)為ON，將死鎖相關(guān)信息打印到MySQL錯誤日志中。
在MySQL中，通常通過以下幾種方式來避免死鎖。
- 盡量讓數(shù)據(jù)表中的數(shù)據(jù)檢索都通過索引來完成，避免無效索引導(dǎo)致行鎖升級為表鎖。
- 合理設(shè)計索引，盡量縮小鎖的范圍。
- 盡量減少查詢條件的范圍，盡量避免間隙鎖或縮小間隙鎖的范圍。
- 盡量控制事務(wù)的大小，減少一次事務(wù)鎖定的資源數(shù)量，縮短鎖定資源的時間。
- 如果一條SQL語句涉及事務(wù)加鎖操作，則盡量將其放在整個事務(wù)的最后執(zhí)行。
- 盡可能使用低級別的事務(wù)隔離機制。
關(guān)于作者：肖宇，分布式事務(wù)架構(gòu)專家，Apache ShenYu(incubating)網(wǎng)關(guān)創(chuàng)始人，Dromara開源組織創(chuàng)始人，Hmily、RainCat、Myth等分布式事務(wù)框架的作者。Apache ShardingSphere Committer。
冰河，互聯(lián)網(wǎng)高級技術(shù)專家、MySQL技術(shù)專家、分布式事務(wù)架構(gòu)專家。多年來，一直致力于分布式系統(tǒng)架構(gòu)、微服務(wù)、分布式數(shù)據(jù)庫、分布式事務(wù)與大數(shù)據(jù)技術(shù)的研究，在高并發(fā)、高可用、高可擴展性、高可維護性和大數(shù)據(jù)等領(lǐng)域擁有豐富的架構(gòu)經(jīng)驗。
本文摘編自《深入理解分布式事務(wù)：原理與實戰(zhàn)》，經(jīng)出版方授權(quán)發(fā)布。
