萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 表鎖詳解:LOCK TABLES / UNLOCK TABLES 語法、鎖類型、InnoDB 差異與死鎖排查

前言

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

讀鎖 (READ / READ LOCAL) 行為演示

會話 A:加讀鎖


mysql> LOCK TABLES t1 READ;
Query OK, 0 rows affected

會話 A:自身讀/寫


mysql> SELECT * FROM t1;  -- ✅ 成功
mysql> INSERT INTO t1 VALUES (1,'a');  -- ❌ ERROR 1099: Table 't1' was locked with a READ lock

會話 B:其他會話讀/寫


mysql> SELECT * FROM t1;  -- ✅ 成功(共享讀鎖)
mysql> INSERT INTO t1 VALUES (2,'b');  -- ⏳ 阻塞,等待讀鎖釋放

READ LOCAL 差異(僅 MyISAM 生效)


mysql> LOCK TABLES t1 READ LOCAL;
-- 允許其他會話在表尾**并发插入** (Concurrent Insert),不阻塞 INSERT
-- 但 UPDATE/DELETE 仍阻塞
  • InnoDB **不支持** READ LOCAL 並發插入,行鎖機制下所有寫操作均阻塞。

寫鎖 (WRITE) 行為演示

會話 A:加寫鎖


mysql> LOCK TABLES t1 WRITE;
Query OK, 0 rows affected

會話 A:自身讀/寫


mysql> SELECT * FROM t1;  -- ✅ 成功
mysql> INSERT INTO t1 VALUES (3,'c');  -- ✅ 成功

會話 B:其他會話讀/寫


mysql> SELECT * FROM t1;  -- ⏳ 阻塞
mysql> INSERT INTO t1 VALUES (4,'d');  -- ⏳ 阻塞

寫鎖**獨佔**表:持有者可讀寫,其他會話**讀寫均阻塞**。

UNLOCK TABLES 釋放所有鎖


mysql> UNLOCK TABLES;
Query OK, 0 rows affected
  • 會話結束(斷開連接)也會隱式釋放所有表鎖。
  • 事務提交/回滾**不會**釋放 LOCK TABLES 獲得的表鎖(需顯式 UNLOCK TABLES)。

InnoDB 特殊交互:MDL + 行鎖 + 表鎖

操作MDL 鎖InnoDB 行鎖LOCK TABLES 表鎖
SELECTMDL_SHARED_READ無 (MVCC)READ 兼容,WRITE 阻塞
INSERT/UPDATE/DELETEMDL_SHARED_WRITE行排他鎖 (X)READ/WRITE 均阻塞
LOCK TABLES READMDL_SHARED_READ (隱式)無共享讀,阻塞寫
LOCK TABLES WRITEMDL_SHARED_WRITE (隱式)無獨佔,阻塞所有
ALTER TABLEMDL_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;

最佳實踐與避坑指南

  1. 業務代碼禁用 LOCK TABLES:改用行級鎖 (SELECT ... FOR UPDATE)、樂觀鎖 (version 欄位)、分布式鎖 (Redis SETNX/Redisson)。
  2. 僅在維護窗口使用:全表導出、Schema 變更前鎖定、大批量載入 (配合 SET autocommit=0 批量提交)。
  3. 鎖表順序全局統一:按表名字典序或固定業務順序,避免死鎖。
  4. 設置鎖等待超時:SET SESSION lock_wait_timeout = 30; (默認 31536000 秒/1 年,極危險)。
  5. 監控告警:Table_locks_waited、Innodb_row_lock_waits、Innodb_deadlocks 計數器異常升高即告警。

總結

LOCK TABLES 是重錘工具:READ 共享讀阻塞寫、WRITE 獨佔阻塞讀寫、READ LOCAL 僅 MyISAM 允許尾部並發插入、InnoDB 不支持 LOCAL、UNLOCK TABLES 釋放所有、事務不自動釋表鎖**。生產業務**堅決不用**,維護窗口謹慎用、統一順序、設置超時、事後驗證。

copyright © 萬盛學電腦網 all rights reserved