快捷導(dǎo)航

MySQL阻塞與死鎖的解決

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

本文主要介紹了MySQL阻塞與死鎖的解決,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

阻塞

因?yàn)椴煌i之間的兼容性關(guān)系，在有些時(shí)刻一個(gè)事務(wù)中的鎖需要等待另一個(gè)事務(wù)中的鎖釋放它所占用的資源，這就是阻塞。

# 查看等待時(shí)間
show variables like 'innodb_lock_wait_timeout';
SET@@innodb_lock_wait_timeout=60;
# 是否在等待超時(shí)時(shí)對(duì)進(jìn)行中的事務(wù)進(jìn)行回滾操作
show variables like 'innodb_rollback_on_timeout';
#設(shè)置等待時(shí)間  默認(rèn)50秒
SET@@innodb_lock_wait_timeout=60;  
#設(shè)置是否在等待超時(shí)時(shí)對(duì)進(jìn)行中的事務(wù)進(jìn)行回滾操作  默認(rèn)是OFF 代表不回滾
SET@@innodb_rollback_on_timeout=on;

查詢：

設(shè)置值：

注意：參數(shù) innodb_lock_wait_timeout 參數(shù)是動(dòng)態(tài)的，在mysql運(yùn)行時(shí)可進(jìn)行調(diào)整， innodb_rollback_on_timeout 參數(shù)是靜態(tài)的，不可在運(yùn)行時(shí)進(jìn)行修改，否則會(huì)報(bào)錯(cuò)。

需要特別注意：在默認(rèn)情況下InnoDB存儲(chǔ)引擎不會(huì)回滾超時(shí)引發(fā)的錯(cuò)誤異常。（其實(shí)InnoDB存儲(chǔ)引擎在大部分情況下都不會(huì)對(duì)異常進(jìn)行回滾。）

異常實(shí)例演示：

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

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

當(dāng)事務(wù)B達(dá)到事務(wù)超時(shí)間時(shí)間時(shí)，報(bào)錯(cuò) ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction .

這時(shí)事務(wù)B在此查詢會(huì)發(fā)現(xiàn)，**ID:7 這條記錄依然存在，這是因?yàn)槭聞?wù)B雖然拋出了異常，但是既沒(méi)有進(jìn)行commit 操作也沒(méi)有進(jìn)行 rollback操作。**這是非常危險(xiǎn)的，因?yàn)榇藭r(shí)數(shù)據(jù)庫(kù)一致性特性被打破了。因此此時(shí)用戶必須判斷是否需要COMMIT還是ROLLBACK，之后再進(jìn)行下一步的操作。

一般情況這時(shí)事務(wù)B需要進(jìn)行 rollback操作，具體情況具體分析。

死鎖

死鎖是指兩個(gè)或兩個(gè)以上的事務(wù)在執(zhí)行過(guò)程中，因爭(zhēng)奪鎖資源而造成的一種互相等待的現(xiàn)象。

當(dāng)發(fā)生死鎖的時(shí)候，若無(wú)外力作用事務(wù)都將無(wú)法進(jìn)行下去。

死鎖產(chǎn)生的條件：

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

如何解決死鎖問(wèn)題：

1.超時(shí)機(jī)制：當(dāng)兩個(gè)事務(wù)互相等待時(shí)，如果等待時(shí)間超過(guò)設(shè)置的某一閾值時(shí)，其中一個(gè)事務(wù)進(jìn)行回滾，另一個(gè)等待的事務(wù)就能繼續(xù)進(jìn)行。在InnoDB存儲(chǔ)引擎中， innodb_lock_wait_timeout 設(shè)置超時(shí)的時(shí)間。

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

2.使用wai t-for graph （等待圖）進(jìn)行死鎖檢測(cè)，數(shù)據(jù)庫(kù)需要保存鎖的信息鏈表和事務(wù)等待鏈表。我們通過(guò)鎖的信息鏈表和事務(wù)等待鏈表就可以構(gòu)造出一張圖，如果圖中存在回路那就說(shuō)明出現(xiàn)了死鎖。

? 事務(wù)和鎖狀態(tài)圖

上圖中，transaction wait list 中有四個(gè)事務(wù)，事務(wù)t2對(duì)row1加了x鎖，事務(wù)t1對(duì)row1加了s鎖，并且事務(wù)t1需要等待事務(wù)t2中的row1資源，因此wait-for graph圖中有一條邊沖節(jié)點(diǎn)t1指向t2。 row2記錄的情況同理。