在 SQL 數(shù)據(jù)庫(kù)中，鎖（Locking）是用來(lái)管理對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的并發(fā)訪(fǎng)問(wèn)的一種機(jī)制。鎖的主要目的是保證數(shù)據(jù)的一致性和完整性，防止多個(gè)用戶(hù)或進(jìn)程同時(shí)修改同一數(shù)據(jù)集導(dǎo)致數(shù)據(jù)沖突。不同類(lèi)型的數(shù)據(jù)庫(kù)（如 MySQL、PostgreSQL、SQL Server、Oracle 等）支持不同類(lèi)型的鎖機(jī)制，每種都有其特定的實(shí)現(xiàn)方式和用途。下面是一些常見(jiàn)數(shù)據(jù)庫(kù)鎖的概述和它們的基本用途：

數(shù)據(jù)庫(kù)鎖的概述

1. 共享鎖（Shared Locks）

• ‌定義‌：允許多個(gè)事務(wù)讀取同一資源，但在事務(wù)結(jié)束前不允許進(jìn)行寫(xiě)操作。
• ‌用途‌：適用于讀密集型操作，例如在讀取數(shù)據(jù)時(shí)不希望被修改。

2. 排他鎖（Exclusive Locks）

• ‌定義‌：只允許一個(gè)事務(wù)對(duì)數(shù)據(jù)進(jìn)行讀取和寫(xiě)入。
• ‌用途‌：適用于寫(xiě)操作，確保數(shù)據(jù)在事務(wù)處理期間不被其他事務(wù)修改或讀取。

3. 意向鎖（Intention Locks）

• ‌定義‌：意向鎖是表級(jí)鎖，表明某個(gè)事務(wù)想要在表的某行上加共享鎖或排他鎖。
• ‌用途‌：意向鎖用于多粒度鎖定，幫助數(shù)據(jù)庫(kù)管理系統(tǒng)決定是否可以授予更細(xì)粒度的鎖。

4. 行級(jí)鎖（Row-Level Locks）

• ‌定義‌：鎖定數(shù)據(jù)庫(kù)表中的特定行。
• ‌用途‌：提高并發(fā)性能，減少鎖定的資源范圍，適用于高并發(fā)的更新操作。

5. 表級(jí)鎖（Table-Level Locks）

• ‌定義‌：鎖定整個(gè)表。
• ‌用途‌：適用于對(duì)整個(gè)表進(jìn)行操作的情況，例如批量更新或刪除操作。

6. 頁(yè)面鎖（Page Locks）

• ‌定義‌：鎖定數(shù)據(jù)庫(kù)表中的頁(yè)（通常是 8KB 或更大）。
• ‌用途‌：介于行級(jí)鎖和表級(jí)鎖之間，可以提供比行級(jí)鎖更好的并發(fā)性能，同時(shí)比表級(jí)鎖更細(xì)粒度。

7. 死鎖（Deadlocks）

• ‌定義‌：兩個(gè)或多個(gè)事務(wù)相互等待對(duì)方釋放資源，導(dǎo)致都無(wú)法繼續(xù)執(zhí)行。
• ‌解決方案‌：數(shù)據(jù)庫(kù)管理系統(tǒng)通常有死鎖檢測(cè)機(jī)制，并可以自動(dòng)檢測(cè)和解決死鎖，例如通過(guò)選擇犧牲一個(gè)事務(wù)來(lái)回滾來(lái)解除死鎖。

8. 樂(lè)觀鎖（Optimistic Locking）

• ‌定義‌：通過(guò)版本號(hào)或時(shí)間戳來(lái)管理并發(fā)，只在提交時(shí)檢查是否有沖突。
• ‌用途‌：適用于讀多寫(xiě)少的場(chǎng)景，通過(guò)減少鎖定來(lái)提高性能。

9. 悲觀鎖（Pessimistic Locking）

• ‌定義‌：在事務(wù)開(kāi)始時(shí)就假設(shè)會(huì)發(fā)生沖突，因此立即鎖定所需資源。
• ‌用途‌：適用于寫(xiě)多讀少的場(chǎng)景，確保數(shù)據(jù)在事務(wù)處理期間不被其他事務(wù)修改。

管理和優(yōu)化鎖的建議：

• ‌合理設(shè)計(jì)事務(wù)大小‌：盡量減少事務(wù)的持續(xù)時(shí)間，避免長(zhǎng)時(shí)間占用資源。
• ‌使用合適的事務(wù)隔離級(jí)別‌：根據(jù)應(yīng)用需求選擇合適的事務(wù)隔離級(jí)別（如 READ COMMITTED, REPEATABLE READ, SERIALIZABLE）。
• ‌索引優(yōu)化‌：合理使用索引可以減少鎖定的范圍，提高查詢(xún)效率。
• ‌監(jiān)控和日志‌：監(jiān)控?cái)?shù)據(jù)庫(kù)的鎖情況，分析熱點(diǎn)和瓶頸，通過(guò)日志診斷性能問(wèn)題。
• ‌避免大事務(wù)中的小鎖定‌：盡量在一次事務(wù)中處理所有相關(guān)操作，減少不必要的中間鎖定。

每種數(shù)據(jù)庫(kù)管理系統(tǒng)都有其特定的實(shí)現(xiàn)細(xì)節(jié)和最佳實(shí)踐，了解和合理使用這些機(jī)制對(duì)于數(shù)據(jù)庫(kù)性能和穩(wěn)定性至關(guān)重要。

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

一、按鎖粒度分（最核心分類(lèi)）

1. 行鎖（Row Lock）

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

2. 表鎖（Table Lock）

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

3. 頁(yè)鎖（Page Lock）

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

4. 全局鎖

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

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

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

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

SELECT * FROM 表 LOCK IN SHARE MODE;

2. 排他鎖 / 寫(xiě)鎖（Exclusive Lock，X鎖）

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

SELECT * FROM 表 FOR UPDATE;

3. 意向鎖

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

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

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

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

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

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

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

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

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

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

4. 更新鎖

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

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

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

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

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

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

1. 安全的“先讀后寫(xiě)”

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

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

此時(shí)，如果另一個(gè)事務(wù)也執(zhí)行同樣的 WITH (UPDLOCK) 查詢(xún)，它的U鎖請(qǐng)求會(huì)因?yàn)閁鎖互斥而立刻等待，這就避免了之前共享鎖升級(jí)造成的死鎖環(huán)。

2. 避免丟失更新

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

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

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

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

BEGIN TRANSACTION;
-- 檢查訂單是否存在，對(duì)查詢(xún)范圍施加更新鎖和范圍鎖
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 的對(duì)比

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

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

5. 自增鎖

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

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

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

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

三種工作模式

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. 樂(lè)觀鎖（Optimistic Lock）

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

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

樂(lè)觀鎖的核心實(shí)現(xiàn)：版本號(hào)或時(shí)間戳

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

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

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

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

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

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

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

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

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

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

將查詢(xún)、計(jì)算、更新、重試整合起來(lái)，就是樂(lè)觀鎖的標(biāo)準(zhǔn)實(shí)現(xiàn)。

# 偽代碼示例
max_retry = 3
retry_count = 0
while retry_count < max_retry:
    # 1. 查詢(xún)當(dāng)前數(shù)據(jù)及版本
    cursor.execute("SELECT balance, version FROM accounts WHERE account_id = 100")
    row = cursor.fetchone()
    if not row:
        print("賬戶(hù)不存在")
        break
    current_balance = row['balance']
    current_version = row['version']
    # 2. 業(yè)務(wù)邏輯計(jì)算
    new_balance = current_balance - 100
    # 3. 帶版本號(hào)條件的更新
    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"版本沖突，正在進(jìn)行第 {retry_count} 次重試...")

樂(lè)觀鎖的適用與不適用場(chǎng)景

最適合的場(chǎng)景：

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

不適合的場(chǎng)景：

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

樂(lè)觀鎖與悲觀鎖的對(duì)比

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

2. 悲觀鎖（Pessimistic Lock）

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

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

使用方式：顯式鎖定讀

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

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

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

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

START TRANSACTION;
-- 1. 對(duì)即將操作的數(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ù)提交前，任何其他想通過(guò) SELECT ... FOR UPDATE 或修改這行的事務(wù)都會(huì)被阻塞。

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

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

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

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

悲觀鎖的完整特點(diǎn)

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

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

決策參考：樂(lè)觀鎖 vs. 悲觀鎖

你可以根據(jù)這個(gè)對(duì)比來(lái)選擇：

常見(jiàn)的應(yīng)用場(chǎng)景

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

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

四、按算法分（InnoDB 特有）

1. 記錄鎖（Record Lock）

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

WHERE id=1

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

和前面的意向鎖、自增鎖不同，記錄鎖是我們?cè)谌粘?xiě) SQL 時(shí)最能直接控制和感受到的鎖。

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

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

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

記錄鎖的精確定義

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

如何使用記錄鎖？

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

1. 顯式加鎖讀取

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

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

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

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

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

2. 隱式加鎖：寫(xiě)操作

UPDATE 和 DELETE 語(yǔ)句會(huì)自動(dòng)對(duì)被修改/刪除的索引記錄加上排他記錄鎖（X Lock）。

修改時(shí)

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

刪除時(shí)

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

實(shí)踐要點(diǎn)：索引的影響

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

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

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

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

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

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

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

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

實(shí)踐總結(jié)

2. 間隙鎖（Gap Lock）

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

間隙鎖同樣由 InnoDB 自動(dòng)施加，你不能顯式地“加一個(gè)間隙鎖”，但可以通過(guò)隔離級(jí)別和特定的查詢(xún)條件來(lái)觸發(fā)它。

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

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

間隙鎖的精確行為

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

如何“使用”間隙鎖？

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

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

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

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

2. 通過(guò)范圍查詢(xún)的邊界觸發(fā)

當(dāng)范圍查詢(xún) WHERE id > X 的終點(diǎn)“正無(wú)窮”沒(méi)有記錄時(shí)，也會(huì)觸發(fā)間隙鎖。

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

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

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

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

線(xiàn)上排查與觀察

在執(zhí)行 SELECT ... FOR UPDATE 前后，可以通過(guò) 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());

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

一個(gè)常見(jiàn)的影響與應(yīng)對(duì)

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

應(yīng)對(duì)思路：

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

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

WHERE id BETWEEN 1 AND 10

3. 臨鍵鎖（Next-Key Lock）

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

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

為什么需要臨鍵鎖？

記錄鎖只能保護(hù)存在的行，間隙鎖只能保護(hù)未來(lái)的行。如果只用一個(gè)，都無(wú)法徹底防幻讀：

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

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

臨鍵鎖的精確定義

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

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

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

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

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

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

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

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

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

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

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

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

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

如果等值查詢(xún)命中了記錄，加的也是臨鍵鎖；如果沒(méi)命中，則退化為間隙鎖。

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

3. 唯一索引等值查詢(xún)的降級(jí)

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

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

實(shí)踐中的直接影響與排查

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

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

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 的臨鍵鎖阻塞。

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

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

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

1. 什么情況下行鎖會(huì)變表鎖？

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

2. UPDATE 不加 WHERE 會(huì)加什么鎖？

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

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

• 讀鎖 + 讀鎖 = 共存
• 讀鎖 + 寫(xiě)鎖 = 互斥
• 寫(xiě)鎖 + 寫(xiě)鎖 = 互斥

4. 樂(lè)觀鎖和悲觀鎖怎么選？

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

總結(jié)

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

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

最新評(píng)論