mysql解決死鎖問題

官方定義如下：兩個事務(wù)都持有對方需要的鎖，并且在等待對方釋放，并且雙方都不會釋放自己的鎖。

專業(yè)領(lǐng)域包括成都網(wǎng)站設(shè)計、成都網(wǎng)站制作、外貿(mào)網(wǎng)站建設(shè)、電子商務(wù)商城網(wǎng)站建設(shè)、微信營銷、系統(tǒng)平臺開發(fā)， 與其他網(wǎng)站設(shè)計及系統(tǒng)開發(fā)公司不同，創(chuàng)新互聯(lián)的整合解決方案結(jié)合了幫做網(wǎng)絡(luò)品牌建設(shè)經(jīng)驗和互聯(lián)網(wǎng)整合營銷的理念，并將策略和執(zhí)行緊密結(jié)合，為客戶提供全網(wǎng)互聯(lián)網(wǎng)整合方案。

這個就好比你有一個人質(zhì)，對方有一個人質(zhì)，你們倆去談判說換人。你讓對面放人，對面讓你放人。

看到這里，也許你會有這樣的疑問，事務(wù)和談判不一樣，為什么事務(wù)不能使用完鎖之后立馬釋放呢？居然還要操作完了之后一直持有鎖？這就涉及到 MySQL 的并發(fā)控制了。

MySQL的并發(fā)控制有兩種方式，一個是 MVCC，一個是兩階段鎖協(xié)議。那么為什么要并發(fā)控制呢？是因為多個用戶同時操作 MySQL 的時候，為了提高并發(fā)性能并且要求如同多個用戶的請求過來之后如同串行執(zhí)行的一樣（ 可串行化調(diào)度 ）。具體的并發(fā)控制這里不再展開。咱們繼續(xù)深入討論兩階段鎖協(xié)議。

官方定義：

對應(yīng)到 MySQL 上分為兩個階段：

就是說呢，只有遵循兩段鎖協(xié)議，才能實現(xiàn) 可串行化調(diào)度 。

但是兩階段鎖協(xié)議不要求事務(wù)必須一次將所有需要使用的數(shù)據(jù)加鎖，并且在加鎖階段沒有順序要求，所以這種并發(fā)控制方式會形成死鎖。

MySQL有兩種死鎖處理方式：

由于性能原因，一般都是使用死鎖檢測來進行處理死鎖。

死鎖檢測的原理是構(gòu)建一個以事務(wù)為頂點、鎖為邊的有向圖，判斷有向圖是否存在環(huán)，存在即有死鎖。

檢測到死鎖之后，選擇插入更新或者刪除的行數(shù)最少的事務(wù)回滾，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段來判斷。

MySQL如何處理死鎖

詳解MySQL（InnoDB）如何處理死鎖

鎖是需要事務(wù)結(jié)束后才釋放的。

一個是 MVCC，一個是兩階段鎖協(xié)議。

為什么要并發(fā)控制呢？是因為多個用戶同時操作 MySQL 的時候，為了提高并發(fā)性能并且要求如同多個用戶的請求過來之后如同串行執(zhí)行的一樣（為了解決臟讀、不可重復(fù)讀、幻讀）

官方定義：

兩階段鎖協(xié)議是指所有事務(wù)必須分兩個階段對數(shù)據(jù)加鎖和解鎖，在對任何數(shù)據(jù)進行讀、寫操作之前，事務(wù)首先要獲得對該數(shù)據(jù)的封鎖；在釋放一個封鎖之后，事務(wù)不再申請和獲得任何其他封鎖。

對應(yīng)到 MySQL 上分為兩個階段：

但是兩階段鎖協(xié)議不要求事務(wù)必須一次將所有需要使用的數(shù)據(jù)加鎖（innodb在需要的索引列數(shù)據(jù)才鎖行），并且在加鎖階段沒有順序要求，所以這種并發(fā)控制方式會形成死鎖。

MySQL有兩種死鎖處理方式：

死鎖檢測 （默認(rèn)開啟）

死鎖檢測的原理是構(gòu)建一個以事務(wù)為頂點、鎖為邊的有向圖，判斷有向圖是否存在環(huán)，存在即有死鎖。

回滾

檢測到死鎖之后，選擇插入更新或者刪除的行數(shù)最少的事務(wù)回滾，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段來判斷。

收集死鎖信息：

減少死鎖：

死鎖解決：

巧用MySQL InnoDB引擎鎖機制解決死鎖問題[2]

索引 KEY_TSKTASK_MONTIME (STATUS_ID MON_TIME)

分析 涉及的兩條語句應(yīng)該不會涉及相同的TSK_TASK記錄 那為什么會造成死鎖呢？

查詢MySQL官網(wǎng)文檔 發(fā)現(xiàn)這跟MySQL的索引機制有關(guān) MySQL的InnoDB引擎是行級鎖 我原來的理解是直接對記錄進行鎖定 實際上并不是這樣的

要點如下:

不是對記錄進行鎖定 而是對索引進行鎖定

在UPDATE DELETE操作時 MySQL不僅鎖定WHERE條件掃描過的所有索引記錄 而且會鎖定相鄰的鍵值 即所謂的next key locking

如語句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID 會鎖定所有主鍵大于等于 的所有記錄 在該語句完成之前 你就不能對主鍵等于 的記錄進行操作

當(dāng)非簇索引(non cluster index)記錄被鎖定時 相關(guān)的簇索引(cluster index)記錄也需要被鎖定才能完成相應(yīng)的操作

再分析一下發(fā)生問題的兩條SQL語句 就不難找到問題所在了

當(dāng) update TSK_TASK set STATUS_ID= UPDATE_TIME=now () where STATUS_ID= and MON_TIME

假設(shè) update TSK_TASK set STATUS_ID= UPDATE_TIME=now () where ID in ( ) 幾乎同時執(zhí)行時 本語句首先鎖定簇索引(主鍵) 由于需要更新STATUS_ID的值 所以還需要鎖定KEY_TSKTASK_MONTIME 的某些索引記錄

這樣第一條語句鎖定了KEY_TSKTASK_MONTIME 的記錄 等待主鍵索引 而第二條語句則鎖定了主鍵索引記錄 而等待KEY_TSKTASK_MONTIME 的記錄 在此情況下 死鎖就產(chǎn)生了

筆者通過拆分第一條語句解決死鎖問題

先查出符合條件的ID select ID from TSK_TASK where STATUS_ID= and MON_TIME date_sub(now() INTERVAL minute) 然后再更新狀態(tài) update TSK_TASK set STATUS_ID= where ID in (… )

至此 死鎖問題徹底解決

lishixinzhi/Article/program/MySQL/201311/29601