LOCK TABLES / UNLOCK TABLES 是 MySQL 服務層(Server Layer)的**顯式表鎖**,與存儲引擎層(InnoDB 行鎖、MDL 元數據鎖)協同又獨立。本文詳細演示讀/寫鎖行為、LOCAL 選項、InnoDB 交互差異、死鎖場景與排查工具。
LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ...
lock_type: READ [LOCAL] | WRITE | LOW_PRIORITY WRITE
UNLOCK TABLES
mysql> LOCK TABLES t1 READ;
Query OK, 0 rows affected
mysql> SELECT * FROM t1; -- ✅ 成功
mysql> INSERT INTO t1 VALUES (1,'a'); -- ❌ ERROR 1099: Table 't1' was locked with a READ lock
mysql> SELECT * FROM t1; -- ✅ 成功(共享讀鎖)
mysql> INSERT INTO t1 VALUES (2,'b'); -- ⏳ 阻塞,等待讀鎖釋放
mysql> LOCK TABLES t1 READ LOCAL;
-- 允許其他會話在表尾**并发插入** (Concurrent Insert),不阻塞 INSERT
-- 但 UPDATE/DELETE 仍阻塞
READ LOCAL 並發插入,行鎖機制下所有寫操作均阻塞。
mysql> LOCK TABLES t1 WRITE;
Query OK, 0 rows affected
mysql> SELECT * FROM t1; -- ✅ 成功
mysql> INSERT INTO t1 VALUES (3,'c'); -- ✅ 成功
mysql> SELECT * FROM t1; -- ⏳ 阻塞
mysql> INSERT INTO t1 VALUES (4,'d'); -- ⏳ 阻塞
寫鎖**獨佔**表:持有者可讀寫,其他會話**讀寫均阻塞**。
mysql> UNLOCK TABLES;
Query OK, 0 rows affected
LOCK TABLES 獲得的表鎖(需顯式 UNLOCK TABLES)。| 操作 | MDL 鎖 | InnoDB 行鎖 | LOCK TABLES 表鎖 |
|---|---|---|---|
| SELECT | MDL_SHARED_READ | 無 (MVCC) | READ 兼容,WRITE 阻塞 |
| INSERT/UPDATE/DELETE | MDL_SHARED_WRITE | 行排他鎖 (X) | READ/WRITE 均阻塞 |
| LOCK TABLES READ | MDL_SHARED_READ (隱式) | 無 | 共享讀,阻塞寫 |
| LOCK TABLES WRITE | MDL_SHARED_WRITE (隱式) | 無 | 獨佔,阻塞所有 |
| ALTER TABLE | MDL_EXCLUSIVE | 表級 X 鎖 (準備階段) | 需等待所有表鎖釋放 |
關鍵點:LOCK TABLES 會隱式獲取對應級別的 MDL 鎖,阻塞 DDL;InnoDB 行鎖與服務層表鎖**疊加**,死鎖風險增加。
-- 會話 A
LOCK TABLES t1 WRITE, t2 WRITE;
-- 會話 B
LOCK TABLES t2 WRITE, t1 WRITE; -- 死鎖
MySQL 會檢測並回滾其中一個事務:ERROR 1213 (HY000): Deadlock found when trying to get lock; try restarting transaction。
-- 1. 查看當前鎖等待 (5.7+ performance_schema)
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
-- 2. 傳統情報
SHOW ENGINE INNODB STATUS\G -- LATEST DETECTED DEADLOCK 區塊
SHOW PROCESSLIST; -- State: Locked / Waiting for table lock
-- 3. 系統視圖 (8.0+)
SELECT * FROM sys.schema_table_lock_waits;
SELECT * FROM sys.innodb_lock_waits;
SELECT ... FOR UPDATE)、樂觀鎖 (version 欄位)、分布式鎖 (Redis SETNX/Redisson)。SET autocommit=0 批量提交)。SET SESSION lock_wait_timeout = 30; (默認 31536000 秒/1 年,極危險)。Table_locks_waited、Innodb_row_lock_waits、Innodb_deadlocks 計數器異常升高即告警。LOCK TABLES 是重錘工具:READ 共享讀阻塞寫、WRITE 獨佔阻塞讀寫、READ LOCAL 僅 MyISAM 允許尾部並發插入、InnoDB 不支持 LOCAL、UNLOCK TABLES 釋放所有、事務不自動釋表鎖**。生產業務**堅決不用**,維護窗口謹慎用、統一順序、設置超時、事後驗證。