您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 鎖機制深度解析：MDL、表鎖、行鎖、全局鎖的協作與衝突

編輯：MySQL 綜合教學

鎖體系全景

MySQL 鎖分層協作：SQL 層 (Server Layer) 管理 MDL、表鎖、全局鎖；存儲引擎層 (InnoDB) 管理行鎖、意向鎖、間隙鎖、Next-Key Lock。理解分層是分析阻塞、死鎖的前提。

一、元數據鎖 (MDL, Metadata Lock)

作用

保護表結構定義不被並發 DDL/DML 破壞，實現 DDL 隔離。

類型與兼容性

鎖類型	獲取場景	兼容	阻塞
MDL_SHARED_READ	SELECT、INSERT/UPDATE/DELETE (讀表結構)	共享讀、共寫	排他寫
MDL_SHARED_WRITE	DML (首次修改表數據)	共享讀	共寫、排他寫
MDL_SHARED_UPGRADABLE	ALTER TABLE ... ADD INDEX (Online DDL 準備階段)	共享讀	共寫、排他寫、升級
MDL_SHARED_NO_WRITE	FLUSH TABLES WITH READ LOCK	無	所有寫
MDL_SHARED_NO_READ_WRITE	TRUNCATE、RENAME、DROP TABLE	無	所有
MDL_EXCLUSIVE	CREATE TABLE、ALTER TABLE (非 Online)、DDL 執行階段	無	所有

關鍵特性

事務級持有：事務提交/回滾才釋放，非語句級
優先級：排他 > 升級 > 共寫 > 共讀，防止飢餓
可見性：performance_schema.metadata_locks (5.7+)
死鎖檢測：MDL 死鎖不進 InnoDB 死鎖檢測，由 lock_wait_timeout 控制

典型阻塞場景

-- Session 1: 長事務
BEGIN;
SELECT * FROM t WHERE id=1 FOR UPDATE;  -- 持有 MDL_SHARED_WRITE
-- 未提交

-- Session 2: DDL
ALTER TABLE t ADD COLUMN c INT;  -- 等待 MDL_EXCLUSIVE，阻塞

-- Session 3: 後續查詢
SELECT * FROM t;  -- 等待 MDL_SHARED_READ，被 Session 2 阻塞，雪崩

二、表級鎖 (Table Lock, SQL 層)

類型

LOCK TABLES t READ：共享表鎖，兼容讀，阻塞寫
LOCK TABLES t WRITE：排他表鎖，阻塞所有
隱式表鎖：LOCK TABLES 以外的 DDL、FLUSH TABLES WITH READ LOCK

與 MDL 關係

LOCK TABLES 同時獲取對應 MDL，釋放時同步釋放。InnoDB 內部行鎖不受 LOCK TABLES 影響，但 SQL 層會阻塞語句執行。

三、全局鎖 (Global Lock)

FLUSH TABLES WITH READ LOCK (FTWRL)
-- 獲取 MDL_SHARED_NO_READ_WRITE 於所有表
-- 關閉所有表，刷新到磁盤
-- 全庫只讀，阻塞所有寫入、DDL、甚至 SELECT (若涉及表結構變更)

-- 用途：邏輯備份 (mysqldump --master-data)、建立從庫快照
-- 釋放：UNLOCK TABLES 或會話斷開

四、InnoDB 行鎖體系

鎖模式

鎖類型	SQL	兼容性	備註
Record Lock (記錄鎖)	`SELECT ... FOR UPDATE`、`UPDATE ... WHERE PK=?`	互斥	鎖定索引記錄本身
Gap Lock (間隙鎖)	`SELECT ... FOR UPDATE` (RR 級)、`DELETE ... WHERE range`	兼容間隙鎖	鎖定索引記錄間隙，防止幻讀
Next-Key Lock (臨鍵鎖)	RR 級範圍查詢修改	互斥	Record + Gap，左開右閉區間
Insert Intention Lock (插入意向鎖)	`INSERT`	與 Gap 衝突	插入前獲取，多事務可同時插入同一間隙不同位置
Auto-inc Lock (自增鎖)	`INSERT` 自增列	表級互斥	可通過 `innodb_autoinc_lock_mode=2` 改為輕量級

意向鎖

IX (意向排他)：事務將加行排他鎖前，先獲取表級 IX
IS (意向共享)：事務將加行共享鎖前，先獲取表級 IS
目的：快速判斷表級是否已有行鎖，避免掃描所有行
兼容：IX/IS 互兼容，與表級 X/S 互斥

五、鎖升級與降級

MySQL 不支持鎖升級 (行鎖→表鎖)，InnoDB 始終維持行級
MDL 無升級，但有優先級隊列
表鎖可顯式轉換：LOCK TABLES t WRITE; UNLOCK TABLES; LOCK TABLES t READ;

六、死鎖檢測與處理

-- 開啟死鎖檢測 (默認 ON)
SET GLOBAL innodb_deadlock_detect = ON;

-- 查看最近死鎖
SHOW ENGINE INNODB STATUS\G
-- LATEST DETECTED DEADLOCK 區塊

-- 關鍵指標
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

常見死鎖模式

交叉更新：Session 1 更新 A→B，Session 2 更新 B→A
索引順序不一致：複合索引 (a,b) 但 WHERE 條件順序不同
Gap Lock 衝突：RR 級範圍 DELETE/UPDATE 產生重疊 Next-Key Lock
外鍵檢查：父表鎖記錄，子表鎖間隙，交叉等待

避免死鎖原則

業務邏輯統一按主鍵順序訪問表/行
拆分大事務，縮短持鎖時間
RC 隔離級減少 Gap Lock (需評估業務可接受性)
使用 SELECT ... FOR UPDATE SKIP LOCKED (8.0+) / NOWAIT 處理熱點併發

七、監控與診斷

-- 當前鎖等待
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 事務等待
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS; (5.7 已移除，用 data_locks)
SELECT * FROM information_schema.INNODB_LOCK_WAITS; (5.7 已移除)

-- 歷史死鎖
SHOW ENGINE INNODB STATUS\G

八、實戰排查案例

-- 現象：QPS 突降，Threads_running 飆升
-- 1. 看鎖等待
SELECT * FROM sys.schema_table_lock_waits;
-- 發現大量等待 MDL_SHARED_WRITE

-- 2. 定位長事務
SELECT * FROM information_schema.INNODB_TRX 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

-- 3. Kill 長事務
KILL 12345;

-- 4. 根因：應用代碼異常導致事務未提交，修復代碼、加超時保護

鎖是並發控制的基石，亦是性能殺手。掌握「分層、模式、兼容、生命周期、檢測」五維認知，配合 performance_schema 與 sys schema 可視化工具，才能在高併發海洋中穩健航行。