MySQL 鎖表問題排查與解決方案大全（含自動化殺進程腳本）

MySQL 鎖表問題排查與解決方案大全

高併發場景下，鎖表是 MySQL 常見故障。本文系統梳理從手動排查到自動化清理的完整方案，並提供可直接投產的 Shell 腳本。

核心排查命令

-- 查看所有線程狀態
SHOW PROCESSLIST;

-- 只看被鎖定的線程
SHOW PROCESSLIST WHERE State LIKE '%Locked%';

方案一：手動殺掉阻塞源頭

若鎖表進程較少，直接 kill 首個阻塞線程往往能解除連鎖：

KILL thread_id;

方案二：批量殺掉所有 Locked 狀態線程（Shell 腳本）

#!/bin/bash
# 輸出所有 Locked 線程 ID 到文件
mysql -u root -p"YOUR_PASSWORD" -e "SHOW PROCESSLIST" | grep -i "Locked" > locked_log.txt

# 生成批量 KILL 語句
awk '{print "KILL " $1 ";"}' locked_log.txt > kill_thread_id.sql

# 執行
mysql -u root -p"YOUR_PASSWORD" < kill_thread_id.sql

方案三：一條命令搞定（生產推薦）

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

方案四：基於 information_schema 精準生成 KILL 語句

#!/bin/bash
mysql_pwd="ROOT_PASSWORD"
mysql_exec="/usr/local/mysql/bin/mysql"
log_dir="/tmp"
kill_sh="$log_dir/mysql_kill_timeout.sh"
kill_log="$log_dir/mysql_kill_timeout.log"

# 1. 找出執行超過 30 秒且非 root 用戶的線程
$mysql_exec -uroot -p$mysql_pwd -e "SHOW PROCESSLIST" | \
awk '$1>30 && $3!="root" {print "'$mysql_exec' -uroot -p'$mysql_pwd' -e \"kill " $2 "\";"}' > $kill_sh

# 2. 記錄日誌
echo "check start ...." >> $kill_log
date >> $kill_log
cat $kill_sh >> $kill_log

# 3. 確認無誤後，將最後 cat 改為 sh 即可自動執行
# sh $kill_sh

部署建議：chmod u+rx mysqld_kill_sleep.sh，加入 root 用戶 crontab -e，每 5 分鐘執行一次：

*/5 * * * * /path/to/mysqld_kill_sleep.sh

進階優化腳本（僅針對 Locked 狀態）

#!/bin/bash
mysql_pwd="PASSWORD"
mysql_exec="/usr/local/mysql/bin/mysql"
log_file="/tmp/mysql_kill_locked.log"
sh_file="/tmp/mysql_kill_locked.sh"

$mysql_exec -uroot -p$mysql_pwd -e "SHOW PROCESSLIST" | grep -i "Locked" >> $log_file
chmod 777 $log_file

for id in $(awk '{print $1}' $log_file); do
  echo "$mysql_exec -uroot -p$mysql_pwd -e \"kill $id\"" >> $sh_file
done
chmod 777 $sh_file
# 確認後執行：sh $sh_file

預防鎖表的最佳實踐

• 縮短事務範圍：只在必要時開啟事務，盡早提交
• 索引優化：確保 WHERE/JOIN/ORDER BY 列有適當索引，減少掃描鎖行數
• 避免長事務：大批量操作分批提交，或使用 pt-online-schema-change 類工具
• 鎖等待超時設置：innodb_lock_wait_timeout=50（默認 50s），防止無限等待
• 死鎖檢測：開啟 innodb_deadlock_detect=ON（默認開啟），定期查看 SHOW ENGINE INNODB STATUS

相關延伸閱讀

• MySQL 鎖表與解鎖語法詳解
• LOAD DATA 導致死鎖案例分析
• MyISAM 讀寫互斥鎖問題解決
• MySQL 死鎖問題分析與解決實戰