よくあるやつですが、諸々の事情で取り合えず書いときます。
めっちゃ簡略化したシナリオ
分離レベルはデフォルトの 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:在庫数 = 在庫数 - @出庫数 なら問題でない
