【SQL】ロック調査・解除完全ガイド｜SHOW PROCESSLIST・pg_locks・sys.dm_tran_locks・KILLコマンドまで

-- 現在のセッション一覧（クエリが長い場合は短縮表示）
SHOW PROCESSLIST;

-- 完全なクエリ文を表示（FULL付き）
SHOW FULL PROCESSLIST;

-- 情報スキーマ版（より詳細・WHERE で絞り込み可能）
SELECT
    id,
    user,
    host,
    db,
    command,
    time          AS elapsed_sec,
    state,
    LEFT(info, 100) AS query
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

-- 現在アクティブな InnoDB トランザクション一覧
SELECT
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS elapsed_sec,
    trx_query,
    trx_rows_locked,
    trx_rows_modified,
    trx_mysql_thread_id   -- PROCESSLIST の id と対応
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- どのトランザクションがどの行をロックしているか
SELECT
    dl.engine_lock_id,
    dl.engine_transaction_id,
    dl.object_schema,
    dl.object_name       AS table_name,
    dl.index_name,
    dl.lock_type,        -- RECORD / TABLE
    dl.lock_mode,        -- X, S, IX, IS, GAP など
    dl.lock_status       -- GRANTED / WAITING
FROM performance_schema.data_locks dl
ORDER BY dl.object_name, dl.lock_status;

-- どのトランザクションが誰を待っているか（MySQL 8.0+）
SELECT
    wait.requesting_engine_transaction_id  AS waiting_trx,
    wait.blocking_engine_transaction_id    AS blocking_trx,
    dl_wait.object_name                    AS table_name,
    dl_wait.lock_mode                      AS waiting_mode,
    dl_blk.lock_mode                       AS blocking_mode
FROM performance_schema.data_lock_waits wait
JOIN performance_schema.data_locks dl_wait
    ON wait.requesting_engine_lock_id = dl_wait.engine_lock_id
JOIN performance_schema.data_locks dl_blk
    ON wait.blocking_engine_lock_id = dl_blk.engine_lock_id;

-- sys スキーマを使った一発確認クエリ（管理者向けに最適化済み）
SELECT
    wait_started,
    wait_age,
    waiting_query,
    blocking_pid,
    blocking_query,
    blocking_trx_started,
    blocking_trx_age
FROM sys.innodb_lock_waits
ORDER BY wait_age DESC;

-- MySQL 5.7 以前のみ有効（8.0で削除）
SELECT r.trx_id        AS waiting_trx_id,
       r.trx_mysql_thread_id AS waiting_thread,
       r.trx_query     AS waiting_query,
       b.trx_id        AS blocking_trx_id,
       b.trx_mysql_thread_id AS blocking_thread,
       b.trx_query     AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id;

-- performance_schema でメタデータロック待ちを確認
SELECT
    waiting.processlist_id     AS waiting_pid,
    waiting.processlist_info   AS waiting_query,
    blocking.processlist_id    AS blocking_pid,
    blocking.processlist_info  AS blocking_query
FROM performance_schema.metadata_locks ml_wait
JOIN performance_schema.metadata_locks ml_blk
    ON ml_wait.object_schema = ml_blk.object_schema
    AND ml_wait.object_name  = ml_blk.object_name
    AND ml_wait.lock_status  = 'PENDING'
    AND ml_blk.lock_status   = 'GRANTED'
JOIN performance_schema.threads waiting
    ON ml_wait.owner_thread_id = waiting.thread_id
JOIN performance_schema.threads blocking
    ON ml_blk.owner_thread_id  = blocking.thread_id;

-- PROCESSLIST の id を指定して終了（接続ごと切断）
KILL 1234;

-- クエリのみ中断（接続は維持）
KILL QUERY 1234;

-- 複数まとめて実行する場合（動的SQL）
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.processlist
WHERE time > 300          -- 300秒以上実行中
  AND command != 'Sleep'
  AND user != 'root';     -- 管理者は除外
-- 出力をコピー&実行する

-- 現在アクティブなセッション（アイドルを除く）
SELECT
    pid,
    usename         AS user_name,
    application_name,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    EXTRACT(EPOCH FROM (NOW() - query_start))::INT AS elapsed_sec,
    LEFT(query, 120)  AS query
FROM pg_stat_activity
WHERE state != 'idle'
  AND pid  != pg_backend_pid()   -- 自分自身を除外
ORDER BY elapsed_sec DESC NULLS LAST;

-- wait_event_type = 'Lock' のセッションを抽出
SELECT
    pid,
    usename,
    wait_event_type,
    wait_event,
    state,
    EXTRACT(EPOCH FROM (NOW() - state_change))::INT AS wait_sec,
    LEFT(query, 200) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY wait_sec DESC;

-- ロック一覧（テーブル名付き）
SELECT
    l.pid,
    l.locktype,
    l.relation::regclass AS table_name,
    l.mode,
    l.granted,
    l.transactionid
FROM pg_locks l
WHERE l.relation IS NOT NULL   -- リレーション（テーブル等）のロックのみ
ORDER BY l.granted, l.table_name;

-- どの PID が誰をブロックしているかを一目で確認
SELECT
    blocked.pid          AS waiting_pid,
    blocked.usename      AS waiting_user,
    LEFT(blocked.query, 80) AS waiting_query,
    blocking.pid         AS blocking_pid,
    blocking.usename     AS blocking_user,
    LEFT(blocking.query, 80) AS blocking_query,
    EXTRACT(EPOCH FROM (NOW() - blocked.query_start))::INT AS wait_sec
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock'
ORDER BY wait_sec DESC;

-- 特定の PID (例: 12345) をブロックしているセッションを調べる
SELECT pg_blocking_pids(12345);
-- → {67890} のように PID の配列が返る

-- 全セッションのブロッキング関係を展開
SELECT
    pid AS waiting_pid,
    unnest(pg_blocking_pids(pid)) AS blocking_pid
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- クエリのみキャンセル（接続は維持、クエリを再実行できる）
SELECT pg_cancel_backend(12345);

-- セッションごと終了（トランザクションはロールバック）
SELECT pg_terminate_backend(12345);

-- ロック待ち中のセッションをまとめて終了
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
  AND pid != pg_backend_pid()
  AND EXTRACT(EPOCH FROM (NOW() - query_start)) > 300;  -- 300秒以上待機中

-- 現在実行中のリクエスト（SSMS でよく使う基本クエリ）
SELECT
    r.session_id,
    r.blocking_session_id,   -- 0 以外ならブロックされている
    r.wait_type,
    r.wait_time / 1000.0     AS wait_sec,
    r.status,
    r.percent_complete,
    DB_NAME(r.database_id)   AS db_name,
    LEFT(t.text, 200)        AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id != @@SPID  -- 自分自身を除外
ORDER BY r.wait_time DESC;

-- シンプルにブロッキング状況を確認（SSMS の基本コマンド）
EXEC sp_who2;
-- BlkBy 列が 0 以外のセッションがブロックされている
-- BlkBy に表示された SPID が原因セッション

-- オブジェクト（テーブル）レベルのロックを確認
SELECT
    l.request_session_id    AS session_id,
    OBJECT_NAME(l.resource_associated_entity_id) AS object_name,
    l.resource_type,
    l.resource_description,
    l.request_mode          AS lock_mode,
    l.request_status        -- GRANT / WAIT / CONVERT
FROM sys.dm_tran_locks l
WHERE l.resource_type = 'OBJECT'
ORDER BY l.request_status, l.object_name;

-- ブロックしているセッションとブロックされているセッションを結合
SELECT
    waiting.session_id      AS waiting_spid,
    waiting.wait_type,
    waiting.wait_time / 1000.0 AS wait_sec,
    waiting_txt.text        AS waiting_query,
    blocking.session_id     AS blocking_spid,
    blocking.status         AS blocking_status,
    blocking_txt.text       AS blocking_query,
    ses.login_name,
    ses.host_name,
    ses.program_name
FROM sys.dm_exec_requests waiting
JOIN sys.dm_exec_sessions  ses     ON ses.session_id = waiting.session_id
JOIN sys.dm_exec_sessions  blocking ON blocking.session_id = waiting.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(waiting.sql_handle)  waiting_txt
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_txt
WHERE waiting.blocking_session_id > 0
ORDER BY waiting.wait_time DESC;

-- セッション・タスクレベルの待機状況
SELECT
    wt.session_id,
    wt.blocking_session_id,
    wt.wait_type,
    wt.wait_duration_ms / 1000.0 AS wait_sec,
    wt.resource_description
FROM sys.dm_os_waiting_tasks wt
WHERE wt.blocking_session_id IS NOT NULL
ORDER BY wt.wait_duration_ms DESC;

-- 特定の SPID を終了
KILL 52;

-- 複数まとめて確認・実行する場合
SELECT 'KILL ' + CAST(session_id AS VARCHAR) + ';'
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
  AND wait_time > 60000;   -- 60秒以上待機中
-- 出力を確認してから手動実行

-- WITH STATUSONLY でロールバック進捗を確認
KILL 52 WITH STATUSONLY;
-- → 推定ロールバック残り時間が表示される

-- V$LOCK と V$SESSION を結合してロック保持者を特定
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    l.type          AS lock_type,
    l.mode_held     AS mode_held,
    l.mode_requested AS mode_req,
    s.sql_id
FROM v$session s
JOIN v$lock l ON l.sid = s.sid
WHERE l.block = 1   -- ブロックしているセッションのみ
ORDER BY s.sid;

-- セッションの強制切断
ALTER SYSTEM KILL SESSION '123,4567' IMMEDIATE;
-- 123 = SID, 4567 = SERIAL#

-- In_use が 1 以上のテーブルが使用中（ロック中）
SHOW OPEN TABLES WHERE In_use > 0;

-- Name_locked = 1 はメタデータロック中
SHOW OPEN TABLES WHERE Name_locked > 0;

-- テーブルレベルロックを解除（LOCK TABLES の場合）
UNLOCK TABLES;