生產上資料庫死鎖,是該程式設計師祭天了

語言: CN / TW / HK

theme: channing-cyan

趣味搞笑新聞熱點盤點西瓜.jpg

Hello,大家好,我是 Skow

閱讀這篇文章之前,大家可以問問自己 - 何為死鎖? - Mysql具有哪些鎖? - Mysql 的鎖模式相容矩陣你是否清楚? - 如何排查死鎖問題?

如果你可以閉著眼睛回答出來這些問題的,那麼就默默點贊離開👍🏻

如果你對上面的知識點,還有點含糊不清,那麼這篇文章將會帶你從一個真實業務場景入手,分析死鎖問題,希望本文對你有所幫助,Let's go 🤨

業務背景

目前我司有兩個系統 A 系統、B系統

A 系統存放著公司所有人員的資訊

B 系統需要日終定時從 A 系統同步資料

人員已在 B 系統中存在,則更新,不存在則插入

因人員資訊過多,所以採取多執行緒方式同步人員資料

在驗證程式碼的時候,😡測試人員怒氣衝衝的反應, sync_user 也就是我們的同步人員的那張資料表打不開了

遇事莫慌,先甩鍋運維,“小姐姐,莫急莫慌,肯定是資料庫系統出問題了"

經過運維和DBA的排查,其實罪魁禍首是開發

我們的程式碼導致了這張表出現了死鎖,從而導致表打不開了

那,到底是為何發生了死鎖?接下來我們還原一下案發現場

案發還原

看一下原始的建表語句(~~當然不會給你看真實的表~~)

SQL CREATE TABLE `sync_user` ( `user_id` VARCHAR ( 32 ) NOT NULL COMMENT '使用者 ID', `user_name` VARCHAR ( 32 ) DEFAULT NULL COMMENT '使用者姓名', `login_account` VARCHAR ( 50 ) DEFAULT NULL COMMENT '登陸賬號', PRIMARY KEY ( `user_id` ), KEY `idx_login_account` ( `login_account` ) USING BTREE ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '使用者資訊表'; 現在系統中有張三、李四兩個使用者

表已經準備就緒了,接下來看下我們的資料隔離級別、並且把我們的自動提交關閉

正戲開始!!! ✍️

按照下圖模擬下我們併發同步資料的情況

  • 開啟 事務1,執行更新語句 > UPDATE sync_user SET user_name = "張三2" where login_account = "zhangsan";

  • 開啟 事務2,執行更新語句 > UPDATE sync_user SET user_name = "李四2" where login_account = "lisi"; 更新成功

  • 回到事務1,執行插入語句 > INSERT INTO sync_user (user_id, user_name, login_account) VALUES ('3', '王五', 'wangwu'); -- 此條語句阻塞中

  • 回到事務2,執行插入語句 > INSERT INTO sync_user (user_id, user_name, login_account) VALUES ('4', '楊六', 'yangliu'); -- 出現死鎖,並且事務1 的插入語句執行成功

以上就是我們模擬的併發情況,課代表總結圖如下 👇

死鎖分析

通過事務2 提示的 Deadlock found when trying to get lock; try restarting transaction

我們可以很明白的得到,這就是發生了死鎖情況

那麼,什麼是死鎖呢?

大學老師都是這樣告訴我們的:死鎖是指多個程序在執行過程中因爭奪資源而造成的一種僵局,當程序處於這種僵持狀態時,若無外力作用,它們都將無法再向前推進 造成死鎖的四個必要條件 - 互斥條件:一個資源每次只能被一個程序使用; - 請求與保持條件:一個程序因請求資源而阻塞時,對已獲得的資源保持不放; - 不剝奪條件:程序已獲得的資源,在末使用完之前,不能強行剝奪; - 迴圈等待條件:若干程序之間形成一種頭尾相接的迴圈等待資源關係;

ok,什麼是死鎖和造成死鎖的必要條件我們已經知道了,要產生死鎖,必先有鎖,那麼 Mysql 有哪些鎖呢

Mysql按照鎖模式區分有:記錄鎖、gap鎖、next-key鎖、插入意向鎖

具體的鎖作用篇幅限制,就不展開說明

鎖的相容矩陣為:橫行為當前已經持有的鎖,縱向為正在請求的鎖

接下來,正式分析一下事務1、事務2各自拿到了什麼鎖

  • 事務1在更新zhangsan 張三的時候
  • 間隙鎖: UPDATE 語句會在非唯一索引的 login_account 加上間隙鎖,即獲得 (lisi,zhangsan)(zhangsan,+∞)
  • 記錄鎖: 因為 login_account 為索引,會在 zhangsan 這一行加鎖
  • Next-Key鎖: Next-Key鎖 = 記錄鎖 + 間隙鎖,所以該 UPDATE 語句就有了 (lisi,zhangsan] 的 Next-Key鎖
  • 綜上所述:更新張三的語句獲得了
    • Next-Key 鎖-> (lisi,zhangsan]
    • Gap鎖 -> (zhangsan,+∞)
  • 事務1在插入 wangwu 王五的時候
  • 間隙鎖: 因為 wangwu(在lisi和zhangsan之間),所以需要請求加 (lisi,zhangsan) 的間隙鎖

  • 插入意向鎖(Insert Intention):插入意向鎖是在插入一行記錄操作之前設定的一種間隙鎖,這個鎖釋放了一種插入方式的訊號,即事務A需要插入意向鎖 (lisi,zhangsan)

因此,事務1的 UPDATE 語句和 INSERT 語句執行完,它是持有了 (lisi,zhangsan] 的 Next-Key鎖,(zhangsan,+∞) 的Gap鎖,想拿到 (lisi,zhangsan) 的插入意向排它鎖

事務2的分析也如上舉例,我們直接給出答案

事務2的 UPDATE 語句和 INSERT 語句執行完,它是持有了 (-∞,lisi] 的 Next-Key鎖,(lisi,zhangsan) 的Gap鎖,想拿到 (lisi,zhangsan) 的插入意向排它鎖

鎖已經分析完畢了,接下來,我們需要去檢視一下事務的日誌結果

真相即將浮出水面

事務1期望拿到 (lisi,zhangsan) 的插入意向鎖,但是這個範圍當前被事務2的 (lisi,zhangsan] 的 gap 鎖佔有了,這兩把鎖又是衝突的

事務2期望拿到 (lisi,zhangsan) 的插入意向鎖,但是這個範圍被事務1的 (lisi,zhangsan] 的 Next-Key 鎖佔有了,這兩把鎖又是衝突的

所以死鎖發生。因為Innodb的底層機制,它會讓其中一個事務讓出資源,另外的事務執行成功,這就是為什麼你最後看到事務1插入成功了,但是事務2的插入顯示了Deadlock found

總結

死鎖原因已經分析出來了,那我們以後面對死鎖,整體解決思路是什麼呢?

  • ~~甩鍋運維~~
  • 模擬死鎖場景
  • show engine innodb status;檢視死鎖日誌
  • 找出死鎖SQL
  • SQL加鎖分析,這個可以去官網看哈
  • 分析死鎖日誌(持有什麼鎖,等待什麼鎖)
  • 熟悉鎖模式相容矩陣,InnoDB儲存引擎中鎖的相容性矩陣。

參考文章:

丁奇 《MySql 實戰45講》

撿田螺的小男孩 《手把手教你分析Mysql死鎖問題》


文章結束 🤣

如果本文對你有所幫助的話,那就點個贊吧

更多分享盡在微信公眾號【codeLiveHouse】

公眾號回覆 “資料” 可以獲取大廠面試題/技術文件/電子書等等


往期推薦

3 分鐘簡單理解橋接模式 來自大廠的11條異常最佳實踐 工廠設計模式,這幾個問題你知道嗎?