はじめに
現場にてwith(unlock)を初めて使用し、ロックについて学習しようと思ったためまとめます。
本記事はSQLServerの公式リファレンスと同じ内容です。公式リファレンスでわかりにくいところを自分用に残しました。
※間違ってたらご指摘いただけますと幸いです。
ロックの種類、内容
共有[S]
selectステートメントなど、データの変更や更新を伴わない読み取り操作で使用。このロックがかけられている間は、データを変更できない。読み取りが完了するとこのロックは解除される。ただし、トランザクションの分離レベルがREPEATABLE READ以上に設定されている場合やトランザクションの間にロックヒントを使用して共有ロックを保つ場合を除く。
更新[U]
更新可能なリソースに使用。デッドロックを防ぐことができる。デッドロックになる例として、REPEATABLE READまたはSERIALIZABLEのトランザクションは、データを読み取るときにリソースに共有ロックをかけ、その後、行を変更する際に排他ロックに変換する必要がある。
仮に２つのトラザクションが一つのリソースに対し、共有ロックをかけてデータを更新する場合に片方のトランザクションは排他ロックとなり、もう一方は排他ロックへの変換待ち（すなわち、共有ロックのままということ）となるため、お互いがお互いの解除を待つデッドロックになってしまう。
このようなデッドロックの問題を解決するには、更新ロックを使用する。更新ロックでは、１つのリソースを一度にロックできるトランザクションは１つだけ。トランザクションはリソースを変更する場合に更新ロックが排他ロックに変換される。
排他ロック[X]
同時に実行されている複数のトランザクションが同じリソースにアクセスすることを防ぐ。ただし、NOLOCKヒントまたはREAD UNCOMMITED分離レベルなどのコミット前のデータを読み取る操作は可能。
通常、INSERT、UPDATE、DELETEなどの変更ステートメントでは読み取り操作を行ってから変更操作を行うといった組み合わせで行っている。
インテントロック
下位のロック階層に位置するリソースに共有ロックまたは排他ロックがかかるのを保護する。
例えば、テーブルにインテントロックをかければ、その下位にある行やページといったものにロックをかけられることを防ぐことができる。
このメリットは、他のトランザクションがテーブルの行に対してロックをかけるときに、通常、テーブルに他のロックがかかっているか確認することになるが、行レベルでのロックは調査に時間を要するが、テーブルにインテントロックがかかっていればロックの存在にすぐ気づくことができるのでパフォーマンスが向上する。
インテント共有、インテント排他、インテント更新、インテント排他付き共有、更新インテント排他がある。
スキーマロック[Sch-Mロック]
テーブルへの同時ロックを防ぐ。すなわち、外部からの操作をすべてブロックすることができる。
一括更新ロック
データをテーブルに一括コピーする際に一括更新(BU)ロックを使用する。存在だけまずは知っておけばよし。
キー範囲ロック
T-SQLで読みとっているレコードセットに含まれている行の範囲を暗黙的にロックしてくれる。