SQL Server での更新ロックを使った排他制御

よくあるやつですが、諸々の事情で取り合えず書いときます。

めっちゃ簡略化したシナリオ
分離レベルはデフォルトの read committed

入庫と出庫 + 在庫を更新するケースで、
しかも update で現在値を計算するのではなく直接設定するケース。
※update 在庫 set 在庫数 = @計算後の値 where 商品Id = @商品Id みたいな update*1

1.お互いが関係無い商品なら

時系列 入庫 出庫
1 begin tran begin tran
2 在庫取得 在庫取得
3 入庫データ追加 出庫データ追加
4 在庫更新 在庫更新
5 commit commit

ちがう商品なのでロックの競合もなく、普通に正しく更新できる。

2.同じ商品なら

時系列 入庫 出庫
1 begin tran begin tran
2 在庫取得 (Sロック※すぐ外れる) 在庫取得 (Sロック同時OK + ※すぐ外れる)
3 入庫データ追加 出庫データ追加
4 在庫更新 (Xロック) 在庫更新 (Xロックは同時無理でロック待ち)
5 commit -
6 - 在庫更新 (入庫が終わったから処理出来る)
7 - commit

という流れになって、出庫での在庫更新が入庫の内容を反映せずに更新してしまう。。
※しつこく書くけど、update が 在庫数 = 在庫数 - @出庫数 なら問題ない!!

こういうのを防ぐには、検索時に更新ロック取ろうねって話しです。

3.同じ商品でも検索時に更新ロックを取ると…

時系列 入庫 出庫
1 begin tran begin tran
2 在庫取得 (Uロック) 在庫取得 (Uロックは同時無理でロック待ち)
3 入庫データ追加 -
4 在庫更新 (Xロック) -
5 commit -
6 - 在庫取得 (入庫が終わったからUロック取れる)
7 - 出庫データ追加
8 - 在庫更新 (Xロック)
9 - commit

これで競合せずに出来るよねー
ドキュメントはここらへんで
SQL Server トランザクションのロックおよび行のバージョン管理ガイド - SQL Server | Microsoft Docs

*1:在庫数 = 在庫数 - @出庫数 なら問題でない