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

鎖是需要事務結束后才釋放的。

專注于為中小企業提供成都網站設計、網站制作服務,電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業大武口免費做網站提供優質的服務。我們立足成都，凝聚了一批互聯網行業人才，有力地推動了1000多家企業的穩健成長，幫助中小企業通過網站建設實現規模擴充和轉變。

一個是 MVCC，一個是兩階段鎖協議。

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

官方定義：

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

對應到 MySQL 上分為兩個階段：

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

MySQL有兩種死鎖處理方式：

死鎖檢測 （默認開啟）

死鎖檢測的原理是構建一個以事務為頂點、鎖為邊的有向圖，判斷有向圖是否存在環，存在即有死鎖。

回滾

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

收集死鎖信息：

減少死鎖：

死鎖解決：

mysql解決死鎖問題

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

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

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

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

官方定義：

對應到 MySQL 上分為兩個階段：

就是說呢，只有遵循兩段鎖協議，才能實現 可串行化調度 。

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

MySQL有兩種死鎖處理方式：

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

死鎖檢測的原理是構建一個以事務為頂點、鎖為邊的有向圖，判斷有向圖是否存在環，存在即有死鎖。

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

MySQL如何處理死鎖

解決一次mysql死鎖問題

多線程開啟事務處理。每個事務有多個update操作和一個insert操作（都在同一張表）。

默認隔離級別：Repeatable Read

只有hotel_id=2和hotel_id=11111的數據

邏輯刪除原有數據

插入新的數據

根據現有數據情況，update的時候沒有數據被更新

報了非常多一樣的錯

發現居然有死鎖。

根據常識考慮，我每個線程（事務）更新的數據都不沖突，為什么會產生死鎖？

帶著這個問題，打印mysql最近一次的死鎖信息

show engine innodb status

顯示如下

發現事務1在等待一個鎖

事務2也在等待一個鎖

而且事物2持有了事物1需要的鎖

關于鎖的描述，出現了 lock_mode ， gap before rec ， insert intention 等字眼，看不懂說明了什么？說明我關于mysql的鎖相關的知識儲備還不夠。那就開始調查mysql的鎖相關知識。

通過搜索引擎，

鎖的持有兼容程度如下表

那么再回到死鎖日志，可以知道 :

事務1正在獲取插入意向鎖

事務2正在獲取插入意向鎖，持有排他gap鎖

再看我們上面的鎖兼容表格，可以知道， gap lock和insert intention lock是不兼容的

那么就可以推斷出： 事務1持有gap lock，等待事務2的insert intention lock釋放；事務2持有gap lock，等待事務1的insert intention lock釋放，從而導致死鎖。

那么新的問題就來了，事務1的intention lock 為什么會和事務2的gap lock 有交集，或者說，事務1要插入的數據的位置為什么會被事務2給鎖住？

讓我回顧一下gap lock的定義：

間隙鎖，鎖定一個范圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況

那為什么是gap lock，gap lock到底是基于什么邏輯鎖的記錄？發現自己相關的知識儲備還不夠。那就開始調查。

調查后發現，當當前索引是一個 普通索引 的時候，會加一個gap lock來防止幻讀， 此gap lock 會鎖住一個左開右閉的區間。 假設索引為xx_idx（xx_id），數據分布為1,4,6,8,12，當更新xx_id=9的時候，這個時候gap lock的鎖定記錄區間就是(8，12]，也就是鎖住了xxid in (9,10,11,12)的數據，當有其他事務要插入xxid in (9,10,11,12)的數據時，就會處于等待獲取鎖的狀態。

ps：當前索引不是普通索引，而且是唯一索引等其他情況，請參考下面資料

MySQL 加鎖處理分析

回到我自己的案例中，重新屢一下事務1的執行過程：

因為普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的關系 這段sql會獲取一個gap lock，范圍(2,11111]

這段sql會獲取一個insert intention lock （waiting）

再看事務2的執行過程

因為普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的關系 這段sql也會獲取一個gap lock，范圍也是(2,11111]（根據前面的知識，gap lock之間會互相兼容，可以一起持有鎖的）

這段sql也會獲取一個insert intention lock （waiting）

看到這里，基本也就破案了。因為普通索引的關系，事務1和事務2的gap lock的覆蓋范圍太廣，導致其他事務無法插入數據。

重新梳理一下：

所以從結果來看，一堆事務被回滾，只有10007數據被更新成功

gap lock 導致了并發處理的死鎖

在mysql默認的事務隔離級別（repeatable read）下，無法避免這種情況。只能把并發處理改成同步處理。或者從業務層面做處理。

共享鎖、排他鎖、意向共享、意向排他

record lock、gap lock、next key lock、insert intention lock

show engine innodb status

網頁標題：mysql表死鎖怎么辦 mysql 表鎖死
網站URL：http://vcdvsql.cn/article24/ddsijce.html

成都網站建設公司_創新互聯，為您提供品牌網站建設、外貿網站建設、靜態網站、網頁設計公司、ChatGPT、Google

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯