title: SQL Serverのロックについて出来る限り分かりやすく解説
tags: SQL SQLServer ロック DB
author: maaaaaaaa
slide: false
#SQL Serverのロックとは
DB上でデータを操作（SELECT/INSERT/UPDATE/DELETE等）する際、データの整合性を保つために使われる排他制御の仕組み。
例えば、「1つのレコードを一度に更新できるのは、1つのクエリだけ」といったルールを実現してくれる。
#SQL Serverのロックを理解するための3つのポイント
１．ロックには複数の粒度（階層とも呼ばれる）が存在する
２．ロックには複数の種類が存在する（ロックモード）
３．各ロックモード間には「互換性」という関係性がある
以降で順を追って説明する。
#なぜロックについて知る必要があるのか
ロックはデータの整合性を保つために必要な仕組みだけど、ブロッキングの原因にもなりやすい。
そのため、クエリを実行する際に「どういったロックが、どの粒度でかけられるのか、そのロックの互換性はどうか」といったことを開発者が意識できるとブロッキングの発生を未然に防ぐ（または最小限に抑える）ことが可能となる。
ロックの粒度
ロックには粒度が存在する。ロックリソースとも呼ばれる。
MSのドキュメントには、ロックリソースの種類として以下の図が掲載されている。見方にポイントがあるので解説。
出典：https://docs.microsoft.com/ja-jp/sql/2014-toc/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2014
###①RID～TABLE
SQL Serverのデータ構造と対応しており、階層構造になっている。
以下のようにKEY→PAGE→HoBT(Heap or BTree=Index)→TABLEの順番。KEYが最下層、TABLEが最上位。
※HoBT：Heap or B-Treeの略。
※EXTENT：物理的に連続した8ページをひとまとめにしてエクステントと呼ぶ。ページの効率的な管理のために使用される。
###②METADATA
統計情報の更新時などに獲得されるロックリソース。
###③DATABASE
最も粒度の大きいロックリソース。クエリを実行すると必ず該当DBにSロックをかける。
また、ALTER DATABASEの実行時などにこのリソースにUロック等が獲得される。
--互換性レベルの変更。該当DBにUロックをかける
ALTER DATABASE
SomeDataBase
SET COMPATIBILITY_LEVEL = 150
※ロック粒度についてのドキュメント
https://docs.microsoft.com/ja-jp/sql/2014-toc/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2014
#ロックの種類
MSのドキュメントから、よく出てくるロックの種類（ロックモード）を抜粋。
ポイント：ロックにはいろいろな種類がある。発行するクエリによって、SQL Serverが自動的に必要なロックをかけてくれる。（各ロックの違いは後述）
ポイント：ロックの種類が異なるだけで、クエリを発行すると実は何らかのロックが必ず獲得されている。
ポイント：最低限覚えておくと良いのは、「SELECTはS、INSERT/UPDATE/DELETEはX、with(nolock)つきのSELECTはSch-S」
出典：https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2008-r2/ms175519%28v%3dsql.105%29
#ロックの互換性
###「互換性」とは
「同一のロックリソースに対して、同時に旗を立てることができるかどうか」とイメージすると分かりやすい。
###例：S Lock（共有ロック）同士
互換性があるため、同時に2つ以上のS Lockをかけることができる
クエリレベルだと、「次の二つのクエリは同時に実行できる」という意味。
###例：S Lock（共有ロック）とX Lock(排他ロック）
互換性が無いため、同時にかけられるロックは1つだけ。
既にS Lockをかけていた場合は、X Lockはかけられない。このとき、X Lockをかけるためにクエリが待ち状態になる。
→これが「ブロッキング」
クエリレベルだと、「次の二つのクエリは同時に実行できない」という意味。
逆も同様。既にX Lockをかけていた場合は、S Lockはかけられない。S Lockをかけるためにクエリが待ち状態になる。
###例：with(nolock)をつけたクエリの挙動
with(nolock)は、正確にはロックをかけないわけではなく、Sロックの代わりにSch-Sロック(スキーマ安定度ロック：Schema Stability Lock)という弱いロックをかけている。
クエリレベルだと、「次の二つのクエリは同時に実行できる」という意味。
ここはとても大事なところなので、Sch-SロックとSロックの図を並べて見比べてみる。
ポイント①：with(nolock)無しのSELECT文を長時間実行すると、取得したSロックによって更新処理(X Lock）をブロックしてしまう恐れがある
→データの読み取りしかできない権限であっても、長時間のSELECT文実行によってブロッキングを発生させる恐れがあるため気をつける
ポイント②：Sch-SロックはXロックと競合しない。その代わり、未コミットのデータを読み取れてしまうため、正しくない結果を読み取ってしまう恐れがある
※with(nolock)を全テーブルにつけたときと、「SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED」をクエリの前に書くことで得られる効果は同一。
※with(nolock) / READ UNCOMMITTEDトランザクション分離レベルによって影響を受けるのはSELECT文のみ。UPDATE/INSERT/DELETE等は挙動が変わらない。
###例：with(nolock)をつけてもブロッキングが発生するとき
どんなロックとも競合する最強のロックである、Sch-Mロック(スキーマ修正ロック：Schema Modification Lock)がかかっている状態だと、with(nolock)をつけたSELECT文でも互換性が無いためブロックされる。
クエリレベルだと、「次の二つのクエリは同時に実行できない」という意味。
###例：1つのクエリが、同一リソースに複数ロックをかけるケース
update文を実行すると、レコードに対してUロックとXロックが取得される。おそらくUロックを獲得した後にXロックへと昇格させていると思われる。
###ロックモードの互換性
MSのドキュメントにある以下の図を参照。
全ての互換性を示した図は以下の通り。↑の互換性の加えて、Sch-S / Sch-Mロックのみ押さえておけばとりあえずはOK。
出典：https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2008-r2/ms186396(v=sql.105)
#ロックの種類と粒度と互換性について理解する
###ポイント：同一粒度の同一リソースには、互換性が無いロックは同時にかけられない
###ポイント：同一粒度の別リソースには、互換性が無いロックを同時にかけられる
###ポイント：粒度が異なるロック
上位の階層に互換性の無いロックがかかっていると、下位のリソースに対してロックをかけられない
###ポイント：インテントロックについて理解する
レコードにXロックをかける場合、その上位階層であるPAGEとTABLEに対して、IXロック（インテントXロック）が自動でかけられる。
理由は、排他制御の処理効率アップのため。
もしIXロックがなければ、例えば他のクエリがテーブル全体にXロックをかけたいときに、全ページと全レコードに互換性の無いロックがかかっていないか調べる必要がある。
一方、テーブルにIXロックがあると、それだけでテーブルにXロックをかけられないことが分かるため、ロックの可否の判断効率が良い。
先ほどの「上位の階層に互換性の無いロックがかかっていると、下位のリソースに対してロックをかけられない」というルールは、
インテントロックを用いると以下のように説明できる。
「TABLEに対してXロックを獲得している場合は、TABLEへのIXロックが互換性が無いためブロッキングされる」
###ポイント：ロックの種類、対象の粒度などはすべてSQL Serverが自動的に決めてやってくれる
→ある程度なら意図的に粒度をいじることもできるが、基本的にはSQL ServerにまかせておけばOK。
#ロックの保持期間
ロックを保持する期間は、「明示的にトランザクションを開始しているかどうか」で変わる。
###明示的なトランザクション(begin tran - commit tran)を使わない場合
クエリ実行直後にロックを開放。
###明示的なトランザクションを使う場合
Sロック：クエリ実行直後にロックを開放。（既定のトランザクション分離レベルである「Read Committed」の場合の挙動）
Uロック / Xロック：クエリの開始からトランザクションのコミット又はロールバックが完了するまでロックを保持する。
イメージ図は以下の通り。
###ブロッキングを最小限に留めるコツ
トランザクションを張っている期間は必要最小限に留め、可能な限り短くする。（コード量の観点からも、実行時間の観点からも）
例：要件的に許されるのであれば、SELECT文をトランザクションの外に出すことで、TableBのロック保持期間を短縮できる。
#ロックエスカレーション
例えばテーブルの全レコードを更新する際、1レコードずつにXロックをかけるより、テーブルに1つだけXロックをかけてしまったほうが効率が良い。※1ロックあたり、粒度に関係なくメモリを96Bytes消費するためメモリリソースの観点からも効率が良い。
https://docs.microsoft.com/ja-jp/sql/database-engine/configure-windows/configure-the-locks-server-configuration-option?view=sql-server-2017
このように、大量のPAGEやKEYにロックをかける場合に、SQL Serverが自動的にロックの粒度をTABLEに昇格する場合がある。
この挙動をロックエスカレーションという。
ロックエスカレーションが起きると、該当テーブルへの更新がすべてブロックされてしまうため注意が必要。
ポイント：エスカレーション先のリソースはテーブルのみ。（KEY→PAGEといったエスカレーションは無い。必ずKEY→TABLEやPAGE→TABLEとなる。）
#デッドロック
下図において、①から④の順番でクエリが実行されるとすると、
①プロセスAがテーブルAのKEYロックを取得
②プロセスBがテーブルBのKEYロックを取得
③プロセスAがテーブルBのKEYロックを取得しようとするが、ブロッキングが発生。待ち状態になる。
④プロセスBがテーブルAのKEYロックを取得しようとするが、ブロッキングが発生。待ち状態になる。
→この時点で、相互にブロックし合う関係になってしまい、このままだとプロセスA、プロセスB共に無限に待ち続けることに。これがデッドロック状態。
⑤数秒後、SQL Serverが自動でデッドロックを検出し、プロセスAまたはプロセスBのどちらかを強制終了し、デッドロックを解消。
ポイント：ブロッキングとデッドロックの違い
・「ブロッキング」は、blockerのクエリが終了しない限りwaiterのクエリは無限に待たされる。一方で、「デッドロック」は、SQL Serverが数秒間隔で自動検出して自動解消してくれる。
・「ブロッキング」は、SQL Serverの介入が無いためKILLしない限りblockerもwaiterも最終的には実行完了する。一方で、「デッドロック」は、クエリ実行中であっても片方のプロセスがSQL Serverによって強制終了される。
#クエリでブロッキングを検出
###検出クエリ
Microsoft MVPの小澤さんのgithubで公開されているクエリが素晴らしく便利。（そのまま実行してOK）
https://raw.githubusercontent.com/MasayukiOzawa/SQLServer-Util/master/Lock/%E3%83%96%E3%83%AD%E3%83%83%E3%82%AD%E3%83%B3%E3%82%B0%E3%83%81%E3%82%A7%E3%83%BC%E3%83%B3%E3%81%AE%E5%8F%96%E5%BE%97.sql
ブロッキングが起きている場合は、以下のような情報が取得できる。
さらに、HeadBlockerのspid(プロセスID）を使って、一連のブロッキングの元凶となっているクエリの詳細情報を取得できる。
dbcc inputbuffer(70)
go
sp_who2 70
↓のように、クエリテキストやホスト名などが分かる。
###現在実行中のクエリリストからも、ブロッキングの発生が分かる
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 100
der.session_id as spid
,der.blocking_session_id as blk_spid
,datediff(s, der.start_time, GETDATE()) as elapsed_sec
,DB_NAME(der.database_id) AS db_name
,des.host_name
,des.program_name
,der.status -- Status of the request. (background / running / runnable / sleeping / suspended)
,dest.text as command_text
,REPLACE(REPLACE(REPLACE(SUBSTRING(dest.text,
(der.statement_start_offset / 2) + 1,
((CASE der.statement_end_offset
WHEN -1 THEN DATALENGTH(dest.text)
ELSE der.statement_end_offset
END - der.statement_start_offset) / 2) + 1),CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' ') AS current_running_stmt
,datediff(s, der.start_time, GETDATE()) as time_sec
,wait_resource --ロックされているリソース名
,wait_type
,last_wait_type --最後または現在の待機の種類の名前
,der.wait_time as wait_time_ms
,der.open_transaction_count
,der.command
,der.percent_complete --一部コマンドの進捗状況を表示してくれるらしい
,der.cpu_time
,(case der.transaction_isolation_level
when 0 then 'Unspecified'
when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted'
when 3 then 'Repeatable'
when 4 then 'Serializable'
when 5 then 'Snapshot'
else cast(der.transaction_isolation_level as varchar) end) as transaction_isolation_level
,der.granted_query_memory * 8 as granted_query_memory_kb --キロバイト単位
-- ,deqp.query_plan -- 実行プラン
-- ,datediff(s, der.start_time, GETDATE()) / 60.0 as time_min
-- ,des.login_time
-- ,(select top (1) waitresource from master.dbo.sysprocesses where spid = der.session_id) as waitresource
-- ,(select top (1) lastwaittype from master.dbo.sysprocesses where spid = der.session_id) as lastwaittype
FROM
sys.dm_exec_requests der
--JOIN sys.dm_exec_connections dec ON der.connection_id = dec.connection_id
JOIN sys.dm_exec_sessions des ON des.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS dest
--OUTER APPLY sys.dm_exec_query_plan(plan_handle) AS deqp
WHERE
des.is_user_process = 1
AND datediff(s, der.start_time, GETDATE()) >= 1 -- 例：1秒以上実行中のクエリに限定
--AND dest.text like '%%' -- クエリの中身でlike検索したいときはここを編集
ORDER BY
datediff(s, der.start_time, GETDATE()) DESC
↓のように、blk_spidに0以外の数字が表示される場合は、そのプロセスIDのクエリによってブロックされていると判断できる。
#まとめ
ロックの粒度、種類、互換性について解説した。
それぞれについて完全に覚えておく必要は無いけど、「XロックとSロックに互換性がないから、UPDATEの実行中は該当レコードへのSELECTはブロックされるのだろうな。データの大量更新を実行するタイミングはブロッキングが起きていないかDMVを使ってチェックしよう」
というように今回の内容を業務で使用するクエリレベルの話に自分で変換して考えられるようになると、ブロッキングなどのトラブルの軽減につながると思う。
