SQL 數(shù)據(jù)庫鎖超清晰總結(jié)

一、按鎖粒度分（最核心分類）

1. 行鎖（Row Lock）

• 鎖一行數(shù)據(jù)
• MySQL InnoDB 默認(rèn)
• 并發(fā)高、沖突小、容易出現(xiàn)死鎖
• 例：UPDATE 表 WHERE id=1 只鎖 id=1 這一行

2. 表鎖（Table Lock）

• 鎖整張表
• 并發(fā)極低，一鎖全表不能寫
• MyISAM 默認(rèn)、InnoDB 不加索引時會退化成表鎖
• 例：UPDATE 表 WHERE name='張三'（name 無索引 → 全表鎖）

3. 頁鎖（Page Lock）

• 鎖一頁數(shù)據(jù)（很少用，了解即可）
• 鎖定數(shù)據(jù)頁（一組相鄰記錄）。是行級和表級鎖的折中，主要用于 SQL Server 等數(shù)據(jù)庫。

4. 全局鎖

• 鎖定整個數(shù)據(jù)庫實例，讓庫變?yōu)橹蛔x狀態(tài)。典型命令是 MySQL 的 FLUSH TABLES WITH READ LOCK，常用于全庫備份。

二、按鎖功能分（面試高頻）

1. 共享鎖 / 讀鎖（Shared Lock，S鎖）

• 允許多個事務(wù)同時讀
• 但不能寫
• 行為：一個事務(wù)加了 S 鎖后，其他事務(wù)還能再加 S 鎖，但不能加排他鎖，直到 S 鎖釋放。
• 手動加鎖：

SELECT * FROM 表 LOCK IN SHARE MODE;

2. 排他鎖 / 寫鎖（Exclusive Lock，X鎖）

• 只有一個事務(wù)能寫
• 其他人既不能讀也不能寫
• 行為：一個事務(wù)加了 X 鎖后，其他事務(wù)無法再加任何鎖（S 或 X）
• UPDATE / DELETE / INSERT 自動加排他鎖
• 手動加鎖：

SELECT * FROM 表 FOR UPDATE;

3. 意向鎖

表級鎖，完全由系統(tǒng)自動管理，為了協(xié)調(diào)行級鎖和表級鎖。事務(wù)想給某行加鎖前，會先在表級加個意向鎖，聲明“我想做某類操作”。

• 意向共享鎖（IS）：事務(wù)想在某些行上加共享鎖。
• 意向排他鎖（IX）：事務(wù)想在某些行上加排他鎖。

這樣，當(dāng)有事務(wù)想鎖整張表時，只需檢查表的意向鎖，就知道有沒有行被鎖住，而不用一行行檢查。

意向鎖是和行鎖搭配工作的，我們以事務(wù) T1 執(zhí)行為例：

-- 事務(wù) T1
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;

它的內(nèi)部加鎖順序是：

1. 在 users 表上，申請并加上意向排他鎖（IX）。
2. 加表鎖成功后，再在 id = 1 的行記錄上，加上排他鎖（X）。

如果此時事務(wù) T2 想鎖定整張表：

-- 事務(wù) T2
LOCK TABLES users WRITE;

1. T2 的請求需要 users 表上的排他鎖。
2. 系統(tǒng)檢查發(fā)現(xiàn)，T1 已在表上持有 IX 鎖。
3. IX 和 X 鎖沖突，因此 T2 的請求會進入等待，直到 T1 提交或回滾。

4. 更新鎖

用于解決“先讀后寫”操作（如UPDATE的查找階段）中的死鎖問題。SQL Server 中常用，它允許共享讀，但只允許一個事務(wù)獲得更新鎖，并最終升級為排他鎖。
典型的 UPDATE 流程是兩步：先找到行，再修改。如果最初用共享鎖來“找行”，就埋下了死鎖隱患。
死鎖推演：

1. 事務(wù)A SELECT … FOR SHARE 找到數(shù)據(jù)，持有該行的共享鎖 (S)。
2. 事務(wù)B 也 SELECT … FOR SHARE 同一行，共享鎖兼容，也成功持有S鎖。
3. 事務(wù)A 執(zhí)行 UPDATE，想把自己的S鎖升級為排他鎖 (X)，但必須等事務(wù)B釋放S鎖。
4. 事務(wù)B 也執(zhí)行 UPDATE，同樣想升級為X鎖，但必須等事務(wù)A釋放S鎖。

雙方互相等待，死鎖就發(fā)生了。
更新鎖（U鎖）正是為了打破這個循環(huán)而設(shè)計的。

更新鎖的核心規(guī)則

它的工作規(guī)則很簡單：

1. 與共享鎖兼容：U鎖和S鎖可以共存，滿足最初的“讀”需求。
2. 與自身互斥：一個資源上只能有一個U鎖。這從源頭上阻止了多個事務(wù)同時持有U鎖并等待升級。
3. 可直接升級為排他鎖：U鎖持有者可以將其直接升級為X鎖，而無需先釋放。

1. 安全的“先讀后寫”

這是最經(jīng)典、也是最正確的用法。通過在 SELECT 階段就加上 (UPDLOCK)，確保后續(xù)更新安全無死鎖。

BEGIN TRANSACTION;
-- 查詢時立刻對該行加上更新鎖(U)
SELECT * FROM Products WITH (UPDLOCK) 
WHERE ProductID = 1;
-- 判斷邏輯，比如檢查庫存...
-- IF ...
-- 后續(xù)更新，此時U鎖將無縫升級為排他鎖(X)
UPDATE Products 
SET Stock = Stock - 1 
WHERE ProductID = 1;
COMMIT TRANSACTION;

此時，如果另一個事務(wù)也執(zhí)行同樣的 WITH (UPDLOCK) 查詢，它的U鎖請求會因為U鎖互斥而立刻等待，這就避免了之前共享鎖升級造成的死鎖環(huán)。

2. 避免丟失更新

直接用 WITH (UPDLOCK) 做讀取-修改-寫回，也是實現(xiàn)悲觀鎖、防止并發(fā)丟失更新的一種手段。

-- 事務(wù)A
BEGIN TRANSACTION;
-- 讀出當(dāng)前值并鎖定
SELECT @current_value = Balance FROM Accounts WITH (UPDLOCK) WHERE AccountID = 100;
-- 計算新值
SET @new_value = @current_value + 500;
-- 基于鎖的保護進行更新
UPDATE Accounts SET Balance = @new_value WHERE AccountID = 100;
COMMIT TRANSACTION;

3. 在可序列化隔離級別下，用更新鎖防止幻讀

在需要范圍查詢并可能后續(xù)插入的場景下，可將 UPDLOCK 和 SERIALIZABLE 結(jié)合使用。

BEGIN TRANSACTION;
-- 檢查訂單是否存在，對查詢范圍施加更新鎖和范圍鎖
IF NOT EXISTS (
    SELECT 1 FROM Orders WITH (UPDLOCK, SERIALIZABLE) 
    WHERE OrderDate = '2023-01-01' AND CustomerID = 5
)
BEGIN
    -- 如果不存在則插入
    INSERT INTO Orders (OrderDate, CustomerID) VALUES ('2023-01-01', 5);
END
COMMIT TRANSACTION;

與 SELECT … FOR UPDATE 的對比

• MySQL 的做法
  SELECT … FOR UPDATE 會直接加排他鎖 (X)。它在讀取階段就把數(shù)據(jù)強鎖住，雖然也避免了升級死鎖，但并發(fā)度會更低，因為X鎖與任何鎖都互斥。
• 更新鎖（U鎖）的優(yōu)勢
  它是一種更精細化的設(shè)計。在讀取階段，它允許共享鎖（S）共存，不影響純讀操作，同時通過自身的互斥性避免了死鎖?？梢哉f是并發(fā)度和安全性之間的一個更好平衡。

總之，在 SQL Server 中，通過 WITH (UPDLOCK) 這個提示，你就能顯式控制更新鎖，它是實現(xiàn)安全“先讀后寫”操作的關(guān)鍵工具。

5. 自增鎖

特指 MySQL 里 AUTO_INCREMENT 列的鎖，保證自增 ID 唯一。它有多種鎖模式（傳統(tǒng)、連續(xù)、交錯），可由 innodb_autoinc_lock_mode 參數(shù)控制。

自增鎖（AUTO-INC Lock）是一種特殊的表級鎖，專門用于保護 AUTO_INCREMENT 列的并發(fā)賦值。它和意向鎖一樣，完全由數(shù)據(jù)庫自動管理，無法通過 SQL 顯式調(diào)用。我們能控制的是它的行為模式。

核心目標(biāo)：保證主鍵唯一，而非連續(xù)

首先要明確，自增鎖的核心任務(wù)是保證自增 ID 的唯一性，并不保證連續(xù)性。出現(xiàn)回滾或沖突時，已分配的 ID 會被浪費，這是設(shè)計取舍。

三種工作模式

自增鎖的行為由 MySQL 參數(shù) innodb_autoinc_lock_mode 控制，有 0、1、2 三種。我們結(jié)合 INSERT 的幾種類型來看，它們的核心區(qū)別在于鎖的粒度和釋放時機。

• 簡單插入：能提前確定行數(shù)，如 INSERT INTO t VALUES (1,'a'), (2,'b')。
• 批量插入：不能提前確定行數(shù)，如 INSERT INTO t SELECT ... FROM s。
• 混合插入：如 INSERT INTO t (name) VALUES ('a'), (NULL, 'b')，部分值自增。

核心使用方法：配置與排查

日常開發(fā)中，你的“使用方法”主要是這三點：

1. 根據(jù)場景設(shè)置模式
在配置文件 my.cnf 中設(shè)置：

[mysqld]
# 使用默認(rèn)的連續(xù)模式，適合大多數(shù)場景
innodb_autoinc_lock_mode = 1
# 若主從復(fù)制用的是 STATEMENT 格式，可能需要更安全的傳統(tǒng)模式
# innodb_autoinc_lock_mode = 0
# 若全用 ROW 格式復(fù)制且追求極高插入并發(fā)，可考慮交錯模式
# innodb_autoinc_lock_mode = 2

2. 排查鎖等待
自增鎖在表級沖突，現(xiàn)象是大量 INSERT 卡在 “AUTO-INC lock waiting”。

-- 查看正在等待自增鎖的線程
SELECT * FROM performance_schema.metadata_locks 
WHERE OBJECT_TYPE = 'TABLE' AND LOCK_TYPE = 'AUTO-INC';

結(jié)合 SHOW ENGINE INNODB STATUS\G 就能看到哪個事務(wù)長時間持有自增鎖不釋放。

3. 優(yōu)化批量插入
在默認(rèn)模式 1 下，要避免讓簡單的單行插入，被大的、不確定行數(shù)的批量插入阻塞。

-- 這種插入行數(shù)不確定，會持有表級自增鎖直到結(jié)束，阻塞所有插入
INSERT INTO t (data) SELECT data FROM huge_table;
-- 如果業(yè)務(wù)允許，可手動分批提交，或考慮用程序先在外部取完數(shù)據(jù)再插入。

三種模式場景總結(jié)

• 0-傳統(tǒng)：老系統(tǒng)或主從復(fù)制仍用 STATEMENT 格式時，用于保證 ID 絕對連續(xù)。
• 1-連續(xù)（默認(rèn)）：線上系統(tǒng)首選。大事務(wù)可能引發(fā)鎖等待，需關(guān)注。
• 2-交錯：批量插入極大負(fù)載，且主從復(fù)制為 ROW 格式，且不關(guān)心 ID 連續(xù)性時使用。

切換到模式 2 前，要確保 replication 配置中 binlog_format 設(shè)置為 ROW，否則主從數(shù)據(jù)可能不一致。

三、按實現(xiàn)方式分

1. 樂觀鎖（Optimistic Lock）

與后面講的記錄鎖、間隙鎖等由數(shù)據(jù)庫內(nèi)核自動管理的悲觀鎖不同，樂觀鎖是一種應(yīng)用層級的并發(fā)控制策略。

它的核心思想是：假定沖突很少發(fā)生，操作時不加鎖，只在最終提交更新時檢查數(shù)據(jù)是否被修改過。 如果被改了，就回滾重試。

樂觀鎖的核心實現(xiàn)：版本號或時間戳

樂觀鎖不依賴 FOR UPDATE，而是在你的業(yè)務(wù)表里加一個字段，最常用的是 version。

1. 表結(jié)構(gòu)設(shè)計

在你的業(yè)務(wù)表中，必須有一個用于版本校驗的字段。

-- 常用的版本號字段
ALTER TABLE accounts ADD COLUMN version INT NOT NULL DEFAULT 0;
-- 或者用時間戳
ALTER TABLE accounts ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

2. 更新的標(biāo)準(zhǔn) SQL 寫法

邏輯是：讀出數(shù)據(jù)時帶出版本號，更新時在 WHERE 里校驗這個版本號，同時把版本號加 1。

-- 1. 查詢余額，同時獲取當(dāng)前版本號
SELECT balance, version FROM accounts WHERE account_id = 100;
-- 假設(shè)結(jié)果：balance = 500, version = 3
-- 2. 應(yīng)用程序計算新余額
-- new_balance = 500 - 100 = 400
-- 3. 執(zhí)行帶版本號校驗的更新
UPDATE accounts 
SET balance = 400, version = version + 1 
WHERE account_id = 100 AND version = 3;

核心判斷：更新后的受影響行數(shù)。

• Affected rows = 1：版本號匹配，更新成功。
• Affected rows = 0：說明在讀取后，有別的事務(wù)搶先更新了數(shù)據(jù)（version 已經(jīng)不是 3 了）。此時你的應(yīng)用程序應(yīng)該處理“更新失敗”的邏輯，通常是重試。

完整的使用模式（含重試）

將查詢、計算、更新、重試整合起來，就是樂觀鎖的標(biāo)準(zhǔn)實現(xiàn)。

# 偽代碼示例
max_retry = 3
retry_count = 0
while retry_count < max_retry:
    # 1. 查詢當(dāng)前數(shù)據(jù)及版本
    cursor.execute("SELECT balance, version FROM accounts WHERE account_id = 100")
    row = cursor.fetchone()
    if not row:
        print("賬戶不存在")
        break
    current_balance = row['balance']
    current_version = row['version']
    # 2. 業(yè)務(wù)邏輯計算
    new_balance = current_balance - 100
    # 3. 帶版本號條件的更新
    cursor.execute(
        "UPDATE accounts SET balance = %s, version = version + 1 "
        "WHERE account_id = %s AND version = %s",
        (new_balance, 100, current_version)
    )
    conn.commit()
    # 4. 檢查結(jié)果
    if cursor.rowcount > 0:
        print("更新成功！")
        break
    else:
        retry_count += 1
        print(f"版本沖突，正在進行第 {retry_count} 次重試...")

樂觀鎖的適用與不適用場景

最適合的場景：

• 讀多寫少：沖突概率低，重試開銷可忽略。
• 短事務(wù)：持有版本號的時間窗口短，產(chǎn)生沖突的幾率就小。
• 無法長時間持鎖的情況：如 Web 應(yīng)用的“先讀取-后修改”模式，用戶可能在編輯頁面停留很久，悲觀的長事務(wù)會嚴(yán)重阻塞他人。

不適合的場景：

• 寫并發(fā)極高：沖突變成常態(tài)，會導(dǎo)致大量重試，性能急劇下降。這種場景應(yīng)使用悲觀鎖。
• 需要強一致性的復(fù)雜操作：樂觀鎖模型較簡單，若涉及跨多表、多行的一致性狀態(tài)變更，實現(xiàn)起來會很復(fù)雜，且重試代價高。數(shù)據(jù)庫的悲觀鎖和事務(wù)是更好的選擇。

樂觀鎖與悲觀鎖的對比

需要我接著講講如何在后端應(yīng)用中，用 AOP 或攔截器封裝這個重試邏輯嗎？

2. 悲觀鎖（Pessimistic Lock）

與樂觀鎖不同，悲觀鎖的核心思想是：假定沖突一定會發(fā)生，所以在讀取數(shù)據(jù)的瞬間就將其鎖定，直到事務(wù)結(jié)束才釋放。

它由數(shù)據(jù)庫內(nèi)核實現(xiàn)，是之前聊過的行鎖、表鎖、臨鍵鎖等的具體應(yīng)用。

使用方式：顯式鎖定讀

悲觀鎖主要通過 SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE 實現(xiàn)。它們必須在事務(wù)中使用，否則讀完后鎖立即釋放，毫無意義。

標(biāo)準(zhǔn)使用流程

1. 經(jīng)典“鎖定-修改”

這是最常用的場景：先鎖住數(shù)據(jù)，確保其間無人更改，再做更新。

START TRANSACTION;
-- 1. 對即將操作的數(shù)據(jù)加排他鎖
SELECT stock FROM products WHERE id = 100 FOR UPDATE;
-- 假設(shè) stock = 50
-- 2. 在應(yīng)用層安全地做業(yè)務(wù)判斷
-- if stock < 10 → ROLLBACK
-- 3. 執(zhí)行更新
UPDATE products SET stock = stock - 10 WHERE id = 100;
COMMIT;

在事務(wù)提交前，任何其他想通過 SELECT ... FOR UPDATE 或修改這行的事務(wù)都會被阻塞。

2. 安全的“讀取-插入”

當(dāng)需要檢查后再決定是否插入時，悲觀鎖是防止競態(tài)最直接的方法。

START TRANSACTION;
-- 1. 嘗試鎖定還未存在的記錄。若記錄不存在，InnoDB會加間隙鎖/臨鍵鎖
SELECT * FROM unique_emails WHERE email = 'test@example.com' FOR UPDATE;
-- 2. 如果沒查到，則安全插入
INSERT INTO unique_emails (email, created_at) VALUES ('test@example.com', NOW());
COMMIT;

這個操作能保證，在檢查和插入之間，不會有其他事務(wù)插入相同的值。

悲觀鎖的完整特點

• 長事務(wù)風(fēng)險：從 SELECT ... FOR UPDATE 到 COMMIT，鎖定時間越長，數(shù)據(jù)庫并發(fā)性能下降越明顯。
• 規(guī)避死鎖：所有事務(wù)都按相同的順序訪問資源，是最有效的避免死鎖方式。比如統(tǒng)一先鎖主表，再鎖子表。
• 避免鎖范圍擴大：WHERE 條件要能精確命中索引，否則可能退化為全表掃描并鎖住大量行甚至整張表。
• 超時處理：被阻塞的事務(wù)不會永遠等待，可用 innodb_lock_wait_timeout 控制超時，避免雪崩。

  -- 超時后應(yīng)用會收到 Lock wait timeout exceeded 異常，需要處理
  SET innodb_lock_wait_timeout = 5;

決策參考：樂觀鎖 vs. 悲觀鎖

你可以根據(jù)這個對比來選擇：

常見的應(yīng)用場景

• 金融/庫存系統(tǒng)：扣款、減庫存，并發(fā)沖突高，必須用悲觀鎖保證絕對數(shù)據(jù)正確。
• 后臺批處理：多個批處理任務(wù)需更新同一批數(shù)據(jù)，悲觀鎖能清晰串行化任務(wù)。
• 高沖突配置表：只讀配置表會被多個事務(wù)讀，并用 FOR SHARE 來保證讀取期間配置不被修改。

悲觀鎖是把利劍，用好了能清晰可靠地解決并發(fā)問題，但前提是事務(wù)設(shè)計必須短小精悍，索引條件精準(zhǔn)。

四、按算法分（InnoDB 特有）

1. 記錄鎖（Record Lock）

鎖定索引中的一條精確記錄

WHERE id=1

記錄鎖（Record Lock）是 InnoDB 中最基礎(chǔ)的鎖之一，它直接鎖定索引中的一條具體記錄，用來防止其他事務(wù)修改或刪除這條數(shù)據(jù)。

和前面的意向鎖、自增鎖不同，記錄鎖是我們在日常寫 SQL 時最能直接控制和感受到的鎖。

核心概念：鎖的是索引記錄

一個關(guān)鍵點是：記錄鎖總是鎖定索引記錄，而不是直接鎖數(shù)據(jù)行。

InnoDB 的表是基于聚簇索引組織的，數(shù)據(jù)就存在葉子節(jié)點。所以，即使你 WHERE 條件里沒有用到索引列，它也會退化為掃全表，并在掃描到的每一行聚簇索引記錄上都加上鎖。

記錄鎖的精確定義

• 作用對象：鎖定一個索引中的單條記錄。
• 互斥關(guān)系：記錄鎖（無論是共享的 S 鎖，還是排他的 X 鎖）與任何其他試圖鎖定同一索引記錄的排他鎖都是沖突的。
• 觸發(fā)隔離級別：在讀已提交（Read Committed, RC） 隔離級別下，它是主要的加鎖方式，用于防止臟寫和丟失更新。

如何使用記錄鎖？

你通過特定的 SQL 語句，在特定的隔離級別下，就能精確觸發(fā)記錄鎖。

1. 顯式加鎖讀取

這是最精確的控制方式，通過在 SELECT 語句后加上 FOR UPDATE 或 FOR SHARE，可以鎖定查詢命中的索引記錄。

SELECT ... FOR UPDATE
對掃描到的索引記錄加上排他記錄鎖（X Lock）。這意味著在你的事務(wù)提交前，其他事務(wù)既不能給這些行加 X 鎖（不能修改或刪除），也不能加 S 鎖（不能執(zhí)行 SELECT ... FOR SHARE），但普通的非鎖定讀不受影響。

-- 事務(wù) A
START TRANSACTION;
-- 假設(shè) id 是主鍵。對 id=10 的主鍵索引記錄加上 X 鎖。
SELECT * FROM users WHERE id = 10 FOR UPDATE;
-- 在提交前，其他事務(wù)無法 UPDATE/DELETE 這行，也無法 SELECT ... FOR UPDATE/SHARE 這行

SELECT ... FOR SHARE（MySQL 8.0+，之前叫 LOCK IN SHARE MODE）
對掃描到的索引記錄加上共享記錄鎖（S Lock）。它允許其他事務(wù)也加 S 鎖，但會阻止加 X 鎖。

-- 事務(wù) A
START TRANSACTION;
-- 對 id=10 的主鍵索引記錄加上 S 鎖
SELECT * FROM users WHERE id = 10 FOR SHARE;
-- 其他事務(wù)可以繼續(xù)讀這行（加 S 鎖），但不能修改它（加 X 鎖會等待）

2. 隱式加鎖：寫操作

UPDATE 和 DELETE 語句會自動對被修改/刪除的索引記錄加上排他記錄鎖（X Lock）。

修改時

-- 事務(wù) A
UPDATE users SET name = 'new_name' WHERE id = 10;
-- 會自動對 id=10 的主鍵索引記錄加 X 鎖。

刪除時

-- 事務(wù) A
DELETE FROM users WHERE id = 10;
-- 同樣會對 id=10 的主鍵索引記錄加 X 鎖。

實踐要點：索引的影響

鎖是對索引記錄加的。如果 WHERE 條件沒有用到索引，就會導(dǎo)致鎖表。

假設(shè) users 表的 city 列沒有索引，你在 RC 隔離級別下執(zhí)行：

-- 事務(wù) A
START TRANSACTION;
DELETE FROM users WHERE city = 'Beijing';

1. 存儲引擎行為：優(yōu)化器會選擇全表掃描。它會掃描整個主鍵索引。
2. 加鎖過程：InnoDB 會對掃描到的所有主鍵索引記錄都加上 X 鎖。
3. 實際結(jié)果：即使某行的 city 不是 ‘Beijing’，在被掃描到時也會被鎖。效果上等同于鎖定了整張表，極大影響并發(fā)。
4. 優(yōu)化：只需給 city 列加上索引，DELETE 就能精確定位，只鎖住相關(guān)的索引記錄。

由上可知，在使用記錄鎖刪改查帶索引的列的一大好處就是避免全局鎖表，造成其他事務(wù)無法進行

隔離級別的關(guān)鍵影響

記錄鎖的行為嚴(yán)重依賴隔離級別：

• 在讀已提交（RC）下：這是記錄鎖工作的主要級別。UPDATE/DELETE 語句在找到匹配的索引記錄時加排他記錄鎖，完成一行就解鎖一行。SELECT ... FOR UPDATE 只對結(jié)果集加鎖，非常明確。
• 在可重復(fù)讀（RR）下：記錄鎖仍然存在，但通常會和間隙鎖合并成臨鍵鎖（Next-Key Lock），用來防止幻讀。此時，你 SELECT ... FOR UPDATE 的加鎖范圍會比 RC 大得多。

實踐總結(jié)

2. 間隙鎖（Gap Lock）

和記錄鎖鎖定具體記錄不同，間隙鎖鎖定的是索引記錄之間的間隙（一個開區(qū)間），用來防止其他事務(wù)在這個間隙里插入新記錄，是解決“幻讀”問題的核心武器。

間隙鎖同樣由 InnoDB 自動施加，你不能顯式地“加一個間隙鎖”，但可以通過隔離級別和特定的查詢條件來觸發(fā)它。

核心規(guī)則：只在可重復(fù)讀（RR）下生效

這是最重要的一點。間隙鎖只在隔離級別設(shè)置為 REPEATABLE READ 時才工作。如果你用的是 READ COMMITTED，即便執(zhí)行同樣的 SELECT ... FOR UPDATE，InnoDB 也只會加記錄鎖，不會加間隙鎖。

間隙鎖的精確行為

• 鎖定范圍：鎖定索引記錄之間的開區(qū)間。比如有 id 為 5 和 10 兩條記錄，間隙鎖會鎖定 (5, 10) 這個區(qū)間。
• 排他性：間隙鎖之間不沖突。多個事務(wù)可以同時持有同一個間隙的間隙鎖。它唯一的目的是阻止插入操作。
• 組合形態(tài)：通常以臨鍵鎖的形式出現(xiàn)，即“記錄鎖 + 間隙鎖”，鎖定一個左開右閉區(qū)間，如 (5, 10]。

如何“使用”間隙鎖？

你不能直接寫 LOCK GAP，但可以通過以下方式精確觸發(fā)。

1. 通過鎖定不存在的記錄觸發(fā)

當(dāng)你查詢一條不存在的記錄并加上鎖，就會產(chǎn)生間隙鎖，鎖住該記錄本應(yīng)落入的間隙。

-- 會話 A（隔離級別為 RR）
BEGIN;
-- 表中只有 id=5 和 id=10 兩條記錄
-- 嘗試鎖定 id=7（不存在），會觸發(fā)間隙鎖，鎖住 (5, 10) 這個區(qū)間
SELECT * FROM users WHERE id = 7 FOR UPDATE;
-- 會話 B
BEGIN;
-- 會被阻塞，因為 id=6 落在被鎖住的 (5, 10) 區(qū)間內(nèi)
INSERT INTO users (id, name) VALUES (6, 'blocked');

2. 通過范圍查詢的邊界觸發(fā)

當(dāng)范圍查詢 WHERE id > X 的終點“正無窮”沒有記錄時，也會觸發(fā)間隙鎖。

-- 會話 A
BEGIN;
-- 表中最大 id=10，那么 id>10 的區(qū)間是 (10, +∞)
-- 執(zhí)行此查詢會鎖住 (10, +∞) 這個間隙
SELECT * FROM users WHERE id > 10 FOR UPDATE;
-- 會話 B
-- 會被阻塞，因為 id=15 落在 (10, +∞) 內(nèi)
INSERT INTO users (id, name) VALUES (15, 'blocked');

3. 通過組合觸發(fā)“臨鍵鎖”

更常見的情況是，你鎖定了存在的行，InnoDB 會默認(rèn)加上臨鍵鎖，其間的間隙部分就是由間隙鎖提供的。

-- 會話 A
BEGIN;
-- 表中存在 id=10，查詢 id <= 10 的兩條記錄（假設(shè) 5 和 10）
-- 可能會對 (5, 10] 和 (10, +∞) 加鎖，其中 (5,10) 和 (10,+∞) 都是間隙鎖
SELECT * FROM users WHERE id <= 10 FOR UPDATE;

線上排查與觀察

在執(zhí)行 SELECT ... FOR UPDATE 前后，可以通過 performance_schema 觀察鎖情況：

-- 查看當(dāng)前事務(wù)持有的鎖
SELECT 
    lock_type, lock_mode, lock_status, lock_data
FROM 
    performance_schema.data_locks
WHERE 
    engine_transaction_id = (SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = CONNECTION_ID());

你會在 lock_mode 列中看到 X,GAP（純粹間隙鎖）或 X（臨鍵鎖，包含了間隙和記錄），lock_data 則顯示鎖定的邊界值。

一個常見的影響與應(yīng)對

間隙鎖的主要副作用是導(dǎo)致并發(fā)插入性能下降，容易引發(fā)死鎖。比如兩個事務(wù)互相持有對方要插入位置的間隙鎖，然后都試圖插入數(shù)據(jù)，就會產(chǎn)生死鎖。

應(yīng)對思路：

• 評估業(yè)務(wù)：如果業(yè)務(wù)不需要防止幻讀，可以考慮將隔離級別降為 READ COMMITTED。
• 優(yōu)化索引：盡量使用唯一索引的等值查詢。如果 WHERE 條件是唯一索引且命中，InnoDB 會將其降級為記錄鎖，而不會加間隙鎖。
• 調(diào)整邏輯順序：在批量操作時，確保所有事務(wù)以相同的順序訪問記錄，可以有效減少死鎖。

間隙鎖是 InnoDB 在 RR 級別下保證數(shù)據(jù)一致性的基石，理解它的觸發(fā)條件對排查死鎖和性能抖動至關(guān)重要。需要我繼續(xù)講它最常組合的“臨鍵鎖”嗎？
鎖范圍之間的空隙，防止幻讀.比如有記錄 1 和 10，間隙鎖會鎖住 (1,10)，防止插入 id=5 的行。只在可重復(fù)讀隔離級別下生效。

WHERE id BETWEEN 1 AND 10

3. 臨鍵鎖（Next-Key Lock）

臨鍵鎖是間隙鎖和記錄鎖的組合，也是 InnoDB 在可重復(fù)讀（RR）隔離級別下，用來防止幻讀的核心默認(rèn)鎖。

和間隙鎖一樣，你無法直接“加一個臨鍵鎖”，但當(dāng)你執(zhí)行 SELECT ... FOR UPDATE 這類鎖定讀時，它就會被自動觸發(fā)。

為什么需要臨鍵鎖？

記錄鎖只能保護存在的行，間隙鎖只能保護未來的行。如果只用一個，都無法徹底防幻讀：

• 僅記錄鎖：鎖住了 id=10，但別人可以在 id=9 的位置插入新行。
• 僅間隙鎖：鎖住了 (5,10) 的區(qū)間，但別人可以刪除或修改 id=5 本身。

臨鍵鎖通過**“當(dāng)前記錄 + 它之前的間隙”**的組合，完美覆蓋了這兩種情況。

臨鍵鎖的精確定義

• 鎖定范圍：一個左開右閉的區(qū)間，即 (起始值, 當(dāng)前記錄值]。
• 組成：針對索引記錄的記錄鎖 + 鎖定該記錄前一個區(qū)間的間隙鎖。
• 效果：既鎖定了當(dāng)前記錄不被修改/刪除，又防止了在該記錄前插入新記錄。

默認(rèn)加鎖規(guī)則：所有區(qū)間都是臨鍵鎖

在 RR 級別下，你執(zhí)行一個范圍查詢的 SELECT ... FOR UPDATE，InnoDB 的默認(rèn)行為就是給掃描到的所有區(qū)間加上臨鍵鎖。

假設(shè)表中有 id 索引，記錄值為 5, 10, 15，可能的臨鍵鎖區(qū)間如下：

• 負(fù)無窮到 5：(-∞, 5]
• 5 到 10：(5, 10]
• 10 到 15：(10, 15]
• 15 到正無窮：(15, +∞]

如何“使用”（觸發(fā)）臨鍵鎖？

你通過查詢的范圍和條件，精確控制它加哪些區(qū)間的臨鍵鎖。

1. 范圍查詢觸發(fā)

這是最標(biāo)準(zhǔn)的觸發(fā)方式。

-- 假設(shè)表中有 id=5, 10, 15 三行
-- 會話 A
BEGIN;
-- 范圍掃描，會鎖住命中的所有臨鍵鎖區(qū)間
SELECT * FROM users WHERE id BETWEEN 8 AND 12 FOR UPDATE;

此時，id=10 被命中，鎖會加到 (5, 10] 和 (10, 15] 這兩個區(qū)間。效果是：

• 不能修改或刪除 id=10。
• 不能在 (5, 10) 或 (10, 15) 之間插入新記錄（如 7, 12）。
• 試圖插入 id=6 的操作會被阻塞。

2. 等值查詢觸發(fā)（不存在時降級為間隙鎖）

如果等值查詢命中了記錄，加的也是臨鍵鎖；如果沒命中，則退化為間隙鎖。

-- 會話 A：鎖定存在的記錄 id=10
SELECT * FROM users WHERE id = 10 FOR UPDATE;
-- 會加臨鍵鎖 (5, 10]，鎖住 id=10 的記錄，以及它前面的間隙 (5,10)
-- 會話 A：鎖定不存在的記錄 id=7
SELECT * FROM users WHERE id = 7 FOR UPDATE;
-- 記錄不存在，退化為間隙鎖 (5, 10)，不鎖任何記錄。

3. 唯一索引等值查詢的降級

這是最重要的優(yōu)化。當(dāng)查詢條件是唯一索引，且精確命中一條記錄時，InnoDB 會認(rèn)為不再需要間隙鎖來防幻讀，臨鍵鎖會自動降級為單純的記錄鎖。

-- 假設(shè) id 是主鍵
-- 會話 A
BEGIN;
-- 唯一索引 + 等值命中，只加記錄鎖，鎖住 id=10
SELECT * FROM users WHERE id = 10 FOR UPDATE;
-- 會話 B（不會被阻塞）
INSERT INTO users (id, name) VALUES (9, 'ok');
-- 因為 (5,10) 的間隙鎖被省略了，插入 id=9 是允許的。

實踐中的直接影響與排查

臨鍵鎖的“左開右閉”設(shè)計，是很多鎖沖突的根源。

一個經(jīng)典死鎖場景：
兩個事務(wù)分別鎖定對方的間隙并等待插入。

1. 事務(wù) A：SELECT * FROM users WHERE id = 10 FOR UPDATE; 持有 (5, 10] 臨鍵鎖。
2. 事務(wù) B：SELECT * FROM users WHERE id = 15 FOR UPDATE; 持有 (10, 15] 臨鍵鎖。
3. 事務(wù) A：INSERT INTO users (id) VALUES (12); 想要 (10, 15) 的插入意向鎖，被事務(wù) B 的臨鍵鎖阻塞。
4. 事務(wù) B：INSERT INTO users (id) VALUES (7); 想要 (5, 10) 的插入意向鎖，被事務(wù) A 的臨鍵鎖阻塞。

排查時，在 SHOW ENGINE INNODB STATUS 的輸出中，你會看到 lock_mode X 后面缺少 GAP 字樣，通常表示臨鍵鎖。 它直接鎖定了記錄本身。

臨鍵鎖是 RR 隔離級別默認(rèn)行為，也是我們?nèi)粘戞i定讀時真正打交道的鎖。理解它的觸發(fā)和降級條件，是寫好高并發(fā) SQL 和排查死鎖的基礎(chǔ)。

五、最常出現(xiàn)的面試/工作問題總結(jié)

1. 什么情況下行鎖會變表鎖？

• 沒有索引
• 使用了函數(shù)
• 模糊查詢 LIKE '%xxx%'
• 事務(wù)太大

2. UPDATE 不加 WHERE 會加什么鎖？

表鎖！
全表鎖定，嚴(yán)重阻塞業(yè)務(wù)。

3. 共享鎖和排他鎖的關(guān)系？

• 讀鎖 + 讀鎖 = 共存
• 讀鎖 + 寫鎖 = 互斥
• 寫鎖 + 寫鎖 = 互斥

4. 樂觀鎖和悲觀鎖怎么選？

• 讀多寫少 → 樂觀鎖
• 寫多、并發(fā)高 → 悲觀鎖

總結(jié)

• 行鎖：鎖一行，InnoDB 默認(rèn)
• 表鎖：鎖全表，無索引會觸發(fā)
• 共享鎖：多人可讀
• 排他鎖：一人可寫
• 樂觀鎖：版本號控制
• 悲觀鎖：直接上鎖

到此這篇關(guān)于SQL 數(shù)據(jù)庫鎖總結(jié)的文章就介紹到這了,更多相關(guān)sql 數(shù)據(jù)庫鎖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論