萬盛學電腦網

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

MySQL 查詢正在被鎖定的表與鎖統計監控

一條命令查看當前被鎖定的表

SHOW OPEN TABLES WHERE In_use > 0;

結果解釋:

  • In_use > 0 表示該表當前有線程持有鎖(正在被讀/寫鎖定)。
  • Name_locked = 1 表示表名被鎖定(如正在 RENAME/DROP)。

全局表鎖統計監控

SHOW GLOBAL STATUS LIKE 'table_locks%';

輸出示例:

+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Table_locks_immediate | 490206328 |
| Table_locks_waited    | 2084912   |
+-----------------------+-----------+

指標含義

變量含義健康判斷
Table_locks_immediate立即獲得表鎖的次數(無等待)數值大為正常,表示多數鎖無競爭
Table_locks_waited等待表鎖的次數(被阻塞)關鍵指標。若佔比 > 1% 或持續增長,說明存在嚴重鎖競爭

鎖等待率計算

SELECT 
  Table_locks_waited / (Table_locks_immediate + Table_locks_waited) * 100 AS wait_pct
FROM (
  SELECT 
    VARIABLE_VALUE AS Table_locks_immediate 
  FROM performance_schema.global_status 
  WHERE VARIABLE_NAME = 'Table_locks_immediate'
) t1
CROSS JOIN (
  SELECT 
    VARIABLE_VALUE AS Table_locks_waited 
  FROM performance_schema.global_status 
  WHERE VARIABLE_NAME = 'Table_locks_waited'
) t2;

進階鎖分析(InnoDB 行鎖)

表鎖主要針對 MyISAM/MEMORY/ddl 操作。InnoDB 預設行鎖,需查詢 performance_schema 或 INFORMATION_SCHEMA.INNODB_TRX/LOCKS/LOCK_WAITS(5.7+)或 sys.schema 視圖:

-- 查看正在等待鎖的事務 (MySQL 5.7+)
SELECT * FROM sys.innodb_lock_waits;

-- 或原始表
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G

常見鎖爭用優化方向

  • 縮短事務持有時間:拆分大事務,避免在事務中進行網絡調用/複雜計算。
  • 索引優化:無索引的 UPDATE/DELETE 會升級為表鎖/全表掃描鎖定大量行。
  • 調整隔離級別:READ COMMITTED 縮小間隙鎖範圍,減少死鎖概率。
  • 應用層重試:死鎖 (Error 1213) 捕獲後指數退避重試。
  • 分區/分表:物理隔離熱點數據,降低鎖衝突概率。

總結

SHOW OPEN TABLES WHERE In_use > 0 快速定位表級鎖持有者;Table_locks_waited 監控表鎖競爭趨勢。InnoDB 行鎖分析請使用 sys.innodb_lock_waits 或 performance_schema。鎖優化核心是縮短關鍵區長度、精準索引、合理隔離級別。

copyright © 萬盛學電腦網 all rights reserved