MySQL InnoDB加鎖規則分析

来源:https://www.cnblogs.com/cjsblog/archive/2023/12/20/17914390.html
-Advertisement-
Play Games

1. 基礎知識回顧 1、索引的有序性，索引本身就是有序的 2、InnoDB中間隙鎖的唯一目的是防止其他事務插入間隙。間隙鎖可以共存。一個事務取得的間隙鎖並不會阻止另一個事務取得同一間隙上的間隙鎖。共用和獨占間隔鎖之間沒有區別。它們彼此之間不衝突，並且執行相同的功能。 3、MySQL預設隔離級別是 R ...


1.  基礎知識回顧

1、索引的有序性，索引本身就是有序的

2、InnoDB中間隙鎖的唯一目的是防止其他事務插入間隙。間隙鎖可以共存。一個事務取得的間隙鎖並不會阻止另一個事務取得同一間隙上的間隙鎖。共用和獨占間隔鎖之間沒有區別。它們彼此之間不衝突，並且執行相同的功能。

3、MySQL預設隔離級別是 REPEATABLE-READ

4、加鎖的對象是索引，加鎖的基本單位是next-key鎖，而行鎖和間隙鎖，是由next-key鎖退化而來的

5、記錄鎖，鎖的是索引，而非數據本身

6、間隙鎖是開區間，next-key鎖是前開後閉區間

7、意向鎖是表級鎖，它相當於一個標誌，可以用來提高加鎖的效率

8、間隙鎖的目的是為了防止幻讀，在“讀已提交”隔離級別下允許幻讀，所以如果隔離級別是“讀已提交”，就不會用到間隙鎖，更不會用到next-key鎖。因此，只有“可重覆讀”及以上隔離級別下，才會有next-key鎖

9、InnoDB中鎖住的是索引。對輔助索引加鎖時，輔助索引所對應的主鍵索引也會被鎖住。

10、所謂“間隙”本質是又間隙右邊的那條記錄決定的

 

接下來，具體看一下走不同的索引時的加鎖情況。本例中使用的MySQL版本為8.0.30

SELECT VERSION();
SHOW VARIABLES LIKE 'transaction_isolation';
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

測試表結構及數據如下：

2.  案例分析

LOCK_MODE不同值的含義：

  • X ：代表next-key鎖
  • X,GAP ：代表間隙鎖
  • X,REC_NOT_GAP ：代表記錄鎖

2.1.  主鍵索引

情況一：等值查詢，存在

Session A Session B
BEGIN;
SELECT * FROM t_user WHERE id = 10 FOR UPDATE;		  
 

INSERT INTO t_user (id, `name`, id_card_no, birthday, score) VALUES (9, '於禁', '1012', '2023-11-01', 1);

Affected rows: 1

首先對錶加意向排它鎖，然後對主鍵加記錄鎖，可以看到只鎖住了id=10這個主鍵索引

情況二：等值查詢，不存在

Session A Session B Session C
BEGIN;
SELECT * FROM t_user WHERE id = 5 FOR UPDATE;		    
 

INSERT INTO t_user (id, `name`, id_card_no, birthday, score) VALUES (6, '於禁', '1012', '2023-11-01', 1);

1205 - Lock wait timeout exceeded; try restarting transaction

  
   

UPDATE t_user SET score = score + 1 WHERE id = 10;

Affected rows: 1

加鎖範圍： (-∞, 10)

註意，是開區間，10並沒有被鎖

情況三：範圍查找

Session A Session B Session C
BEGIN;
SELECT * FROM t_user WHERE id >= 10 AND id < 11 FOR UPDATE;		    
 

INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (9,'典韋','1011','2022-12-19',1)

Affected rows: 1

  
   

INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (18,'徐晃','1018','2022-12-09',1);

1205 - Lock wait timeout exceeded; try restarting transaction

一個記錄鎖10，加一個間隙鎖(10, 20)，合起來就是[10, 20)

鎖定區間：[10, 20)

Session A Session B Session C
BEGIN;
SELECT * FROM t_user WHERE id >= 10 AND id <= 20 FOR UPDATE;		    
 

UPDATE t_user SET score = score + 1 WHERE id = 20;

1205 - Lock wait timeout exceeded; try restarting transaction

  
   

INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (21,'張遼','1021','2022-12-09',1);

Affected rows: 1

id=10上加了記錄鎖，id=20上加了next-key鎖

next-key鎖是前開後閉區間，所以，最終鎖定區間為：[10,20]

如果這裡不是id>=10，而是id>10的話，最終只會在id=20上加next-key鎖，這種情況下鎖定區間為：(10,20]

2.2.  唯一索引（非主鍵）

情況一：等值查詢，存在

Session A Session B
BEGIN;
SELECT * FROM t_user WHERE id_card_no = '1003' FOR UPDATE;		  
 

UPDATE t_user SET score = score + 1 WHERE id = 30;

1205 - Lock wait timeout exceeded; try restarting transaction

輔助索引 ('1003',30)加記錄鎖，同時，主鍵索引上id=30加記錄鎖

情況二：等值查詢，不存在

先看一眼現在的數據

Session A Session B Session C
BEGIN;
SELECT * FROM t_user WHERE id_card_no = '1042' FOR UPDATE;		    
 

INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (52,'許褚','1041','2023-01-01',1);

1205 - Lock wait timeout exceeded; try restarting transaction

  
   

UPDATE t_user SET score = score + 1 WHERE id_card_no = '1041';

Affected rows: 0

只在輔助索引idx_card上加了間隙鎖，鎖定範圍是：('1040', '1050')

索引是有序的，儘管索引欄位類型是字元串類型，仍然是有序的

因為是間隙鎖，所以沒有鎖定1050，也就自然不會給id=50加記錄鎖

值得註意的是，在('1040', '1050')這個區間內插入是不行的，但是更新是可以的

情況三：範圍查找

Session A Session B Session C
BEGIN;
SELECT * FROM t_user WHERE id_card_no <= '1024' FOR UPDATE;		    
 

INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (11,'潘鳳','1011','2023-01-01',1);

1205 - Lock wait timeout exceeded; try restarting transaction

  
   

INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (11,'潘鳳','1031','2023-01-01',1);

Affected rows: 1

主鍵索引上id=10和id=20都加了記錄鎖

輔助索引idx_card上加了Next-key鎖，鎖定範圍為：(-∞, '1010']、('1010', '1020']、('1020', '1030']

2.3.  非唯一索引（普通索引）

情況一：等值查詢，存在

Session A Session B Session C
BEGIN;
SELECT * FROM t_user WHERE birthday = '2023-12-01' FOR UPDATE;		    
 

UPDATE t_user SET score = score + 1 WHERE birthday = '2023-12-11';

Affected rows: 0

  
   

UPDATE t_user SET score = score + 1 WHERE birthday = '2023-12-09';

Affected rows: 0

主鍵索引id=10加記錄鎖

輔助索引idx_birthday上，'2023-12-01'上加Next-key鎖，'2023-12-12'上加間隙鎖

加鎖區間：(-∞, 2023-12-01]、(2023-12-01, 2023-12-12)、id=10

因為是非唯一索引，所以當找到第一條birthday = '2023-12-01'的記錄時，不確定後面還有沒有這樣的記錄，所以必須繼續往後找，直到遇到一條不是2023-12-01的記錄未止。

間隙鎖阻止其它事務插入，但是不阻止更新

情況二：範圍查找

Session A Session B Session C
BEGIN;
SELECT * FROM t_user WHERE birthday >= '2023-11-11' AND birthday <='2023-11-28' FOR UPDATE;		    
 

UPDATE t_user SET score = score + 1 WHERE birthday = '2023-11-29';

Affected rows: 0

  
   

INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (13,'華雄','1033','2023-11-29',1);

1205 - Lock wait timeout exceeded; try restarting transaction

主鍵索引上加鎖範圍：id=30和id=40

輔助索引idx_birthday上加鎖範圍：(2023-01-01, 2023-11-12]、(2023-11-12, 2023-11-28]、(2023-11-28, 2023-11-30]

2.4.  不走索引

Session A Session B Session C
BEGIN;
SELECT * FROM t_user WHERE score = 2 FOR UPDATE;		    
 

UPDATE t_user SET score = score + 1 WHERE id = 33;

Affected rows: 0

  
   

INSERT INTO t_user (id,`name`,id_card_no,birthday,score) VALUES (33,'顏良','1038','2023-12-20',1);

Lock wait timeout exceeded; try restarting transaction

在所有記錄的主鍵上加next-key鎖

加鎖範圍：(-∞, 10]、(10, 20]、(20, 30]、(30, 40]、(40, 50]、(50, +∞)

3.  總結

1、主鍵索引

  • 等值查詢，命中，則被命中的主鍵索引加記錄鎖
  • 等值查詢，未命中，則繼續向後（向右）查找，直到找到第一個不滿足的記錄，對該記錄加間隙鎖，即鎖住該記錄之前的間隙，以防止其它事務向其中插入數據
  • 範圍查找，找到的（滿足條件的）記錄的主鍵加記錄鎖，掃描過的區間加間隙鎖

2、非主鍵唯一索引

  • 與主鍵索引類似，唯一的區別是鎖住輔助索引記錄的同時會鎖住對應的主鍵索引

3、非唯一索引

  • 向右查找直到遇到一條不滿足條件的記錄，然後對掃描到的區間加間隙鎖，對掃描到的輔助索引記錄加記錄鎖，同時對與其對應的主鍵加記錄鎖

4、不走索引

  • 表中所有記錄的主鍵加next-key鎖

 

總結幾個規律：

  1. 命中的索引記錄會加記錄鎖，如果它是一個輔助索引，則對應的主鍵索引也會被加上記錄鎖
  2. 沒有命中的記錄不會被加記錄鎖
  3. 非唯一索引上查找時，當找到第一條滿足條件的索引記錄時，還會繼續向右查找，直到遇到一條不滿足條件的記錄（PS：幸虧索引是有序的，不然找到累死）
  4. 當一條SQL沒有走索引時，那麼將會在每一條聚集索引上加X鎖，這個類似於表鎖，但原理上和表鎖是完全不同的

建議：

  1. 儘量控制事務大小，減少鎖定資源量和時間長度
  2. 即便在條件中使用了索引欄位，但是否使用索引來檢索數據是由 MySQL 通過判斷不同執行計劃的代價來決定的。如果 MySQL 認為全表掃描效率更高，它就不會使用索引。因此，在分析鎖衝突時，可以查看執行計劃（explain）以確認是否真正使用了索引

最後，重要的事情說三遍：

  • 加鎖的單位是next-key鎖
  • 加鎖的單位是next-key鎖
  • 加鎖的單位是next-key鎖

 

參考

https://www.cnblogs.com/harda/p/16820592.html

https://blog.csdn.net/qq_42604176/article/details/115431744

https://zhuanlan.zhihu.com/p/378306056

https://cloud.tencent.com/developer/article/1971381

https://cloud.tencent.com/developer/article/1844928


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

-Advertisement-
Play Games
更多相關文章
  • 35. 乾貨系列從零用Rust編寫負載均衡及代理，代理伺服器的源碼升級改造
    代理在電腦網路很常見，比如伺服器群組內部通常只會開一個口進行對外訪問，就可以通過內網代理來進行處理，從而更好的保護內網伺服器。代理讓我們網路更安全，但是警惕非正規的代理可能會竊取您的數據。請用HTTPS內容訪問更安全。 ...
  • 第十三單元 流程式控制制和函數
    1. 選擇結構 If(...) Begin ​ 語句塊 ​ End ​ else if(...) Begin ​ 語句塊 ​ End ​ Else ​ Begin ​ 語句塊 ​ End; 註意事項 語法中begin..end相當於C#中的{} 執行語句只有一條時，begin..end可以省略 () ...
  • 第十二單元 `T-SQL` 編程
    create database step2_unit12; go use step2_unit12; go -- 部門表 CREATE TABLE [dbo].[Department]( [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL, [Name] [v ...
  • 第十一單元 事務與鎖
    create database step2_unit13; go use step2_unit13; go -- 創建數據表 CREATE TABLE account ( id INT PRIMARY KEY identity, NAME VARCHAR(10), balance decimal(1 ...
  • 精準掌握.NET依賴註入：DI自動註冊服務輕鬆搞定
    概述：.NET依賴註入（DI）通過反射自動註冊服務，示例展示了註冊指定類、帶特性類、項目下所有介面實現的類。簡化配置，提高可維護性。 在.NET中，進行依賴註入（DI）的自動註冊，可以通過反射機制和程式集掃描來實現。以下是詳細的步驟以及相應的C#源代碼示例，包括註冊指定類、註冊帶有自定義特性的類、以 ...
  • Qt程式接收串口數據存在延遲解決辦法
    問題 在調試接收串口數據的Qt程式中發現，數據存在延遲和粘包現象。下位機發送數據包頻率是100Hz，一包56位元組，波特率115200，在列印port->readAll()的值的時候發現並不是每10ms讀到一包數據，而是大概每50ms左右一次接收到5包數據，在其他電腦上調試，以及下載其他串口助手調試後 ...
  • Flink處理函數解析（ProcessFunction和KeyedProcessFunction）
    Flink中的處理函數（ProcessFunction和KeyedProcessFunction）在對於數據進行顆粒化的精確計算時使用較多，處理函數提供了一個定時服務（TimerService），可以向未來註冊一個定時服務， ...
  • MySQL運維13-Mycat分庫分表之按月分片
    一、按照月分片 使用場景為按照自然月來分片，每個自然月為一個分片，但是一年有12個月，是不是要有12個數據節點才行呢？並不是。例如我現在只有三個分片資料庫，這樣就可以1月在第一個數據分片中，2月在第二個數據分片中，3月在第三個數據分片中，當來到4月的時候，就會重新開始分片，4月在第一個數據分片，5月 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類