SQL ServerとPostgreSQLでロストアップデートの挙動を調査する
azure mssql postgresqlDBMSを利用したシステムを作っていると、 同じレコードを複数のシステムが同時に更新しようとしたとき、 うまく排他制御や競合解決をしなければ更新データが紛失する可能性があります。
例えば2つのアプリから同時に1つのレコードにある値をインクリメントしたい場合、 Amountの初期値が0であれば、2つのアプリから1回ずつインクリメントしたので 結果はAmount = 2になるはずですが、 トランザクション分離レベルが Read Committedであれば、下図のようにAmount = 1になってしまいます。 このように複数のシステムから同時に更新を行ったときに、更新データが消失してしまうことをロストアップデートと呼びます。
これがもし注文システムであり、Amount = 在庫数であったならば、 在庫数が最後の1個で、複数のユーザーが同時に同じ商品を購入したとき、 在庫数が足りないにもかかわらず、両方のユーザーが注文できてしまうことになります。
こういうことを防止するために、DBMSには排他制御や競合解決という概念がありますが、 DBMSによって挙動が異なったり、結構理解していないところがあったので、挙動を調査してみます。
ロック挙動の調べ方 ロックの挙動を調べるために、select、update、commitの操作、各状態でのロック情報の表示 などを実現する必要があります。
まず、テーブルを作ります。 下記ではSQL Serverを例として示します。
create table dbo.Items ( Id int identity constraint PK_Items primary key, Amount int default 0 not null ) そして、下記のことができるdotnetのconsoleアプリを作ってターミナルを2つ同時に立ち上げて 確認することとします。 DBMSとの接続はEntity Framework Coreを使用することとします。
トランザクションの開始 (DatabaseFacade.BeginTransactionAsyncを使用) selectクエリで1行取得 Amount++ updateクエリで1行更新 commit ↑を2つのターミナル(tx1、tx2とする)から同時に実行
Amountの更新が消失していないか調査 正しく排他制御ができているならAmount = 2になるはず 各ランタイムやパッケージのバージョンは以下です。
dotnet core: 8.0.4 EntityFrameworkCore: 8.0.4 SQL Server: 2019 PostgreSQL: 16 なお、トランザクション分離レベルについては、Read CommittedとSerialziedについて調査することとします。
Read more...