MySQL阻塞與死鎖的解決

更新時間：2023年09月07日 14:40:40 作者：柒間

本文主要介紹了MySQL阻塞與死鎖的解決,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

阻塞

因為不同鎖之間的兼容性關系，在有些時刻一個事務中的鎖需要等待另一個事務中的鎖釋放它所占用的資源，這就是阻塞。

# 查看等待時間
show variables like 'innodb_lock_wait_timeout';
SET@@innodb_lock_wait_timeout=60;
# 是否在等待超時時對進行中的事務進行回滾操作
show variables like 'innodb_rollback_on_timeout';
#設置等待時間  默認50秒
SET@@innodb_lock_wait_timeout=60;  
#設置是否在等待超時時對進行中的事務進行回滾操作  默認是OFF 代表不回滾
SET@@innodb_rollback_on_timeout=on;

查詢：

設置值：

注意：參數 innodb_lock_wait_timeout 參數是動態(tài)的，在mysql運行時可進行調整， innodb_rollback_on_timeout 參數是靜態(tài)的，不可在運行時進行修改，否則會報錯。

需要特別注意：在默認情況下InnoDB存儲引擎不會回滾超時引發(fā)的錯誤異常。（其實InnoDB存儲引擎在大部分情況下都不會對異常進行回滾。）

異常實例演示：

左邊為會話A，右邊為會話B。初始狀態(tài)數據庫表film中有3條數據，ID，分別為3，5，6；

首先會話A 開啟了事務A，并且在Next-Key Lock算法下鎖定了小與5包含5的記錄。事務B正常插入記錄ID:7，當插入ID:4 時就進人阻塞狀態(tài)了

當事務B達到事務超時間時間時，報錯 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction .

這時事務B在此查詢會發(fā)現，**ID:7 這條記錄依然存在，這是因為事務B雖然拋出了異常，但是既沒有進行commit 操作也沒有進行 rollback操作。**這是非常危險的，因為此時數據庫一致性特性被打破了。因此此時用戶必須判斷是否需要COMMIT還是ROLLBACK，之后再進行下一步的操作。

一般情況這時事務B需要進行 rollback操作，具體情況具體分析。

死鎖

死鎖是指兩個或兩個以上的事務在執(zhí)行過程中，因爭奪鎖資源而造成的一種互相等待的現象。

當發(fā)生死鎖的時候，若無外力作用事務都將無法進行下去。

死鎖產生的條件：

互斥條件
臨界資源是獨占資源，進程應互斥且排他的使用這些資源。
占有和等待條件
進程在請求資源得不到滿足而等待時，不釋放已占有資源。
不剝奪條件
又稱不可搶占，已獲資源只能由進程自愿釋放，不允許被其他進程剝奪。
循環(huán)等待條件
又稱環(huán)路條件，存在循環(huán)等待鏈，其中，每個進程都在等待鏈中等待下一個進程所持有的資源，造成這組進程處于永遠等待狀態(tài)。

如何解決死鎖問題：

1.超時機制：當兩個事務互相等待時，如果等待時間超過設置的某一閾值時，其中一個事務進行回滾，另一個等待的事務就能繼續(xù)進行。在InnoDB存儲引擎中， innodb_lock_wait_timeout 設置超時的時間。

我們知道，一條記錄是有很多undo log的或者undo 版本鏈有很多版本的，如果一個事務操作更新了很多行，這時候如果要進行回滾所占用的時間可能就會很多。

2.使用wai t-for graph （等待圖）進行死鎖檢測，數據庫需要保存鎖的信息鏈表和事務等待鏈表。我們通過鎖的信息鏈表和事務等待鏈表就可以構造出一張圖，如果圖中存在回路那就說明出現了死鎖。