記一次線上問題引發的對 Mysql 鎖機制分析

来源:https://www.cnblogs.com/jingdongkeji/archive/2023/11/15/17833457.html
-Advertisement-
Play Games

最近雙十一開門紅期間組內出現了一次因 Mysql 死鎖導致的線上問題，當時從監控可以看到資料庫活躍連接數飆升，導致應用層資料庫連接池被打滿，後續所有請求都因獲取不到連接而失敗 ...


背景

最近雙十一開門紅期間組內出現了一次因 Mysql 死鎖導致的線上問題，當時從監控可以看到資料庫活躍連接數飆升，導致應用層資料庫連接池被打滿，後續所有請求都因獲取不到連接而失敗

整體業務代碼精簡邏輯如下：

@Transaction
public void service(Integer id) {
    delete(id);
    insert(id);
}

資料庫實例監控：

當時通過分析上游問題流量限流解決後，後續找時間又重新分析了下問題發生的根本原因，現將其總結如下：本篇文章會先對 Mysql 中的各種鎖進行分析，包括互斥鎖、間隙鎖和插入意向鎖，讓大家對各種鎖的使用場景有一個瞭解，然後在此基礎上再對本問題進行分析，希望大家未來再碰到相似場景時，能夠快速的定位問題

Mysql 鎖機制

在 Mysql 中為瞭解決對同一行記錄併發寫的問題，引入了行鎖機制，多個事務不能同時對一行數據進行修改操作，當需要對資料庫中的一行數據進行修改時，會首先判斷該行數據是否加鎖，如果沒加鎖，那麼當前事務加鎖成功，可以進行後續的修改操作；但如果該行數據已經被其他事務加鎖，則當前事務只有等待加鎖的事務釋放鎖後才能加鎖成功，繼續執行修改操作

本篇文章中所有實驗用到的建表語句：

create table `test` (
    `id` int(11) NOT NULL,
    `num` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `num` (`num`)
) ENGINE = InnoDB;

insert into
    test
values
(10, 10),
(20, 20),
(30, 30),
(40, 40),
(50, 50);

Shared and Exclusive Locks

shared(S) lock 表示共用鎖，當一個事務持有某行上的 S 鎖後可以對該行的數據進行讀操作，通過語句 select ... from test lock in share mode 可以添加共用鎖，一般使用的較少，不做過多闡述

exclusive(X) lock 表示互斥鎖，當一個事務對某行數據進行 update 或 delete 操作時都要先獲取到該記錄上的 X 鎖，如果已經有其他事務獲取到了該記錄上的 X 鎖，那麼當前事務會阻塞等待直到上一事務釋放了對應記錄上的 X 鎖

S 鎖之間不互斥，多個事務可以同時獲取一條記錄上的 S 鎖 X 鎖之間互斥，多個事務不能同時獲取同一條記錄上的 X 鎖 S 鎖和 X 鎖之間互斥，多個事務不能同時獲取同一條記錄上的 S 鎖和 X 鎖

當多個事務同時去 update 索引上同一條記錄時，都需要先獲取到該記錄上的 X 鎖，所謂的鎖也就是會在記憶體中生成一個數據結構來記錄當前的事務信息、鎖類型和是否等待等信息。下圖中就是 T1 和 T2 同時去更新 id = 30 的這行記錄，並且 T1 成功獲取到了鎖，其在記憶體中生成的鎖結構信息中欄位 is_wating 為 false，可以繼續執行事務的後續邏輯，而 T2 獲取鎖失敗，則生成的鎖結構信息欄位 is_wating 為 true，阻塞等待 T1 上的鎖釋放

互斥鎖在 Mysql 日誌中的鎖信息為：lock_mode X locks rec but not gap

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

Gap Locks

上一小節中介紹了 Exclusive Locks，該鎖可以避免多個事務同時對一行記錄進行更新操作，但不能解決幻讀的問題，所謂的幻讀就是指一個事務在前後兩次查詢同一個範圍時，後一次查詢到了前一次沒有的記錄

session A session B
T1 select num from test where num > 10 and num < 15 for update; (0 rows)
T2 insert into test values(12, 12);
T3 select num from test where num > 10 and num < 15 for update; (1 rows)

在上面這個場景中，session A 分別在 T1、T3 時刻進行了兩次範圍查詢，session B 在 T2 時刻插入了一條該範圍內的數據，如果 session A 能在 T3 時刻查詢出 session B 插入的數據，就說明發生了幻讀。此時只使用互斥鎖是無法解決幻讀的，因為 num = 12 的記錄在資料庫中還不存在，不能給其加上互斥鎖來防止 T2 時刻 session B 的插入

因此為瞭解決幻讀問題，只有引入新的鎖機制，也就是間隙鎖(Gap Locks)。間隙鎖和互斥鎖不同，互斥鎖是行鎖，只會鎖定一行特定的記錄，而間隙鎖則是鎖定兩行記錄之間的空隙，防止其他事務在此間隙中插入新的記錄

引入了間隙鎖之後，session A 在 T1 時刻會給 id = 20 記錄生成一個 Gap Locks，之後 session B 在 T2 時刻想要插入記錄時，需要先判斷待插入位置的後一條記錄上是否存在 Gap Locks，很明顯此時 id = 20 的記錄上已經存在了 Gap Locks，那麼session B 就需要在 id = 20 的記錄上生成一個插入意向鎖，併進入鎖等待

間隙鎖在 Mysql 中的鎖日誌信息如下：lock_mode X locks gap before rec

RECORD LOCKS space id 133 page no 3 n bits 80 index PRIMARY of table `test`.`test` trx id 38849 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000001e; asc     30 ;;
 1: len 6; hex 00000000969c; asc       ;;
 2: len 7; hex a60000011a0128; asc       (;;
 3: len 4; hex 8000001e; asc     ;;

間隙鎖雖然解決了幻讀問題，但因每次都會鎖住一段間隙，大大降低了資料庫整體的併發度，且因間隙鎖和間隙鎖之間不互斥，不同事務可以同時對同一間隙加上 Gap Locks，這也往往是各種死鎖產生的源頭

Next-Key Locks

Next-Key Locks 是 (Shard/Exclusive Locks + Gap Locks) 的結合，當 session A 給某行記錄 R 添加了互斥型的 Next-Key Locks 後， 相當於擁有了記錄 R 的 X 鎖和記錄 R 的 Gap Locks

在上面 Gap Locks 的例子中事務 1 加的就是 Next-Key Locks，即同時給 id = 20 的記錄加了 X 鎖和 Gap 鎖

在可重覆讀隔離級別下，update 和 delete 操作預設都會給記錄添加 Next-Key Locks，Mysql 中 Next-Key Locks 的鎖日誌信息為：lock_mode X

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;

Insert Intention Locks

插入意向鎖(Insert Intention Locks) 也是一種間隙鎖，由 INSERT 操作在行數據插入之前獲取

在插入一條記錄前，需要先定位到該記錄在 B+ 樹中的存儲位置，然後判斷待插入位置的下一條記錄上是否添加了 Gap Locks，如果下一條記錄上存在 Gap Locks，那麼插入操作就需要阻塞等待，直到擁有 Gap Locks 的那個事務提交，同時執行插入操作等待的事務也會在記憶體中生成一個鎖結構，表明有事務想在某個間隙中插入新記錄，但目前處於阻塞狀態，生成的鎖結構就是插入意向鎖

實驗模擬如下：

session 1 session 2 session 3
T1 begin;
T2 select * from test where id = 25 for update;
T3 insert into test values(26, 26); (blocked)
T4 insert into test values(26, 26); (blocked)

對於語句 select * from test where id = 25 for update 因當前表中不存在該記錄，在可重覆讀隔離級別下，為了避免幻讀，會給 (20, 30] 間隙加上 Gap Locks

從鎖日誌可以看出 session 1 給記錄 30 添加了間隙鎖(lock_mode X locks gap before rec)

RECORD LOCKS space id 133 page no 3 n bits 80 index PRIMARY of table `test`.`test` trx id 38849 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000001e; asc     30 ;;
 1: len 6; hex 00000000969c; asc       ;;
 2: len 7; hex a60000011a0128; asc       (;;
 3: len 4; hex 8000001e; asc     ;;

當 session 2 插入記錄 26 時，會在 B+ 樹中先定位到待插入位置，再判斷插入位置的間隙是否存在 Gap Locks，也就是判斷待插入位置的後一記錄 id = 30 是否存在 Gap Locks，如果存在需要在該記錄上生成插入意向鎖等待

RECORD LOCKS space id 133 page no 3 n bits 80 index PRIMARY of table `test`.`test` trx id 38850 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000001e; asc    30 ;;
 1: len 6; hex 00000000969c; asc       ;;
 2: len 7; hex a60000011a0128; asc       (;;
 3: len 4; hex 8000001e; asc     ;;

此時 session 2 和 session 3 都在 id = 30 的記錄上添加了插入意向鎖等待 session 1 上的 Gap Locks 釋放，生成的鎖記錄如下：

線上問題分析

在對 Mysql 中的各種鎖結構有了一個清晰的瞭解之後，回過頭來再看看前面的線上問題

@Transaction
public void service(Integer id) {
    delete(id);
    insert(id);
}

對於上面的業務代碼可能存在下麵兩種情況:

  • 傳入的參數 id 在原資料庫中不存在
  • 傳入的參數 id 在原資料庫中存在

本次主要會針對 id 記錄在原資料庫中不存在進行分析

session 1 session 2 session 3
T1 delete from test where id = 15;
T2 delete from test where id = 15; delete from test where id = 15;
T3 insert into test values(15, 15);
T4 insert into test values(15, 15);
T5 insert into test values(15, 15);

因 id = 15 在資料庫中不存在，在 T1 時刻 session 1 會給其所在間隙的下一條記錄添加上 Gap Locks，又因 Gap Locks 不互斥， 在 T2 時刻 session 2 和session 3 都會同時獲取到 id = 20 的 Gap 鎖

下圖中 tx: T1、T2、T3 分別代表 session 1、session 2 和 session 3

當在 T3 時刻 session 1 插入 id = 15 的記錄時，會判斷其插入位置的後一條記錄是否存在 Gap Locks，如果存在，則需要在該記錄上生成 Insert Intention Locks 並等待持有 Gap Locks 的事務釋放鎖

在 T4 時刻 session 2 執行插入語句，同樣會因插入位置的後一條記錄中存在 Gap Locks 而需要生成 Insert Intention Locks 等待。此時很明顯就形成了死鎖，session 1 生成插入意向鎖等待 session 2 和 session 3 上的 Gap 鎖釋放，而 session 2 同樣生成插入意向鎖等待 session 1 和 session 3 上的 Gap 鎖釋放

在 T4 時刻檢測到死鎖後，Mysql 會選擇其中一個事務進行回滾，假設此時 session 2 被回滾，釋放了其持有的所有鎖資源，session 1 可以繼續執行嗎？ 很明顯不可以，session 1 還同時在等待 session 3 上的 Gap 鎖釋放，繼續阻塞等待

在 T5 時刻 session 3 開始執行插入語句，此時同 T4 時刻，死鎖形成，session 1 生成的插入意向鎖正在等待 session 3 上的 Gap Locks 釋放，session 3 上生成的插入意向鎖正在等待 session 1 上的 Gap Locks 釋放，此時 session 3 回滾釋放所有鎖資源後，session 1 才可以最終執行成功

在完成了三個併發線程的死鎖分析後，可能有人會想雖然有死鎖，但通過死鎖檢測可以很快的檢測出，程式也可以正常的執行，這有什麼問題呢？ 其實上面沒有問題主要是因為併發量較小，死鎖檢測可以很快檢測出，如果此時將併發量擴大 100 倍甚至 1000 倍後，還會沒有問題嗎？

看看當時出現線上問題時，介面的調用量情況，

進一步在本地模擬 300 個線程併發執行，因人腦併發分析所有事務的執行情況的話會非常複雜，本次只以事務 1 為一個點來進行分析

從圖中可以看到當 T1 在執行插入語句時，需要等待 T2- T101 上持有的 Gap Locks 釋放，之後 T2 - T6 可能同時執行插入語句，然後進行死鎖檢測，事務回滾，看著似乎只要後續有事務執行了插入語句就會執行死鎖回滾，正常運行，但在死鎖檢測的過程中還會有新事務(T101 - T 200 )獲取到 Gap Locks，造成鎖等待隊列中的事務越來越多，而 Mysql 的整體死鎖檢測時間複雜度為 O(n^2)，鎖等待隊列中的事務較多時，每一次有新事務進行鎖等待，死鎖檢測都需要遍歷鎖等待隊列中在其之前等待的事務，判斷是否會因自己的加入形成環，此時檢測會非常消耗 CPU 資源，造成資料庫整體性能下降，死鎖檢測耗時增加，Mysql 活躍連接數大幅增加，並且因鎖等待而連接無法釋放，最終造成應用層連接池被打滿

綜上分析，本次出現問題的最主要原因是在短時間記憶體在大併發的請求對同一行數據進行先刪除再插入操作(先更新再插入同理)，造成了死鎖等待，應用層連接池被打滿，大量上游請求超時重試，進一步導致鎖等待，最終影響了所有依賴該資料庫的業務

因此對於未來在業務代碼中存在相似邏輯的地方，一定要做好防重校驗，避免短時間記憶體在對同一行數據的先更新再插入的併發操作。同時在可重覆讀隔離別下，更新和刪除操作預設都會添加 Next-Key Locks，間隙鎖的引入使得死鎖問題在併發情況下很容易出現，這也是在業務邏輯實現上需要考慮的問題。

總結

本文以一個線上問題為背景，對 Mysql 中的各種鎖機制進行了詳細的總結，分析了各個鎖的加鎖時機和具體使用場景，其中特別要註意間隙鎖的使用，因間隙鎖和間隙鎖之間不互斥，當多個事務之間併發執行時很容易形成死鎖

作者：京東物流 張弓言

來源：京東雲開發者社區 自猿其說Tech 轉載請註明來源


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Spring5學習隨筆-Spring5的第一個程式（環境搭建、日誌框架整合）
    學習視頻：【孫哥說Spring5：從設計模式到基本應用到應用級底層分析，一次深入淺出的Spring全探索。學不會Spring？只因你未遇見孫哥】 第二章、第一個Spring程式 1.軟體版本 1.JDK1.8+ 2.Maven3.5+ 3.IDEA2018+ 4.SpringFramework 5. ...
  • final關鍵字的作用
    目錄1.修飾類時2.修飾方法時3.修飾屬性和局部變數時3.1修飾局部變數時3.2修飾成員變數時3.3修飾類變數時4.final與普通變數的區別5.final用於引用 1.修飾類時 1.只能是公共的（public）就算不寫也是public 2.被final修飾的類不可以被繼承 //前面預設有個publ ...
  • 【scipy 基礎】--圖像處理
    SciPy庫本身是針對科學計算而不是圖像處理的，只是圖像處理也包含了很多數學計算，所以Scipy也提供了一個專門的模塊ndimage用於圖像處理。 ndimage模塊提供的功能包括輸入/輸出圖像、顯示圖像、基本操作（如裁剪、翻轉、旋轉等）、圖像過濾（如去噪、銳化等）、圖像分割、分類、特征提取以及註冊 ...
  • Welcome to YARP - 7.目標健康檢查
    目錄 Welcome to YARP - 1.認識YARP並搭建反向代理服務 Welcome to YARP - 2.配置功能 2.1 - 配置文件（Configuration Files） 2.2 - 配置提供者（Configuration Providers） 2.3 - 配置過濾器（Confi ...
  • 記一次 .NET 某券商論壇系統 卡死分析
    一：背景 1. 講故事 前幾個月有位朋友找到我，說他們的的web程式沒有響應了，而且監控發現線程數特別高，記憶體也特別大，讓我幫忙看一下怎麼回事，現在回過頭來幾經波折，回味價值太濃了。 二：程式到底經歷了什麼 1. 線上程上找原因 這個程式記憶體高，線程高，無響應，尼瑪是一個複合態問題，那怎麼入手呢？按 ...
  • Unity播放Hap格式視頻 (大分辨下流暢播放以及幀同步解決方案)
    前言: 之前對於項目上播放大解析度視頻(特別是大於4k解析度的)常常會感覺相當的頭疼,最開始使用的是Unity自帶的VideoPlayer,發現效果並不理想,更換為AVPro後發現播放是流暢了 但不能操作視頻快進,只要一快進就會出現卡頓,最後偶然間發現了一款用於播放Hap格式視頻的插件才最終解決了這 ...
  • Net 高級調試之八：代碼審查及雜項命令
    1.HighLightingSystem 用於3D物體高亮顯示 在項目中的使用：導入插件後在需要高亮顯示的3d物體上附加Highlighter組件，在需要顯示高亮效果的攝像機上附加Highlighting Renderer組件。在代碼中調整Highlighter屬性即可控制物體高亮效果的開關、閃爍。 ...
  • MySQL Shell連接資料庫報MySQL Error 1045 (28000)錯誤淺析
    這裡簡單總結一下mysql shell訪問資料庫時報MySQL Error 1045 (28000): Access denied for user 'root'@'::1' (using password: YES)的原因以及如何解決這個問題 這裡測試的環境為MySQL 8.0.35，我們先來看看 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類