MySQL 鎖機制全景解析：從共享/排他鎖到 MVCC、死鎖、間隙鎖與鎖升級

鎖的基本分類

共享鎖 (S) vs 排他鎖 (X)

• S 鎖可疊加 S 鎖，不可加 X 鎖；X 鎖不可疊加任何鎖。
• MySQL 根據隔離級別隱式加鎖；可顯式：SELECT ... LOCK IN SHARE MODE (S)、SELECT ... FOR UPDATE (X)。

鎖粒度

• 行鎖：InnoDB 預設，僅鎖定訪問行，並發高，開銷大。
• 表鎖：MyISAM/MEMORY/ALTER TABLE 等 DDL，鎖全表，並發低，開銷小。

意向鎖 (IS/IX)

表級鎖，標識事務「意圖」在表中某行加 S/X 鎖。解決「表鎖與行鎖衝突檢查」全表掃描問題：事務加行 X 鎖前先加表 IX，另一事務想加表 S 鎖只需檢查表級 IX 即知衝突，無需遍歷所有行。

悲觀鎖 vs 樂觀鎖

死鎖：成因、檢測、解決

經典案例

-- 事務 1                    -- 事務 2
UPDATE t SET v=1 WHERE id=1;  UPDATE t SET v=2 WHERE id=2;
SLEEP 1;                      SLEEP 2;
UPDATE t SET v=2 WHERE id=2;  UPDATE t SET v=1 WHERE id=1;
COMMIT;                       COMMIT;

若交替執行至 SLEEP，雙方各持一鎖等待對方 → 死鎖。

三大解決策略

1. 一次性鎖定所有所需資源（按固定順序）。
2. 全局統一加鎖順序（如按主鍵遞增）。
3. 允許死鎖，InnoDB 自動檢測並回滾代價最小事務（默認行為）。

間隙鎖

SELECT ... WHERE id BETWEEN 10 AND 20 FOR UPDATE 會鎖定 (10,20) 區間，防止幻讀（新插入 id=15）。RR 隔離級別下默認啟用。

MVCC 多版本並發控制

• 系統版本號遞增，每行隱藏 create_version、delete_version。
• INSERT：寫入當前版本號到 create_version。
• DELETE：寫入當前版本號到 delete_version（標記刪除）。
• UPDATE：新行寫 create_version，舊行寫 delete_version。
• SELECT：只讀 create_version ≤ 當前版本 AND (delete_version > 當前版本 OR delete_version IS NULL) 的行。

效果：不加鎖實現一致性非阻塞讀，解決髒讀、不可重複讀、幻讀（RR 下）。

常見 SQL 與鎖對應表

鎖兼容性驗證示例

-- 事務 1
SET autocommit=0; BEGIN;
SELECT * FROM biz_pay_task WHERE id=1 FOR UPDATE; -- 持有 X 鎖
-- 事務 2
SET autocommit=0; BEGIN;
SELECT * FROM biz_pay_task WHERE id=1 LOCK IN SHARE MODE; -- 阻塞，等事務 1 COMMIT

X 鎖與 S 鎖互斥，驗證排他性。