MySQL查詢進行中程序狀況，並刪掉lock程序

2018-05-17 273 words 2 minutes

Contents

在使用 phpMyAdmin 執行複雜查詢時，如果 SQL 寫得不夠好（如缺少索引、長時間 Transaction），可能造成 Lock 等待，導致 phpMyAdmin 頁面卡住無法中斷。此時可透過 MySQL CLI 或其他工具查看並終止問題程序。

登入 MySQL CLI

1

mysql -h <ip> -u <username> -p

輸入密碼後進入 MySQL 互動模式，每條指令必須以 ; 結尾才會送出。

查看進行中的程序

SHOW PROCESSLIST

1
2
3


SHOW PROCESSLIST;
-- 或查看完整 SQL（不截斷）
SHOW FULL PROCESSLIST;

輸出欄位說明：

欄位	說明
Id	程序 ID
User	執行的使用者
Host	連線來源
db	目前使用的資料庫
Command	指令類型（Query、Sleep、…）
Time	已執行秒數
State	目前狀態（關鍵欄位）
Info	正在執行的 SQL

INFORMATION_SCHEMA.PROCESSLIST（推薦）

與 SHOW PROCESSLIST 資料相同，但可用 SQL 語法篩選，更靈活：

1
2
3
4


SELECT id, user, host, db, command, time, state, info
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC;

State 欄位常見狀態

State	說明
`executing`	正在執行 SQL
`Waiting for table metadata lock`	等待資料表的 Metadata Lock（DDL 操作造成）
`Waiting for this lock to be granted`	等待 InnoDB Row Lock
`Locked`	被 MyISAM Table Lock 鎖住
`Sending data`	正在傳送查詢結果
`Sorting result`	正在排序
`Sleep`	閒置中的連線

終止問題程序

找到卡住的程序 Id 後，用 KILL 終止：

1
2
3
4
5


-- 終止指定 Id 的程序
KILL 1234;

-- 只終止查詢，保留連線
KILL QUERY 1234;

查看 InnoDB Lock 詳情

如果懷疑是 InnoDB Row Lock 造成阻塞，可執行：

1

SHOW ENGINE INNODB STATUS\G

重點看 TRANSACTIONS 區段，會顯示：

哪些 Transaction 持有 Lock
哪些 Transaction 在等待 Lock
Lock 的詳細資訊（資料表、Row 位置）

MySQL 5.7+ 還可以查詢 performance_schema：

1
2
3
4
5
6
7
8
9


-- 查看 Lock 等待關係
SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

預防 Lock 的方法

控制 Transaction 範圍：避免 Transaction 持有過久，儘早 COMMIT 或 ROLLBACK
建立適當索引：WHERE 條件缺少索引時，InnoDB 可能鎖更多行（甚至全表掃描）
避免長時間查詢：複雜報表查詢建議用 Replica（從庫）執行，不佔用主庫資源
設定 Lock Timeout：

1
2


-- 設定 InnoDB Lock 等待逾時（秒）
SET innodb_lock_wait_timeout = 10;

使用 SELECT ... FOR UPDATE 時要謹慎：會鎖定查詢到的所有行，確保後續操作能快速完成

參考資料

MySQL 常用指令 - Jex’s Note