MySQL 鎖表問題急救手冊：從單條 KILL 到自動化清理腳本

現象識別

SHOW PROCESSLIST;
-- 關注 State 欄位出現 "Locked" 或 Time 值異常大的線程

方案一：手動逐條 KILL（少量鎖表）

KILL 線程ID;

方案二：批次腳本清理（大量鎖表）

Shell 版

#!/bin/bash
mysql -uroot -p'密碼' -e "SHOW PROCESSLIST" | grep -i "Locked" | awk '{print $1}' > /tmp/locked_ids.txt

while read id; do
  mysql -uroot -p'密碼' -e "KILL $id;"
  echo "Killed $id"
done < /tmp/locked_ids.txt

單行指令版

for id in $(mysqladmin processlist | grep -i locked | awk '{print $1}'); do mysqladmin kill $id; done

方案三：INFORMATION_SCHEMA 導出 KILL 語句（可審計）

SELECT CONCAT('KILL ', ID, ';')
FROM information_schema.PROCESSLIST
WHERE USER = 'root' AND STATE LIKE '%LOCKED%'
INTO OUTFILE '/tmp/kill_locked.sql';

SOURCE /tmp/kill_locked.sql;

方案四：生產環境自動化巡檢腳本（定時任務）

#!/bin/bash
# 部署於從庫或監控機，每分鐘執行一次
THRESHOLD=30  # 超過 30 秒視為異常
MYSQL="/usr/local/mysql/bin/mysql -uroot -p'密碼' -sN"

$MYSQL -e "SHOW PROCESSLIST" | awk -v t=$THRESHOLD '$6 > t && $3 != "root" {print $1}' | while read pid; do
  $MYSQL -e "KILL $pid;"
  echo "$(date) Killed long-running pid: $pid" >> /var/log/mysql_kill_long.log
done

根因排查與預防（治本）

監控建議

• Prometheus + mysqld_exporter 監控 mysql_global_status_threads_connected、mysql_innodb_row_lock_waits
• Grafana 告警：Running threads > 閾值、Lock wait count 突增
• 慢查詢日誌 + pt-query-digest 定期分析