SqlServerのトランザクションについて。 SqlServerのデータを取得・更新・追加するGU

Question

SqlServerのトランザクションについて。

SqlServerのデータを取得・更新・追加するGUIアプリをc#で作成しています。※このアプリを使用ユーザーは不特定多数。

データを取得し更新する操作があります。取得の際に取得行をロック（UPDLOCK）しています。
この時のアプリ操作として、取得ボタン押下〜更新ボタン押下までの処理について質問です。
現状は、以下のような処理にしています。
1、取得ボタン押下の際の処理でトランザクションを開始。
2、（ユーザーにて編集作業）←この間、トランザクションはコミットせず保持したまま
3、更新ボタン押下でトランザクションをコミット

質問①:この処理は一般的に正しいやり方でしょうか。問題なくやりたいこと（ロックはうまく機能し他ユーザーから対象レコードを閲覧できないこと）はできています。
質問②:SqlCommandのトランザクションタイムアウトは規定が1分となっているようですが、1分経ってもトランザクションは保持されたままになっています。自動でロールバックしないのはなぜでしょうか。

少し長くなりましたが、よろしくお願い致します。

naktak · Accepted Answer

タイムアウトはしたくないんですね。
情報呼び出し後にトイレなどに立って1,2時間戻ってこなかったとしても問題ありませんか？
ともすると、不測のロックし続け状態に対してリカバリー可能なアプローチが必要になります。

恐らく、呼出によるDBのロック操作は行わない方がいいでしょう。

ご想像の通り、画面としてのデータロックは、画面IDを主キーにした、操作してるユーザーと、操作データのキー情報、あとはロック開始日時などを把握するテーブルを持って、それをもって呼び出し可能かを判断した方がいいでしょう。

呼出時は、上記テーブルにユーザーの異なるデータがないことによって呼出可能とする。
更新時には、上記テーブルにデータがあること、楽観的排他(タイムスタンプ)による更新データの変化がないこと、そしてどちらもUPDLOCKすることで実現可能ではないでしょうか。
操作データが画面からクリアされるならば、上記テーブルからも消す。

勿論、削除処理なども同じように。
そして、アプリを起動してログインした時、アプリのログオフした時、アプリを閉じた時、画面を開いた時、閉じた時など、完全に不要になる場面で、可能な限り上記テーブルから自分のデータは削除する。

そしてマスターユーザーでは、そのテーブルを操作してロックを外せるようにするといいかと思います。
もうデータがなくなってるとか、マジでまさにデータ更新中だったりもするはずなので、こちらもUPDLOCKした上でDELETEがよいと思います。

そこまですれば、例えば作業中にPCがフリーズしたなどによってアプリが落ちた時、もう一度そのユーザーでログインするか、マスターユーザーで上記テーブルから情報を消せば、他者がロックされてたデータを扱えるようになるかと思います。

ざっくりテキトーにシーンと可能性を考えて記したものですので、導入する場合はかなり細かく考えて策定した上で導入してくださいね。
テストもしまくった方がいいです。
悲観的排他やそれに似た仕組みを構築するということは、往々にして、操作ができなくなって業務が止まった、至急、という話になりがちですので。
マスターユーザーは最終手段であって、まずよっぽどおかしな状況に陥らない限り正確にロック制御が保たれることが大前提です。

naktak · Answer

知恵袋の回答も確認しましたが、楽観的排他を推す説明ですね。
これは間違っていません。
悲観的排他は知恵袋の回答者も懸念している通り、問題があった時の悲惨さがリスキーだからです。

UI操作が挟まれるのにロックすべきではない、トランザクションは可能な限り狭い範囲であるべき、というのも間違っていません。

しかし今回は、記されているような問題も承知の上で、『他者が呼び出しているデータを参照させたくない』という要件があるからこそ、悲観的排他にならざるを得ないのです。
ただし、そのような要件でトランザクションを長く取り、DBのロック機構を素直に使ってしまうと、その問題が頻発する可能性が高く、恐らくあなたは数日徹夜とかなります。
ですから、テーブルで管理、トランザクションは急に死ねばロールバックされるから、最悪テーブルのデータ消せば直せる、ということになりますね。

どれだけの人が操作するのか、リアルタイムなのか定期バッチなのかなど、シーンによって楽観的排他、悲観的排他はそれぞれ使いどこが違ったりするので、どちらが正しい、どちらが間違っているということはありません。
ただ、取り上げられている問題がまずいい方向に向かないので、積極的に悲観的排他を取り入れる人はいない、というところでしょうか。

naktak · Answer

不特定多数のユーザーが操作するのに、ある情報を誰かが呼び出している時、同一制御においては、他者は一切その情報を呼び出せなくていいんでしょうか？
UPDLOCKは、NOWAITも一緒に付与しないと、他者のトランザクションが終わるまでロックを取得できないので待ちを食らうかと思います。
綺麗なのは、仮に1分のトランザクションタイムアウトだとしても、それを待たず、他者が編集中だから少し待ってね、という促しの上、呼出を中止し、トランザクションを破棄するような動作があった方が良いのでは。

コマンドタイムアウトの関係性もありますが、何もしていないと、約1分ひたすら待った挙句、自分も結果的にトランザクションタイムアウトを食らう可能性もあります。（勿論、トランザクションが完結するまでの処理の重さに起因します）

トランザクションタイムアウトを既定のままだと、果たして1分で呼び出しから更新までを行えるのか、という問題もあるかと思います。
トランザクションタイムアウトを延ばせば延ばすほど、先述した問題に直面します。

さて、質問への回答ですが

質問①
悲観的排他としては正しいやり方です。
楽観的排他をNGとした経緯があるなら、悲観的排他としての逃げ道も作る必要があります。
（まぁそれが質問②なのでしょう）

質問②
TransactionScopeを利用しなければ、トランザクションは終了するまで保持し続けます。
https://docs.microsoft.com/ja-jp/dotnet/api/system.transactions.transactionscope?view=net-5.0
https://csharp.sql55.com/database/how-to-set-transaction-scope-timeout.php

SqlServerのトランザクションについて。 SqlServerのデータを取得・更新・追加するGU

タイムアウトはしたくないんですね。

知恵袋の回答も確認しましたが、楽観的排他を推す説明ですね。

不特定多数のユーザーが操作するのに、ある情報を誰かが呼び出している時、同一制御においては、他者は一切その情報を呼び出せなくていいんでしょうか？

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング