【MySQL】ロックを確認する方法｜SHOW PROCESSLIST・InnoDBトランザクション・待機SQLの調査

MySQLで更新SQLが終わらない、画面が待ち続ける、DDLが止まる、Lock wait timeout exceeded が出る。このようなときは、まず「待たされているSQL」と「止めているトランザクション」を分けて確認します。

この記事では、MySQLのロック確認を実務で使う順番で整理します。SHOW FULL PROCESSLISTで全体を見て、INFORMATION_SCHEMA.INNODB_TRXで長いトランザクションを確認し、performance_schema.data_locksとdata_lock_waitsで待機元とブロック元を追います。最後に、KILL QUERYやKILL CONNECTIONを使う前の注意点もまとめます。

MySQLのロック確認で見るもの

ロック調査では、最初からテーブル名だけを探すより、次の4点を順に確認すると原因へ近づきやすくなります。

単に「ロックがあるか」だけでなく、待っている側と持っている側を分けて見るのが大事です。SQL全般のロック確認の考え方は、SQLでロックを確認する方法でも整理しています。

コピペ用: まず実行するロック確認SQL

急ぎで状況を見たい場合は、次の順番で確認します。最初に全体の詰まりを見て、次に未コミットのトランザクション、最後に待機関係を確認します。

-- 1. いま動いているSQLと待機状態を見る
SHOW FULL PROCESSLIST;

-- 2. 長時間残っているInnoDBトランザクションを見る
SELECT
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_seconds,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

-- 3. ロック待ちの関係を見る
SELECT
    REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
    REQUESTING_THREAD_ID AS waiting_thread_id,
    BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
    BLOCKING_THREAD_ID AS blocking_thread_id
FROM performance_schema.data_lock_waits;

-- 4. 直近のデッドロック情報を見る
SHOW ENGINE INNODB STATUS\G

data_lock_waitsに行が出る場合は、待機しているトランザクションとブロックしているトランザクションがあります。行が出ない場合でも、未コミットのトランザクション、メタデータロック、単純な重いSQLで待っている可能性があるため、PROCESSLISTとINNODB_TRXをあわせて確認します。

確認に必要な権限とバージョンの注意

本番環境では、ロック確認SQLが権限不足で実行できないことがあります。MySQL公式ドキュメントでは、プロセスリストで他ユーザーのスレッドを見るにはPROCESS権限が必要とされています。また、SHOW ENGINEもPROCESS権限を要求します。

権限が足りない場合は、DBAまたは管理者アカウントで確認します。本番障害時に慌てないよう、事前にどのアカウントでどこまで見られるかを確認しておくと安心です。

まずSHOW FULL PROCESSLISTで止まっているSQLを見る

障害対応で最初に見るなら、SHOW FULL PROCESSLISTが手早いです。MySQL公式ドキュメントでは、プロセスリストから接続ID、ユーザー、実行状態、経過秒数、実行中SQLを確認できると説明されています。SHOW PROCESSLISTではSQL本文が途中で切れるため、実務ではFULL付きで確認します。

SHOW FULL PROCESSLIST;

アプリケーションの接続が多い環境では、次のようにinformation_schema.processlistを使うと、長時間実行中のものを並べ替えて見られます。

SELECT
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO
FROM information_schema.PROCESSLIST
WHERE COMMAND <> 'Sleep'
ORDER BY TIME DESC;

ただし、プロセスリストだけでは「誰がロックを持っているか」までは分からないことがあります。次にInnoDBのトランザクションを確認します。

INNODB_TRXで長いトランザクションを確認する

InnoDBで未コミットのトランザクションが残っていると、更新対象の行や範囲をロックしたままになり、別のSQLが待たされることがあります。INFORMATION_SCHEMA.INNODB_TRXでは、トランザクションID、状態、開始時刻、実行中SQLなどを確認できます。

SELECT
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_seconds,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

trx_mysql_thread_idは、プロセスリストのIdと対応します。長時間残っているトランザクションがあれば、次のように接続情報と突き合わせます。

SELECT
    t.trx_id,
    t.trx_state,
    t.trx_started,
    TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_seconds,
    p.ID,
    p.USER,
    p.HOST,
    p.DB,
    p.COMMAND,
    p.TIME,
    p.STATE,
    p.INFO
FROM information_schema.INNODB_TRX AS t
LEFT JOIN information_schema.PROCESSLIST AS p
    ON p.ID = t.trx_mysql_thread_id
ORDER BY t.trx_started;

COMMANDがSleepでも、トランザクションが未コミットならロックを持っていることがあります。アプリ側でBEGIN後にCOMMITまたはROLLBACKしていない、例外時に接続を返してしまった、といった原因を疑います。

data_locksで保持中・待機中のロックを見る

MySQL 8.0以降では、performance_schema.data_locksでInnoDBのデータロックを確認できます。公式ドキュメントでも、data_locksは保持中または要求中のデータロックを示す表として説明されています。

SELECT
    ENGINE,
    ENGINE_TRANSACTION_ID,
    THREAD_ID,
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
FROM performance_schema.data_locks
ORDER BY OBJECT_SCHEMA, OBJECT_NAME, THREAD_ID;

テーブルを絞るなら、次のように対象スキーマとテーブル名を指定します。

SELECT
    ENGINE_TRANSACTION_ID,
    THREAD_ID,
    INDEX_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = 'app_db'
  AND OBJECT_NAME = 'orders'
ORDER BY THREAD_ID, LOCK_STATUS;

LOCK_STATUS = 'WAITING'があれば待機中のロックです。ただ、調査で本当に知りたいのは「その待機を誰が止めているか」です。その関係はdata_lock_waitsで確認します。

data_lock_waitsで待機SQLとブロック元を特定する

performance_schema.data_lock_waitsは、待機しているロック要求と、それをブロックしている保持中ロックの関係を表します。MySQL公式ドキュメントでも、どのセッションやトランザクションがロックを待ち、どれが保持しているかを理解するために使えると説明されています。

SELECT
    REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
    REQUESTING_THREAD_ID AS waiting_thread_id,
    BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
    BLOCKING_THREAD_ID AS blocking_thread_id
FROM performance_schema.data_lock_waits;

待機側とブロック側のSQLまで一緒に見たい場合は、data_locks、INNODB_TRX、threadsを結合します。

SELECT
    r.trx_mysql_thread_id AS waiting_process_id,
    r.trx_started AS waiting_trx_started,
    r.trx_query AS waiting_query,
    b.trx_mysql_thread_id AS blocking_process_id,
    b.trx_started AS blocking_trx_started,
    b.trx_query AS blocking_query,
    dlw.REQUESTING_ENGINE_LOCK_ID,
    dlw.BLOCKING_ENGINE_LOCK_ID
FROM performance_schema.data_lock_waits AS dlw
JOIN information_schema.INNODB_TRX AS r
    ON r.trx_id = dlw.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.INNODB_TRX AS b
    ON b.trx_id = dlw.BLOCKING_ENGINE_TRANSACTION_ID
ORDER BY r.trx_started;

blocking_queryがNULLになることもあります。その場合でも、blocking_process_idをSHOW FULL PROCESSLISTやinformation_schema.PROCESSLISTへ渡して、接続元、ユーザー、状態を確認します。

SELECT
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO
FROM information_schema.PROCESSLIST
WHERE ID IN (123, 456);

ロックの基本的な種類を整理したい場合は、SQLのテーブルロックと行ロックの違いもあわせて見ると理解しやすいです。

SHOW ENGINE INNODB STATUSで直近のデッドロックを見る

ロック待ちではなくデッドロックが起きている場合は、SHOW ENGINE INNODB STATUSのLATEST DETECTED DEADLOCKを確認します。MySQL公式ドキュメントでは、直近のInnoDBデッドロックを見る方法としてSHOW ENGINE INNODB STATUSが案内されています。

SHOW ENGINE INNODB STATUS\G

出力は長いので、まず次の見出しを探します。

デッドロックは、発生した瞬間にInnoDBが片方をロールバックするため、通常のロック待ちのように長く残らないことがあります。何度も起きる場合は、更新順序をそろえる、トランザクションを短くする、UPDATE ... WHEREやSELECT ... FOR UPDATEで使う列に適切なインデックスを用意する、といった見直しが必要です。

sys.innodb_lock_waitsが使えるなら簡単に見る

環境によっては、sys.innodb_lock_waitsビューでロック待ちを見られます。列名が見やすく整えられているため、障害対応の入り口として便利です。

SELECT
    waiting_pid,
    waiting_query,
    blocking_pid,
    blocking_query,
    locked_table,
    locked_index,
    wait_age
FROM sys.innodb_lock_waits
ORDER BY wait_age DESC;

ただし、すべての環境でsysスキーマやビューが使えるとは限りません。使えない場合は、前の章のperformance_schema.data_lock_waitsを使って同じ関係を追います。

ALTER TABLEが止まるときはmetadata_locksを見る

UPDATEやSELECT ... FOR UPDATEだけでなく、ALTER TABLE、DROP TABLE、CREATE INDEXなどのDDLが待たされることもあります。この場合は行ロックではなく、メタデータロックを疑います。

SELECT
    ml.OBJECT_TYPE,
    ml.OBJECT_SCHEMA,
    ml.OBJECT_NAME,
    ml.LOCK_TYPE,
    ml.LOCK_DURATION,
    ml.LOCK_STATUS,
    t.PROCESSLIST_ID,
    t.PROCESSLIST_USER,
    t.PROCESSLIST_HOST,
    t.PROCESSLIST_TIME,
    t.PROCESSLIST_STATE,
    t.PROCESSLIST_INFO
FROM performance_schema.metadata_locks AS ml
LEFT JOIN performance_schema.threads AS t
    ON t.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.OBJECT_SCHEMA = 'app_db'
  AND ml.OBJECT_NAME = 'orders'
ORDER BY ml.LOCK_STATUS, t.PROCESSLIST_TIME DESC;

LOCK_STATUSがPENDINGなら待機中です。長いSELECTや未コミットトランザクションが同じテーブルを参照していると、DDLが進まないことがあります。本番でDDLを実行するときは、ロングトランザクションやバッチ処理の時間帯も確認しましょう。

ロック待ちのよくある原因

ロックが発生しているSQLが分かったら、次は原因を分類します。現場では、次のパターンが多いです。

SQLが遅いためにロック保持時間が伸びている場合は、インデックスが使われない原因の観点も役立ちます。ロック問題は、SQLの実行時間とトランザクション設計の両方から見るのが近道です。

KILLする前に確認すること

ブロック元の接続IDが分かると、すぐにKILLしたくなります。しかし、本番ではロールバック時間、業務処理の途中終了、再実行時の重複処理などが問題になります。解除前に最低限、次を確認してください。

クエリだけを止めるならKILL QUERY、接続ごと切るならKILL CONNECTIONまたはKILLを使います。MySQL公式ドキュメントでは、KILL [CONNECTION | QUERY] processlist_idの形で接続や実行中ステートメントを終了できると説明されています。

-- 実行中のSQLだけを止める
KILL QUERY 123;

-- 接続ごと終了する。KILL 123 と同じ意味
KILL CONNECTION 123;

迷う場合は、まずアプリ担当者やDBAとブロック元を確認し、業務的に止めてよい接続かを判断します。SQL Serverの場合のデッドロック調査はSQL Serverのデッドロック確認で別途整理しています。

ロック待ちを減らす設計のコツ

ロック待ちは、発生後に調査するだけでなく、日常的なSQLとトランザクション設計で減らせます。

innodb_lock_wait_timeoutを短くすれば待ち時間は減りますが、根本原因が消えるわけではありません。タイムアウト値は、失敗時のリトライ設計やユーザー体験とセットで考えます。

よくある質問

SHOW PROCESSLISTだけでロック元は分かりますか？

分かることもありますが、確実ではありません。止まっているSQLや長時間接続の候補は見えますが、待機側とブロック側の関係はperformance_schema.data_lock_waitsやsys.innodb_lock_waitsで確認するほうが正確です。

Sleepの接続は無視してよいですか？

無視できません。Sleepでも未コミットのトランザクションが残っていれば、ロックを保持していることがあります。INNODB_TRXと突き合わせて確認します。

Lock wait timeout exceededが出たらどうしますか？

まず、待たされたSQLだけでなく、待たせているSQLを探します。data_lock_waits、INNODB_TRX、プロセスリストを使い、未コミット、インデックス不足、長いバッチ、DDL待ちのどれに近いかを切り分けます。

参考

Accessing the Process List – MySQL 8.4 Reference Manual

The INFORMATION_SCHEMA INNODB_TRX Table – MySQL 8.4 Reference Manual

The data_locks Table – MySQL 8.4 Reference Manual

The data_lock_waits Table – MySQL 8.4 Reference Manual

The metadata_locks Table – MySQL 8.4 Reference Manual

SHOW ENGINE Statement – MySQL 8.4 Reference Manual

Deadlocks in InnoDB – MySQL 8.4 Reference Manual

KILL Statement – MySQL 8.4 Reference Manual

まとめ

MySQLでロックを確認するときは、SHOW FULL PROCESSLISTで全体を見て、INNODB_TRXで長いトランザクションを確認し、data_locksとdata_lock_waitsで待機元とブロック元を特定します。

ロック解除は最後の手段です。ブロック元の接続IDだけを見て止めるのではなく、SQL、接続元、未コミット、ロールバック影響、再実行方法まで確認してから対応しましょう。