【SQL Server】テーブルロックを確認するSQL｜sys.dm_tran_locks・sp_who2・ブロッキング調査

SQL Serverで更新処理が終わらない、画面が固まる、別セッションのSQLが待たされる。そのような場面では、まずテーブルロックとブロッキングの有無を確認します。

この記事では、sys.dm_tran_locks、sys.dm_exec_requests、sys.dm_exec_sessions、sp_who2を使って、ロックしているセッション、待たされているSQL、対象テーブルを調べる手順をまとめます。SQL Serverのロック確認だけでなく、解除する前に見るべきポイントまで実務向けに整理します。

SQL Serverのテーブルロック確認で見るもの

SQL Serverのロック調査では、「誰が待っているか」「誰が止めているか」「どのテーブルやリソースで待っているか」を分けて確認します。テーブル名だけを探そうとすると、KEY、PAGE、RID、HOBTなどの単位で出てきたときに見落としやすくなります。

まず実行するSQL: ブロッキング中のセッションを確認する

ロック待ちを疑ったら、最初に blocking_session_id が入っているリクエストを確認します。blocking_session_id は、そのセッションを待たせている相手のセッションIDです。

SELECT
    r.session_id,
    r.blocking_session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    DB_NAME(r.database_id) AS database_name,
    s.login_name,
    s.host_name,
    s.program_name,
    t.text AS running_sql
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

このSQLで行が返る場合、session_id が待たされている側、blocking_session_id が待たせている側です。まずはブロッキング元のセッションIDを控え、次のSQLでそのセッションが何をしているか確認します。

ブロッキング元のSQLを確認する

待たせている側のセッションが実行中であれば、sys.dm_exec_requests からSQL本文を確認できます。もし実行中のリクエストがないのに他セッションを止めている場合、未コミットのトランザクションを開いたまま放置している可能性があります。

DECLARE @blocking_session_id int = 57;

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status AS session_status,
    r.status AS request_status,
    r.command,
    r.wait_type,
    r.wait_resource,
    t.text AS running_sql
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.session_id = @blocking_session_id;

session_status が sleeping でも、トランザクションが残っていればロックを保持していることがあります。アプリケーションの接続プールや手動実行したSQLで、BEGIN TRAN 後に COMMIT / ROLLBACK していないケースはよくあります。

sys.dm_tran_locksでロック一覧を確認する

現在取得中または待機中のロックは sys.dm_tran_locks で確認できます。まずはセッションID、DB名、リソース種別、ロックモード、状態を一覧で見ます。

SELECT
    l.request_session_id AS session_id,
    DB_NAME(l.resource_database_id) AS database_name,
    l.resource_type,
    l.request_mode,
    l.request_status,
    l.resource_associated_entity_id
FROM sys.dm_tran_locks AS l
WHERE l.resource_database_id = DB_ID()
ORDER BY l.request_session_id, l.resource_type, l.request_mode;

テーブル名付きでロックを確認するSQL

sys.dm_tran_locks だけでは、ロック対象がIDで表示されることがあります。テーブル単位で見たい場合は sys.partitions と結合し、OBJECT_NAME でテーブル名に変換します。

SELECT
    l.request_session_id AS session_id,
    DB_NAME(l.resource_database_id) AS database_name,
    COALESCE(
        OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id),
        OBJECT_NAME(p.object_id, l.resource_database_id)
    ) AS table_name,
    l.resource_type,
    l.request_mode,
    l.request_status
FROM sys.dm_tran_locks AS l
LEFT JOIN sys.partitions AS p
    ON p.hobt_id = l.resource_associated_entity_id
WHERE l.resource_database_id = DB_ID()
  AND l.resource_type IN ('OBJECT', 'KEY', 'PAGE', 'RID', 'HOBT')
ORDER BY l.request_session_id, table_name, l.resource_type;

OBJECT ロックはオブジェクトIDから直接テーブル名を取れることがあります。一方、KEY や PAGE などは hobt_id 経由でsys.partitions と結び付けると追いやすくなります。

特定テーブルだけロックを確認するSQL

実務では「このテーブルがロックされているかだけ見たい」という場面がよくあります。その場合は、対象テーブルの object_id を使って絞り込みます。スキーマ名とテーブル名は自分の環境に合わせて変更してください。

DECLARE @object_id int = OBJECT_ID(N'dbo.Orders');

SELECT
    l.request_session_id AS session_id,
    DB_NAME(l.resource_database_id) AS database_name,
    COALESCE(
        OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id),
        OBJECT_NAME(p.object_id, l.resource_database_id)
    ) AS table_name,
    l.resource_type,
    l.request_mode,
    l.request_status
FROM sys.dm_tran_locks AS l
LEFT JOIN sys.partitions AS p
    ON p.hobt_id = l.resource_associated_entity_id
WHERE l.resource_database_id = DB_ID()
  AND (
      l.resource_associated_entity_id = @object_id
      OR p.object_id = @object_id
  )
ORDER BY l.request_session_id, l.resource_type, l.request_mode;

このSQLで request_status が WAIT の行があれば、そのテーブルに関係するロック待ちが発生しています。GRANT の行は、すでに取得済みのロックです。待っている側と持っている側を切り分けるには、前述の blocking_session_id と合わせて確認します。

ロックモードの意味

request_mode には、ロックの強さや目的が表示されます。すべてを暗記する必要はありませんが、S、U、X、IS、IX はよく出るため、意味を押さえておくと調査しやすくなります。

ロック待ちの直接原因になりやすいのは、更新処理が保持する X ロックや、大量処理に伴う範囲の広いロックです。ただし、表示されているロックモードだけで判断せず、実行SQL、待機時間、対象テーブルを合わせて見ます。

待機チェーンを確認するSQL

複数セッションが連鎖的に待っている場合は、sys.dm_os_waiting_tasks で待機チェーンを見ると整理しやすいです。

SELECT
    wt.session_id AS waiting_session_id,
    wt.blocking_session_id,
    wt.wait_type,
    wt.wait_duration_ms,
    r.wait_resource,
    t.text AS waiting_sql
FROM sys.dm_os_waiting_tasks AS wt
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = wt.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE wt.blocking_session_id IS NOT NULL
  AND wt.blocking_session_id <> 0
ORDER BY wt.wait_duration_ms DESC;

単純なロック待ちだけでなく、先頭のセッションが何を待たせているかを把握したいときに使います。長時間待機している順に見ると、影響範囲の大きい問題から追いやすくなります。

sp_who2で簡易確認する

詳細な調査はDMVを使うのが基本ですが、急ぎの一次確認なら sp_who2 も使えます。BlkBy にセッションIDが表示されていれば、そのセッションにブロックされています。

EXEC sp_who2;

sp_who2 は手早い反面、SQL本文やロック対象テーブルまでは分かりにくいです。原因調査まで進める場合は、前述のDMVのSQLに切り替えましょう。

ロックを解除する前に確認すること

ブロッキング元のセッションが分かったとしても、すぐに KILL するのは危険です。業務処理中の更新、バッチ、メンテナンス処理、インデックス作成などを止めると、ロールバックに長時間かかったり、アプリケーション側でエラーが発生したりします。

どうしても終了する必要がある場合は、対象セッションIDを指定して KILL します。ただし、ロールバック処理が終わるまでロックが残ることがあります。

-- 例: セッション57を終了する。実行前に必ず影響を確認する
KILL 57;

未コミットトランザクションを確認するSQL

セッションが sleeping なのにロックを保持している場合、未コミットのトランザクションが残っている可能性があります。sys.dm_tran_session_transactions と sys.dm_tran_database_transactions を使うと、どのセッションにトランザクションが残っているかを確認できます。

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    at.transaction_begin_time,
    dt.database_transaction_state,
    dt.database_transaction_log_bytes_used
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
JOIN sys.dm_tran_database_transactions AS dt
    ON dt.transaction_id = st.transaction_id
WHERE dt.database_id = DB_ID()
ORDER BY at.transaction_begin_time;

開始時刻が古いトランザクションほど、長時間ロックを保持している可能性があります。ただし、バックアップ、メンテナンス、バッチ処理など正常に長く動く処理もあるため、接続元と実行内容を確認してから判断します。

-- 現在のDBで最も古いアクティブトランザクションを確認する
DBCC OPENTRAN;

DBCC OPENTRAN は、データベース内で古いアクティブトランザクションを確認する簡易的な方法です。ロック待ちの根本原因が未コミット放置かどうかを確認する補助として使えます。

よくある原因

SQL Serverのロック待ちは、単に「ロックがある」だけではなく、なぜ長く保持されているのかを確認することが大切です。よくある原因は次の通りです。

更新SQLそのものの書き方を見直す場合は、SQLのUPDATE文でNULLに更新する方法 のような基本構文の記事も合わせて確認しておくと、WHERE句漏れや制約エラーの切り分けがしやすくなります。

読み取りが更新をブロックする場合の考え方

読み取り処理と更新処理がぶつかっている場合、分離レベルの設計も重要です。SQL Serverでは、環境によって READ_COMMITTED_SNAPSHOT を使うことで、読み取りと更新の待ちを減らせることがあります。

SELECT
    name,
    is_read_committed_snapshot_on
FROM sys.databases
WHERE name = DB_NAME();

ただし、分離レベルの変更はアプリケーション全体の読み取り整合性に影響します。ロック待ちを減らすためだけに即変更するのではなく、業務要件と検証環境での確認が必要です。

テーブル一覧や対象テーブルの確認も合わせて行う

ロック対象のテーブル名が分かったら、列構成、インデックス、対象データ量も確認します。テーブル一覧やメタ情報の確認方法は SQLでテーブル一覧を確認する方法 にまとめています。ロック調査では、対象テーブルの主キーや更新条件に使っている列を確認すると、インデックス不足や不要な全表走査に気付きやすくなります。

Oracleのロック調査との違い

Oracleでは V$SESSION や V$LOCK を使ってロックを調査しますが、SQL ServerではDMVの組み合わせで見るのが基本です。Oracle側の調査が必要な場合は、Oracleでロックしているセッションを確認・KILLする方法 も参考になります。トランザクションの考え方を整理したい場合は Oracleのトランザクション完全ガイド、デッドロックの切り分けは ORA-00060 デッドロックの原因と対処法 も関連します。

権限が足りない場合

DMVを参照するには、環境によってサーバー状態を参照する権限が必要です。SQL Server 2019以前では VIEW SERVER STATE、SQL Server 2022以降では一部の情報に VIEW SERVER PERFORMANCE STATE が必要になる場合があります。権限不足でエラーになる場合は、DBAや管理者に参照権限の付与を相談してください。

-- 権限付与の例。実行は管理者が判断する
GRANT VIEW SERVER STATE TO [login_name];

調査の流れ

現場で迷わないように、ロック調査の流れをまとめると次の順番です。

1. sys.dm_exec_requests で blocking_session_id を確認する
2. 待たせているセッションID、待たされているセッションIDを控える
3. sys.dm_tran_locks でロックモードと対象リソースを確認する
4. テーブル名、接続元、実行SQL、待機時間を確認する
5. 未コミット放置か、正常な長時間処理かを切り分ける
6. 必要な場合だけ担当者確認後に KILL を検討する

よくある質問

SQL Serverでテーブルロックを確認する一番簡単な方法は？

まずは sys.dm_exec_requests の blocking_session_id を確認します。ブロッキングがあるかを素早く見たいだけなら sp_who2 の BlkBy でも確認できます。

sys.dm_tran_locksでテーブル名が出ないのはなぜですか？

ロック対象が KEY、PAGE、RID などの単位で表示されるためです。resource_associated_entity_id を sys.partitions の hobt_id と結合すると、対象テーブルを追いやすくなります。

特定テーブルだけロックされているか確認できますか？

確認できます。OBJECT_ID(N'dbo.テーブル名') で対象テーブルのIDを取得し、sys.dm_tran_locks の resource_associated_entity_id または sys.partitions.object_id で絞り込みます。テーブル単位で影響範囲を見たい場合に便利です。

sleepingのセッションがロック原因になることはありますか？

あります。実行中のSQLがなくても、未コミットトランザクションが残っているとロックを保持します。sys.dm_tran_session_transactions や DBCC OPENTRAN で、古いトランザクションが残っていないか確認します。

ロックしているセッションをKILLしてもよいですか？

業務処理中の可能性があるため、すぐにKILLするのは避けます。接続元、実行SQL、待機時間、トランザクション状態を確認し、担当者確認後に判断します。KILL後もロールバックが完了するまで待たされることがあります。

ロック待ちとデッドロックは同じですか？

同じではありません。ロック待ちは一方が待っている状態です。デッドロックは複数のセッションが互いに相手のロック解放を待ち、SQL Serverが片方を犠牲者として終了する状態です。

まとめ

SQL Serverでテーブルロックを確認するときは、sys.dm_exec_requests でブロッキングの有無を確認し、sys.dm_tran_locks でロックの種類と対象を調べます。sp_who2 は一次確認には便利ですが、原因調査ではDMVを組み合わせて見る方が確実です。

大切なのは、ロックしているセッションを見つけることだけではありません。未コミット放置、長時間更新、インデックス不足、分離レベルの問題など、なぜロックが長く残っているのかを切り分けることです。セッション終了は最後の手段として、影響を確認してから実行しましょう。