同じSELECT文の構造同士でデッドロックエラーが出る現象

前回の記事で、SELECTでインデックスの絞り込みが甘いせいでデッドロックが発生した記事を書いたけど、
その現象の一例について記事化しておく。


pubsデータベースのsalesテーブルを使って解説。
pubsデータベースのトランザクション分離レベルはREAD_COMMITTED。
salesテーブルは、主キーが、stor_id,ord_num,title_idとなっているクラスター化インデックステーブル。
salesテーブルにstor_id='7067'のデータは4件あり、このうち2件に対して、rowlockとupdlockのヒントをつけてselectをする。
(rowlockはロックエスカレーションを起こさないため、updlockは、この後の更新で変換デッドロックを起こさないためにつけている。)

2つのトランザクションから同じ構造のselect文を2回発行する。(トランザクションをTrAとTrBと書く。)
TrAとTrBで発行するのはそれぞれ以下のSQL。

select * from sales with(rowlock,updlock)
where stor_id='7067' and title_id='TC4203'

と、

select * from sales with(rowlock,updlock)
where stor_id = '7067' and title_id='TC3218'

まず、TrAでselectを実行する。(下図の左の選択部分を実行した結果)
実行できて、1行結果が返ってくる。

次に、TrBでselectを実行する。(下図の右の選択部分を実行した結果)
検索実行待ちとなる。

次にTrAで、2回目のselectを実行する。(下図の左の選択部分を実行した結果)
TrAは結果が返ってきて、TrBは待ちとなっていたselectがデッドロックエラーとなる。


では解説。
まず、TrAで1つ目のSELECTをした結果1行返ってきた時点で、このトランザクションのこの行に更新ロックがかかる。
トランザクション分離レベルが何であっても更新ロックの生存期間はトランザクション終了までである。

次に、TrBでSELECTをした結果、(店番に対してのみの)クラスタ化インデックスキーシークを行う。
このときに、店番で見つけたレコードに対して、更新ロックをかけ中身を見ようとする。
(ロックの役割上ロックをかけられなればそのレコードの中身を見れない。)
TrAで取得していない行をロックしつつ、
TrAで取得した行をロックをしようとしたときに、更新ロック同士は互換性がないため、
TrAのロックの開放待ちとなり、中身が見れない状態となる。
この行の内容が検索条件にヒットするのかヒットしないのかわからない状態のため、SELCT自体のTrBも実行中(ロック解除待ち)となる。

次に、TrAの2つ目のSELECTをした結果、TrBでロックされている行をロックすることができず、その行の中身が見られない。(ロック解除まち)となる。

結果、TrAとTrBが互いのロック解除待ちとなりデッドロックが成立する。

ここで、TrBが検索結果をまだ返していないのにもかかわらずTrAとのデッドロックの原因となることから、
たとえupdatelockつきselectが1回のみのトランザクション同士でも、そのトランザクション同士が同時に起動した場合にデッドロックが起こりうることになる。
どんだけやっても1つのselect文だけでは2つのトランザクション間でデッドロックが発生しなかった。

この辺のロックの詳細仕様はSQL Server内部の仕様となり不明。
(例えば、ロックする行の順番など仕様として決めてしまうとマルチプロセスで処理をする場合の足かせになってしまうので、仕様を定めていないと思います。)

今回の実験ではデッドロックとなりましたが、実行順序を変えた場合以下のようになり、ロックは発生するがデッドロックは起きませんでした。

TrBで1つ目のSELECT→1行返ってくる。
TrAで1つ目のSELECT→実行中となる。
TrBで2つ目のSELECT→1行返ってくる。
TrBでCOMMIT→TrAで1行返ってくる。(TrBのロック待ちが解除された)
TrAで2つ目のSELECT→1行返ってくる。
TrAでCOMMIT→正常完了。

で、インデックスで対象の行が絞り込まれ、絞り込まれた先には他のトランザクションのロックがかかっていない場合は、このデッドロックの現象を回避できる。
というわけで、、前回の記事のように、
salesテーブルにstor_id,title_idを持つインデックスixを作り、

TrAとTrBから同じ構造のselect文を2回発行する。
TrAとTrBで発行するのはそれぞれ以下のSQL。

select * from sales with(rowlock,updlock,index(ix))
where stor_id='7067' and title_id='TC4203'

と、

select * from sales with(rowlock,updlock,index(ix))
where stor_id = '7067' and title_id='TC3218'

まず、TrAでselectを実行する。(下図の左の選択部分を実行した結果)
実行できて、1行結果が返ってくる。

次に、TrBでselectを実行する。(下図の左の選択部分を実行した結果)
実行できて、1行結果が返ってくる。

次にTrAで、2回目のselectを実行する。(下図の左の選択部分を実行した結果)
実行できて、1行結果が返ってくる。

次にTrBで、2回目のselectを実行する。(下図の左の選択部分を実行した結果)
実行できて、1行結果が返ってくる。