そのへんのちらしのうら

調べたこと、学んだこと、おもしろかったこと。

SQL Serverでロック待ちSQLを探して対処する

SQLServer

先日、稼働したシステムで、ロック待ちによりアプリケーションの遅延が発生したときに、調査方法を熟知しておらず、解決するまでに時間がかかったので、ここでまとめておきたい。

目標

プログラムでロック待ちが発生したときに、原因を素早く突き止め、対処できるようにする。
具体的には、
・どの行がロックされているのか分かる。
・どのSQLが原因かわかる。
・どのセッションか分かる。

１．ロック待ちのセッションとリソースを調べる

とりあえずロック待ち調査といえば

検索するとよく紹介されているのが「sys.dm_tran_locks」。

sys.dm_tran_locks (TRANSACT-SQL) - SQL Server | Microsoft Docs

SQL Server でのロック・タイムアウト - マイクロソフト系技術情報 Wiki

ただ、これだけ読んでみても意味がきちんと分かっていないので使いこなせない……
ので、もう少し掘り下げて調べてみる。

そもそも「sys.dm_tran_locks」とは？

上記のMSDNドキュメントによれば、動的管理ビューのひとつで現在アクティブなロック要求（待ちも含む）を示しているとのこと。 sys.dm_tran_locksを使いこなせばロック待ちへの対処はできる、と。

ここからはsys.dm_tran_locksの各項目の意味について。

resource_type

「リソースの種類」については、MSDN上ここに説明がある。

sys.dm_tran_locks (TRANSACT-SQL) - SQL Server | Microsoft Docs

自分がよく見るリソースの種類を中心に、自分なりにかみ砕いて理解すると、
・KEY⇒インデックス内の行をロックした場合
・RID⇒「RID」はヒープ表（クラスター化インデックスのない表）の1行の識別子。ヒープ内の1行をロックした場合に表示
（大概のアプリケーションでは、テーブルにPK＝クラスター化インデックスが定義されていることが大半だと思う。 結果、基本的には「RID」よりも「KEY」が表示されることがほとんどという理解を今のところしている）
・HOBT⇒「HOBT」とは、「heap or B-Tree」のことで、ヒープ表かインデックスのこと
（「KEY」「RID」と、「HOBT」の違いが難しいところだが、前者は「行」そのもの、後者は「インデックス含む行へのアクセスパス」という理解）
・ALLOCATION_UNIT⇒これはよくわからん

resource_associated_entity_id

ここはMSDNどおりで。

リソースが関連付けられているデータベース内のエンティティの ID。 この ID はリソースの種類に応じて、オブジェクト ID、Hobt ID、またはアロケーション ユニット ID になります。

sys.partitions

resource_associated_entity_idが何を指し示すのかは「sys.partitions」から探す。 どうやら、全てのテーブルやインデックスは少なくとも一つのパーティションというものに含まれる、らしい。

sys.partitions (TRANSACT-SQL) - SQL Server | Microsoft Docs

【SQL-01】 ロック待ちセッションとロックの種類、状態を調べる

最終的に作成したSQLはこちら。 SQL-01

SELECT
 V1.request_session_id
,V1.resource_database_id
,DB_NAME(V1.resource_database_id) AS resource_database
,V1.resource_type
,V1.resource_associated_entity_id
,CASE
    WHEN V1.resource_type = 'DATABASE' THEN DB_NAME(V1.resource_database_id)
    WHEN V1.resource_type = 'OBJECT' THEN
        OBJECT_SCHEMA_NAME(V1.resource_associated_entity_id, resource_database_id)
        + '.' + OBJECT_NAME(V1.resource_associated_entity_id, resource_database_id)
    WHEN V1.resource_type = 'ALLOCATION_UNIT' THEN 
    (
        SELECT
        OBJECT_NAME(sp.object_id)
        + '.' +  si.name
        FROM sys.allocation_units sa
        LEFT JOIN sys.partitions sp 
        ON sa.container_id = sp.hobt_id
        LEFT JOIN sys.indexes si 
        ON sp.object_id = si.object_id 
        AND sp.index_id = si.index_id
        WHERE sa.allocation_unit_id = V1.resource_associated_entity_id
    )
    WHEN V1.resource_type IN('HOBT', 'KEY', 'PAGE') THEN 
    (
        SELECT 
        OBJECT_NAME(sp.object_id, resource_database_id) + '.' +  si.name
        FROM sys.partitions sp
        LEFT JOIN sys.indexes si
        ON sp.object_id = si.object_id 
        AND sp.index_id = si.index_id 
        WHERE sp.hobt_id = V1.resource_associated_entity_id
    )
    ELSE 'OTHERS'
 END AS resource_name
,V1.resource_description
,V1.request_mode
,V1.request_status
FROM sys.dm_tran_locks V1
ORDER BY
 V1.request_session_id
,V1.resource_type

参考： SQL Server でのロック・タイムアウト - マイクロソフト系技術情報 Wiki

２．ロックされている行そのものを取得する

%%lockres%%とは

ロックされている行そのものを知りたいときに、使われているのがこちら。
「ロックのリソースを確認することができる仮想列」とのこと。

ロックリソースを取得する仮想列 at SE の雑記

実際に、SELECT文に%%lockres%%を含めて実行してみると、仮想列が取れる。 この仮想列により、ロック時の行を識別できるようだ。

SELECT *
,%%lockres%%
FROM [tablename]
【SQL-02】 ロックされている行を調べる

上記のサイト等も参考に、自分なりに取得項目などを整理したSQLがこちら。 [TableName] には、SQL-01で分かったロックが発生しているテーブル名を入れる。

SELECT
 V1.request_session_id
,V1.resource_type
,V1.request_mode
,V1.request_type
,V1.request_status
,T1.%%lockres%% AS resource_description
,T1.*
FROM [TableName] T1
INNER JOIN sys.dm_tran_locks V1
ON 1=1
AND T1.%%lockres%% = V1.resource_description
ORDER BY
 V1.request_session_id
,T1.%%lockres%%

３．ロックを発生させているSQLテキストを取得する

では、ロックを発生させているSQLそのものを知るには？
（※ここはSSMSの利用状況モニタでも分かるけれど……）

https://code.i-harness.com/ja/q/e5ec3

【SQL-03】 ロックを発生させているSQLを調べる
SELECT
 S1.session_id
,S2.TEXT
,S1.start_time
,S1.status
,S1.command
,DB_NAME(S1.database_id)
,S1.blocking_session_id
,S1.wait_time
,S1.last_wait_type
,S1.wait_resource
FROM sys.dm_exec_requests S1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS S2
ORDER BY
 S1.session_id
,S2.TEXT
;

「sys.dm_exec_requests」は、現在実行中のSQLを取れる。
「sys.dm_exec_sql_text」は、sql_handle（＝各SQLの識別子）をもとにSQLテキストを示してくれる。

ちなみに、「wait_resource」にも、%%lockres%%相当の情報が示されているっぽい。

Decoding Key and Page WaitResource for Deadlocks and Blocking - by Kendra Little

【補足】SQLの履歴

なお、これまでに流れたSQLを知りたい場合は、下記で取得できる。

sys.dm_exec_query_stats (TRANSACT-SQL) - SQL Server | Microsoft Docs

ただし、キャッシュからクエリからプランが消えると取得できない。

SELECT
 st.text
,last_execution_time
FROM   sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE  last_execution_time >= [time]
ORDER BY last_execution_time
;

４．ロックを発生させているセッションの詳細を取得する

こちらを参考に。

SQL Server で現在接続中のセッションを確認するSQL - くらげのChangeLog

select
 session_id
,host_name
,program_name
,status
,last_request_start_time
,last_request_end_time
,lock_timeout
from sys.dm_exec_sessions
where host_name is not NULL

ここで最終確認をして、あとは「KILL」すればよい。