線上SQL超時場景分析-MySQL超時之間隙鎖

来源:https://www.cnblogs.com/Jcloud/archive/2023/11/10/17823419.html
-Advertisement-
Play Games

資料庫底層實現博大精深,本文所述,根據線上場景進行了一些研究和探討,希望能為相關場景提供一些啟示。文章中難免會有不足之處,希望讀者能給予寶貴的意見和建議 ...


前言

之前遇到過一個由MySQL間隙鎖引發線上sql執行超時的場景,記錄一下。

背景說明

分散式事務消息表:業務上使用消息表的方式,依賴本地事務,實現了一套分散式事務方案

消息表名:mq_messages

數據量:3000多萬

索引:create_time 和 status

status:有兩個值,1 和 2, 其中99%以上的狀態都是2,表示分散式事務全部已經執行完成,可以刪除。

消息表處理邏輯

1. 啟動一個獨立的定時任務,刪除status=2的歷史數據,具體的sql如下:

    delete from mq_messages where create_time<xxx and status=2 limit 200

2. 定時任務執行頻率:3分鐘跑一次任務,一個任務執行200次 刪除。這個條件基本上篩選出了90%以上的數據

業務邏輯:線上業務在執行時,不斷的往表裡插入status=1的數據,主鍵id隨著時間是遞增的

sql超時產生的場景

一次大型促銷活動流量峰值的時候,出現了一次資料庫連接被打滿的情況,初步定位是數據量太大了導致鎖表導致的。為了防止資料庫連接被再次打滿,需要儘快的刪除狀態為2的數據,手動執行定時任務,刪除數據,具體sql為:

delete from mq_messages where status=2 limit 2000

三分鐘執行一次任務,一個任務執行200次刪除。

然後,資料庫連接馬上被打滿,資料庫掛了

復盤分析

線上是否存在表鎖?

初始化表結構(簡化後的表結構)

CREATE TABLE `my_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `state` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `state` (`state`) USING BTREE
) 
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

存儲過程準備測試數據

DELIMITER $$
  CREATE PROCEDURE pro_copy_date()
  BEGIN
  SET @i=1;
  WHILE @i<=100000 DO
  INSERT INTO my_test VALUES(@i,@i,@i,1); 
  SET @i=@i+1;
  END WHILE;
END $$
call pro_copy_date();
UPDATE my_test SET state =2 WHERE id <= 99990;

驗證

1. 數據基本情況

表中一共有10萬條數據,只有後10條的state=1(id>99990)

2. 事務隔離級別可重覆讀

3. 開啟一個事務A,並且不提交

執行 DELETE FROM my_test WHERE state =2 LIMIT 2000;

4. 開啟另一個事務B

• 更新id=2001的數據,可以更新成功

• 更新id=2000的數據,被阻塞

說明沒有表鎖

5. 開啟另一個事務C

• 插入狀態為2的數據,可以插入成功

• 插入狀態為0的數據,可以插入成功

• 插入狀態為1的數據,被阻塞

說明state的1和2的間隙被鎖導致不能插入

結論

線上不存在表鎖,而是間隙鎖

間隙鎖

線上間隙鎖場景分析

表中state一共兩個值1和2。因此會產生三個間隙 (-∞, 1), (1, 2), (2, +∞) 和兩個孤值1和2。根據前開後閉原則,對應的臨建鎖區間為 (-∞, 1], (1, 2],(2, +∞)

執行DELETE FROM my_test WHERE state =2 LIMIT 2000時,掃描到的行數為(state=2, id=1)到(state=2,i d=2000)。state=2落在區間](1,2]。因此鎖住的範圍是(state=1,id=100000) 到 (state=2,id=2000),如圖所示:

對於線上場景鎖的範圍是(state=1, id=status為1的最大id) 到 (state=2, id=要刪除的記錄中id的最大值)。由於線上只會插入state=1而且,id是遞增的。新插入的id是表的最大值,所以新插入的記錄一定會落在鎖區間,所以新插入的記錄都會被阻塞。

間隙鎖作用

解決幻讀

幻讀指的是一個事務在前後兩次查詢同一個範圍的時候,後一次查詢看到了前一次查詢沒有看到的數據行。

幻讀專門指的是新插入的數據。

在可重覆讀隔離級別下,普通的查詢是快照讀,是不會看到別的事務插入的數據的。幻讀在“當前讀”下才會出現。innodb解決幻讀的方法,間隙鎖。

幻讀帶來的問題

新建測試表:

CREATE TABLE `my_test2` (
  `id` INT (11) NOT NULL,
  `b` INT (11) DEFAULT NULL,
  `c` INT (11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE = INNODB;

-- 插入測試數據
NSERT INTO my_test2  VALUES(0, 0, 0),(5, 5, 5),(10, 10, 10),(15, 15, 15);

測試sql 1

begin;
select * from t where b=5 for update;

這個語句會命中 b=5 的這一行,對應的主鍵 id=5,因此在 select 語句執行完成後,id=5 這一行會加一個寫鎖,這個寫鎖會在執行 commit 語句的時候釋放。

由於欄位 b 上沒有索引,因此這條查詢語句會做全表掃描。那麼,其他被掃描到的不滿足條件的記錄上,會不會被加鎖呢?

假如只會在id為5的記錄上加鎖:

事務A 事務B 事務C
T1 BEGIN; SELECT * FROM my_test2 where b=5 FOR UPDATE; 結果(5,5,5)
T2 UPDATE my_test2 SET b=5 WHERE id = 0;
T3 SELECT * FROM my_test2 where b=5 FOR UPDATE; 結果(0,5,0)(5,5,5)
T4 INSERT INTO my_test2(1,5,1)
T5 SELECT * FROM my_test2 where b=5 FOR UPDATE; 結果(0,5,0)(1,5,1)(5,5,5)
T6 commit

事務A 事務B
T1 BEGIN; SELECT * FROM my_test2 where b=5 FOR UPDATE;
T2 UPDATE my_test2 SET b=5 WHERE id = 0; UPDATE my_test2 SET c=5 WHERE id = 0;
T3 commit

假如只會在id為5的記錄上加鎖,會破壞事務A的加鎖聲明,即“把所有 b=5 的行鎖住,不准別的事務進行讀寫操作

事務A 事務B 事務C
T1 BEGIN; SELECT * FROM my_test2 WHERE b=5 FOR UPDATE; UPDATE my_test2 SET c=10 WHERE b=5;
T2 UPDATE my_test2 SET b=5 WHERE id = 0;
T3 INSERT INTO my_test2(1,5,1)
T4 commit

T1時刻: id=5的這行數據,的c的值改成了10,事務還沒提交,binlog還沒寫

T2時刻:id=0 這一行變成 (0,5,0), 變更寫入binlog;

T3時刻:id=1 這一行變成 (1,5,1), 變更寫入binlog;

T4時刻:事務A提交,寫入binlog。

此時主庫的數據為(0,5,0),(1,5,1),(5,5,10)

因此binlog寫入的日誌為:

UPDATE my_test2 SET b=5 WHERE id = 0;
INSERT INTO my_test2(1,5,1)
UPDATE my_test2 SET c=10 WHERE b=5;

從庫執行完成binglog後數據就變成了(0,5,10),(1,5,10),(5,5,10),因此出現了數據的不一致

出現數據不一致的原因,是只鎖了那一刻需要變更的行,並不能阻擋現有數據變成b=5

如果把掃描到的行全部加鎖會如何哪?由於b沒有索引,索引得掃描全表才知道那一行需要更新,所以表中的每一條記錄都會被鎖住。

事務A 事務B 事務C
T1 BEGIN; SELECT * FROM my_test2 where b=5 FOR UPDATE; UPDATE my_test2 SET c=10 WHERE b=5;
T2 UPDATE my_test2 SET b=5 WHERE id = 0; (block)
T3 INSERT INTO my_test2(1,5,1)
T4 commit

T1時刻: id=5的這行數據,的c的值改成了10,事務還沒提交,binlog還沒寫

T2時刻:id為0的行被鎖住,不能更新,等待鎖釋放;

T3時刻:id=1 這一行變成 (1,5,1), 變更寫入binlog;

T4時刻:事務A提交,寫入binlog。

T5時刻:事務A已提交,id=0的鎖被釋放,事務B更新成功,變成 (0,5,0),寫入binlog

此時主庫的數據為(0,5,0),(1,5,1),(5,5,10)

因此binlog寫入的日誌為:

INSERT INTO my_test2(1,5,1)
UPDATE my_test2 SET c=10 WHERE b=5;
UPDATE my_test2 SET b=5 WHERE id = 0;

從庫執行完成binglog後數據就變成了(0,5,0),(1,5,10),(5,5,10),因此還是存在數據不一致

鎖定了查找過程中掃描的行,有效的避免了修改帶來的數據不一致問題。數據之間的間隙插入的數據依然會出現b=5的數據,因此要向解決這個問題我們還需在數據的間隙加鎖

事務A 事務B 事務C
T1 BEGIN; SELECT * FROM my_test2 b=5 FOR UPDATE; UPDATE my_test2 SET c=10 WHERE b=5;
T2 UPDATE my_test2 SET b=5 WHERE id = 0; (block)
T3 INSERT INTO my_test2(1,5,1) (block)
T4 commit

T1時刻: id=5的這行數據,的c的值改成了10,事務還沒提交,binlog還沒寫

T2時刻:id為0的行被鎖住,不能更新等待鎖釋放;

T3時刻:間隙(0,5)被鎖住,不能插入等待鎖釋放;

T4時刻:事務A提交,寫入binlog。

T5時刻:事務A已提交,id=0的鎖被釋放,事務B更新成功,變成 (0,5,0),寫入binlog

T6時刻:事務A已提交,(0,5)的間隙鎖被釋放,事務C寫入成功,變成 (1,5,1),寫入binlog

此時主庫的數據為(0,5,0),(1,5,1),(5,5,10)

因此binlog寫入的日誌為:

UPDATE my_test2 SET c=10 WHERE b=5;
UPDATE my_test2 SET b=5 WHERE id = 0;
INSERT INTO my_test2(1,5,1)

從庫執行完成binglog後數據就變成了(0,5,0),(1,5,1),(5,5,10),完美解決了數據不一致

通過上面兩個情況分析,如果只鎖對應修改的行,會出現兩個問題

1. 破壞加鎖聲明

2. 數據的不一致性

幻讀的解決方法

通過上面案例分析,即使把所有的記錄都加上鎖,還是阻止不了新插入的記錄。行鎖只能鎖住行,但是新插入記錄這個動作,要更新的是記錄之間的“間隙”。因此,為瞭解決幻讀問題,InnoDB 只好引入新的鎖,也就是間隙鎖 (Gap Lock)

間隙鎖,鎖的就是兩個值之間的空隙,表中一共有4條數據,因此會產生五個間隙 (-∞, 0), (0, 5), (5, 10), (10, 15), (15, +∞),在掃描確認要修改的行時,不僅僅要鎖住掃描到的行,兩邊的間隙也要加上鎖。

間隙鎖和行鎖合稱 next-key lock(鄰鍵鎖),每個 next-key lock 是前開後閉區間。因此上述情況會有五個鄰鍵鎖(-∞,0],(0,5],(5,10],(10,15],(15, +∞)

間隙鎖可以被多個事務同時加

間隙鎖和行鎖有區別,行鎖只能被一個事務加上,但是間隙鎖可以被多個事務加上。

如下圖:開啟兩個事務,

1. 事務A執行:SELECT * FROM my_test2 WHERE id=2 for UPDATE; 會鎖住(0,5)這個間隙。

2. 事務B執行SELECT * FROM my_test2 WHERE id=3 for UPDATE;,同樣也會鎖住(0,5)這個間隙,而且可以成功。

間隙鎖的目前是保護這個間隙不能插入數據,但他們不衝突。

加鎖規則

原則1:加鎖的基本單位是 next-key lock,next-key lock 是前開後閉區間。

原則2:查找過程中訪問到的對象才會加鎖。

優化1:索引上的等值查詢,給唯一索引加鎖的時候,next-key lock 退化為行鎖。

優化2:索引上的等值查詢,向右遍歷時且最後一個值不滿足等值條件的時候,next-key lock 退化為間隙鎖。

唯一索引上的範圍查詢會訪問到不滿足條件的第一個值為止

加鎖規則—等值查詢間隙鎖

事務A執行UPDATE my_test2 SET b=100 WHERE id =7;

根據原則1,加鎖的區間應該為(5,10].

根據優化2,這是一個等值查詢 ,而 id=10 不滿足查詢條件,next-key lock 退化成間隙鎖,因此最終加鎖的範圍是 (5,10)。

因此:事務B的插入會被阻塞,事務C的更新可以成功

事務A:

事務B:

事務C:

加鎖規則—非唯一索引等值查詢

事務A執行SELECT id FROM my_test2 WHERE c=5 lock in share mode``;

根據原則1,加鎖的區間應該為(0,5],由於c不是唯一索引還得往後掃描,因此(5,10]也會被加鎖。根據優化2,會退化成(5,10)。因此索引c上的鎖區間為(0,10)。

由於這個查詢走的是索引覆蓋,並不需要去主鍵索引查數據,因此id=5的行並不會被鎖住 。

所以更新會成功,插入不會成功

事務A執行SELECT * FROM my_test2 WHERE c=5 lock in share mode;

由於 查詢全部的數據就需要,去主鍵索引上查找id=5的數據,根據原則2,id=5的這行數據也要被鎖住,因此更新會被阻塞。

註意,如果執行的語句為SELECT id FROM my_test2 WHERE c=5 for UPDATE;雖然這個語句也會走索引覆蓋,但是用for update mysql會認為你接下來要更新這行,因此順便會給id=5的這行加鎖。

加鎖規則—非唯一索引,存在等值

新插入兩條數數據(20,20,5)和(30,30,5)

執行sql: DELETE FROM my_test2 WHERE c=5 LIMIT 2;

根據加鎖原則,只會掃描c=5的數據,因此加鎖區間為

(c=0,id=0) 到 (c=5,id=20)

INSERT INTO my_test2 VALUES(-1,0,0); //不阻塞

INSERT INTO my_test2 VALUES(1,0,0); //阻塞

INSERT INTO my_test2 VALUES(19,0,5); //阻塞

INSERT INTO my_test2 VALUES(21,0,5); //不阻塞

執行結果驗證:

資料庫底層實現博大精深,本文所述,根據線上場景進行了一些研究和探討,希望能為相關場景提供一些啟示。文章中難免會有不足之處,希望讀者能給予寶貴的意見和建議。謝謝!

作者:京東物流 劉浩

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


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

-Advertisement-
Play Games
更多相關文章
  • 常用的包 import torch import torchvision from torch import nn from torch.utils.data import DataLoader from torch.nn import Conv2d, MaxPool2d, Flatten, Lin ...
  • 在使用事務的時候需要添加@EnableTransactionManagement註解來開啟事務,Spring事務底層是通過AOP來實現的,所以啟用事務後,同樣會向容器中註入一個代理對象創建器,AOP使用的是AnnotationAwareAspectJAutoProxyCreator,事務使用的是In ...
  • 前言: 繼上篇:Taurus .Net Core 微服務開源框架:Admin 插件【4-3】 - 配置管理 - Mvc【Plugin-MicroService 微服務】 本篇繼續介紹下一個內容: 系統配置節點:Mvc - Plugin - CORS 跨域界面: 界面如下: 跨域功能相關配置說明如下: ...
  • 目錄 Welcome to YARP - 1.認識YARP並搭建反向代理服務 Welcome to YARP - 2.配置功能 2.1 - 配置文件(Configuration Files) 2.2 - 配置提供者(Configuration Providers) 2.3 - 配置過濾器(Confi ...
  • 前言 由於業務需要,需要多台雲伺服器,但是公有雲的帶寬價格不菲,所以不可能給所有的雲伺服器都配上公網IP,一方面是成本的問題,另一方面也是公網安全的問題。 所以通過其它的方式使用無公網的雲伺服器來來實現對外資源的訪問。 一、操作步驟 至少需要有一臺具有公網IP的雲伺服器! 1、開啟ECS的路由轉發功 ...
  • 近幾年IT界軟硬體“國產化”搞得很密集,給很多公司帶來了商機。但是有些公司拿國外的代碼改改換個皮膚,就是“自主知識產權”的國產軟體,光明正大賣錢,這個有點...,還經常有醜聞露出,譬如某星瀏覽器、C某-IDE... 話不多說,最近有個項目需要國產化改造,業主方推薦了國產資料庫 瀚高資料庫,我們原來用 ...
  • 本文分享自華為雲社區《淺析KV存儲之長尾時延問題,華為雲 GeminiDB Redis 探尋行業更優解決方案!》,作者:華為雲資料庫GaussDB NoSQL團隊。 目前,KV存儲的廣泛使用極大程度上源於快速訪問的業務需求,而這種業務通常對時延敏感度高,在較好的平均性能下,還需要解決特定場景下的性能 ...
  • 在之前的實時湖倉系列文章中,我們已經介紹了實時湖倉對於當前企業數字化轉型的重要性,實時湖倉的功能架構設計,以及實時計算和數據湖結合的應用場景。 在本篇文章中,將介紹袋鼠雲數棧在構建實時湖倉系統上的探索與落地實踐,及未來規劃。 數棧為什麼選擇實時湖倉 數棧作為一個數據開發平臺,在未引入實時湖倉之前提供 ...
一周排行
    -Advertisement-
    Play Games
  • 在C#中使用SQL Server實現事務的ACID(原子性、一致性、隔離性、持久性)屬性和使用資料庫鎖(悲觀鎖和樂觀鎖)時,你可以通過ADO.NET的SqlConnection和SqlTransaction類來實現。下麵是一些示例和概念說明。 實現ACID事務 ACID屬性是事務處理的四個基本特征, ...
  • 我們在《SqlSugar開發框架》中,Winform界面開發部分往往也用到了自定義的用戶控制項,對應一些特殊的界面或者常用到的一些局部界面內容,我們可以使用自定義的用戶控制項來提高界面的統一性,同時也增強了使用的便利性。如我們Winform界面中用到的分頁控制項、附件顯示內容、以及一些公司、部門、菜單的下... ...
  • 在本篇教程中,我們學習瞭如何在 Taurus.MVC WebMVC 中進行數據綁定操作。我們還學習瞭如何使用 ${屬性名稱} CMS 語法來綁定頁面上的元素與 Model 中的屬性。通過這些步驟,我們成功實現了一個簡單的數據綁定示例。 ...
  • 是在MVVM中用來傳遞消息的一種方式。它是在MVVMLight框架中提供的一個實現了IMessenger介面的類,可以用來在ViewModel之間、ViewModel和View之間傳遞消息。 Send 接受一個泛型參數,表示要發送的消息內容。 Register 方法用於註冊某個對象接收消息。 pub ...
  • 概述:在WPF中,通過EventHandler可實現基礎和高級的UI更新方式。基礎用法涉及在類中定義事件,併在UI中訂閱以執行更新操作。高級用法藉助Dispatcher類,確保在非UI線程上執行操作後,通過UI線程更新界面。這兩種方法提供了靈活而可靠的UI更新機制。 在WPF(Windows Pre ...
  • 概述:本文介紹了在C#程式開發中如何利用自定義擴展方法測量代碼執行時間。通過使用簡單的Action委托,開發者可以輕鬆獲取代碼塊的執行時間,幫助優化性能、驗證演算法效率以及監控系統性能。這種通用方法提供了一種便捷而有效的方式,有助於提高開發效率和代碼質量。 在軟體開發中,瞭解代碼執行時間是優化程式性能 ...
  • 概述:Cron表達式是一種強大的定時任務調度工具,通過配置不同欄位實現靈活的時間規定。在.NET中,Quartz庫提供了簡便的方式配置Cron表達式,實現精準的定時任務調度。這種靈活性和可擴展性使得開發者能夠根據需求輕鬆地制定和管理定時任務,例如每天備份系統日誌或其他重要操作。 Cron表達式詳解 ...
  • 概述:.NET提供多種定時器,如System.Windows.Forms.Timer適用於UI,System.Web.UI.Timer用於Web,System.Diagnostics.Timer用於性能監控,System.Threading.Timer和System.Timers.Timer用於一般 ...
  • 問題背景 有同事聯繫我說,在生產環境上,訪問不了我負責的common服務,然後我去檢查common服務的health endpoint, 沒問題,然後我問了下異常,timeout導致的System.OperationCanceledException。那大概率是客戶端的問題,會不會是埠耗盡,用ne ...
  • 前言: 在本篇 Taurus.MVC WebMVC 入門開發教程的第四篇文章中, 我們將學習如何實現數據列表的綁定,通過使用 List<Model> 來展示多個數據項。 我們將繼續使用 Taurus.Mvc 命名空間,同時探討如何在視圖中綁定並顯示一個 Model 列表。 步驟1:創建 Model ...