【MySQL】SHOW PROCESSLISTで実行中クエリとロック待ちを確認する

MySQLで実行中のクエリを確認するにはSHOW PROCESSLISTを使う。
ロック待ちの詳細を調べるにはinformation_schema.innodb_trxやsys.innodb_lock_waitsとあわせて確認する。

参考: 【PostgreSQL】pg_stat_activityで実行中クエリとロック待ちを確認する

SHOW PROCESSLISTで実行中のクエリを確認する

SHOW PROCESSLISTを実行すると、サーバーに接続している全セッションの一覧が表示される。

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 4
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 10
   User: root
   Host: localhost
     db: testdb
Command: Query
   Time: 0
  State: init
   Info: SHOW PROCESSLIST

主要なカラムは以下の通り。

Id: 接続ID（コネクションID）
User: 接続ユーザー名
db: 接続先のデータベース
Command: セッションの種別（Query、Sleep、Daemonなど）
Time: 現在の状態が続いている秒数
State: 現在の処理状態
Info: 実行中、または直近に実行されたSQL文

権限がない一般ユーザーで実行すると自分のセッションしか表示されないが、PROCESS権限を持つユーザーであれば全セッションを確認できる。

SHOW PROCESSLISTだけではロック待ちが分かりにくい

行ロックの取得待ちで止まっているセッションがあっても、SHOW PROCESSLISTのStateはupdatingのままで、ロック待ちかどうかを見分けにくい。

以下は、主キーidとbalanceカラムを持つtestdb.accountsテーブルに対して、一方のセッションがロックを保持したまま、もう一方が同じ行の更新を試みた状態である。

CREATE TABLE accounts (id INT PRIMARY KEY, balance INT);
INSERT INTO accounts VALUES (1, 1000), (2, 2000);

-- セッションA（コミットせず保持）
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- セッションB（セッションAの行ロック待ちになる）
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 11
   User: root
   Host: localhost
     db: testdb
Command: Query
   Time: 5
  State: User sleep
   Info: SELECT SLEEP(30)
*************************** 2. row ***************************
     Id: 13
   User: root
   Host: localhost
     db: testdb
Command: Query
   Time: 1
  State: updating
   Info: UPDATE accounts SET balance = balance + 100 WHERE id = 1

セッションB（Id 13）はStateがupdatingのままで、Timeだけがロック取得待ちの間ずっと増え続ける。
Timeが長いのにStateが更新系のまま変わらないセッションは、ロック待ちを疑う手がかりになる。

innodb_trxでロック待ちのトランザクションを確認する

information_schema.innodb_trxを使うと、実行中のInnoDBトランザクションの状態を確認できる。
trx_stateがLOCK WAITのトランザクションが、ロック待ちで止まっている。

mysql> SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
    -> FROM information_schema.innodb_trx\G
*************************** 1. row ***************************
             trx_id: 1820
          trx_state: LOCK WAIT
        trx_started: 2026-08-22 07:14:41
trx_mysql_thread_id: 13
          trx_query: UPDATE accounts SET balance = balance + 100 WHERE id = 1
*************************** 2. row ***************************
             trx_id: 1819
          trx_state: RUNNING
        trx_started: 2026-08-22 07:14:37
trx_mysql_thread_id: 11
          trx_query: SELECT SLEEP(30)

trx_mysql_thread_idがSHOW PROCESSLISTのIdと対応するため、ロック待ちのセッションを特定できる。
ただし、この時点ではまだ「何にブロックされているか」までは分からない。

sys.innodb_lock_waitsでブロックしているセッションを特定する

sys.innodb_lock_waitsビューを使うと、ロック待ちのセッションとブロックしている側のセッションを1回のクエリで対応付けられる。

mysql> SELECT wait_age_secs, locked_table, waiting_pid, waiting_query, blocking_pid, blocking_query
    -> FROM sys.innodb_lock_waits\G
*************************** 1. row ***************************
 wait_age_secs: 1
  locked_table: `testdb`.`accounts`
   waiting_pid: 13
 waiting_query: UPDATE accounts SET balance = balance + 100 WHERE id = 1
  blocking_pid: 11
blocking_query: SELECT SLEEP(30)

waiting_pid・blocking_pidにはそのままSHOW PROCESSLISTのIdが入っているため、ブロックしている側のセッション（blocking_pid）をSHOW PROCESSLISTやKILLで直接扱える。
PostgreSQLのpg_blocking_pids関数に近い役割を、MySQLではsysスキーマのビューが担っている。

sys.innodb_lock_waitsは内部でperformance_schema.data_lock_waitsとperformance_schema.threadsをJOINしてPROCESSLIST_IDに変換している。
data_lock_waitsのrequesting_thread_id・blocking_thread_idはperformance_schema.threadsのTHREAD_IDであり、SHOW PROCESSLISTのId（PROCESSLIST_ID）とは別の採番であるため、自前でJOINする場合はTHREAD_IDからPROCESSLIST_IDへの変換を忘れずに行う。

SELECT
  wt.PROCESSLIST_ID AS waiting_pid,
  wt.PROCESSLIST_INFO AS waiting_query,
  bt.PROCESSLIST_ID AS blocking_pid,
  bt.PROCESSLIST_INFO AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN performance_schema.threads wt ON w.requesting_thread_id = wt.THREAD_ID
JOIN performance_schema.threads bt ON w.blocking_thread_id = bt.THREAD_ID;

data_locksでロックの詳細を確認する

performance_schema.data_locksを使うと、各トランザクションが保持・待機しているロックの種類まで確認できる。

mysql> SELECT ENGINE_TRANSACTION_ID, THREAD_ID, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
    -> FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE_TRANSACTION_ID: 1819
            THREAD_ID: 51
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE_TRANSACTION_ID: 1820
            THREAD_ID: 53
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 3. row ***************************
ENGINE_TRANSACTION_ID: 1819
            THREAD_ID: 51
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
*************************** 4. row ***************************
ENGINE_TRANSACTION_ID: 1820
            THREAD_ID: 53
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: WAITING
            LOCK_DATA: 1

セッションA（THREAD_ID 51）はaccountsテーブルへの意図的排他ロック（LOCK_TYPEがTABLE、LOCK_MODEがIX）と、idが1の行への排他ロック（LOCK_TYPEがRECORD）をGRANTED（取得済み）の状態で保持している。
セッションB（THREAD_ID 53）は同じ行に対する排他ロックをWAITING（取得待ち）の状態で要求しており、両者のLOCK_DATAが一致することから、idが1の行を巡って競合していると分かる。

MySQL 5.7以前では同様の情報をinformation_schema.innodb_locks・innodb_lock_waitsから取得していたが、8.0でperformance_schema.data_locks・data_lock_waitsに置き換わった。