トランザクション周りのQ&A

[nishi.74322014]

Q&A履歴を纏めました。

（１）デッドロックの件（１）

　　現在、PJでは更新日付を更新条件に用いることで楽観排他を実現しようとしていますが、開発者から

　　「複数人で同じデータを同時に大量更新した際にデッドロックが発生した経験があるため、必ずSELECTロックすべき」

　　との意見がありました。SQL Serverに限らず、このようなケースでデッドロックが発生した経験はありますか？

　　→　SQL Serverでデッドロックという話は、

　　　　Oracleユーザがロック法で実装されたRDBMSを使用する際に感じる一般的な問題だと思います。

　　　　最近は、色々なRDBMSで多バージョン法（MVCC)モードの動作も可能になってきてはいます。

　　　　詳しくは、下記URLを参照下さい。

　　　　・DBMSのロック・分離戦略と同時実行制御

　　　　　https://techinfoofmicrosofttech.osscons.jp/index.php?DBMS%E3%81%AE%E3%83%AD%E3%83%83%E3%82%AF%E3%83%BB%E5%88%86%E9%9B%A2%E6%88%A6%E7%95%A5%E3%81%A8%E5%90%8C%E6%99%82%E5%AE%9F%E8%A1%8C%E5%88%B6%E5%BE%A1

　　　　対策として、確かに、SELECTを更新ロックするのは、

　　　　デッドロック回避対策のうちの１つではありますが、全てではありません。

（２）デッドロックの件（２）

　　現状、解決策として３つの方式があると思っていますが、

　　Open棟梁とSQL Serverを用いた実績として、どの方式が一般的に採用されているか分かりますか？

　　① 更新ロックを取得する方式

　　② READ_COMMITTED_SNAPSHOT 方式

　　③ そもそも直列(Serializable)方式

　　→　③ は共有ロックをホールドするので、デッドロックの解決にならず、むしろ悪化します。

　　　　なので、①、②だと思います。詳細は追加された参考URLを確認下さい。

　　　　・SQL Server でのデッドロック

　　　　　https://techinfoofmicrosofttech.osscons.jp/index.php?SQL%20Server%20%E3%81%A7%E3%81%AE%E3%83%87%E3%83%83%E3%83%89%E3%83%AD%E3%83%83%E3%82%AF

（３）一意制約違反が発生した場合

　　DBへの書き込み時に一意制約違反が発生した場合のみ、表示するメッセージを変えるという処理を実装したいと考えています。

　　DB書き込み時のエラーでは全てSqlExceptionが投げられていますが、エラーコード等でエラーの原因を判断することは可能でしょうか。

　　→　データプロバイダによって型が違いますが、SQL Serverの場合、

　　　　確かに、System.Data.SqlClient.SqlExceptionのようです。

　　　　以下でハンドルして、システム例外か業務例外に振り替えるとイイかと思います。

　　　　・ システム例外に振り替える。

　　　　　https://github.com/OpenTouryoProject/OpenTouryo/blob/02-30/root/programs/CS/Frameworks/Infrastructure/Business/Business/MyFcBaseLogic.cs#L546

　　　　・ 業務例外に振り替える。

　　　　　https://github.com/OpenTouryoProject/OpenTouryo/blob/02-30/root/programs/CS/Frameworks/Infrastructure/Business/Business/MyFcBaseLogic.cs#L534

　　　　なお、「ex.Number = 2627」（一意エラー）だそうです。

　　　　http://d.hatena.ne.jp/lpczclt/20081028/1223351983

（４）入れ子のトランザクション

　　SQLServerのトランザクション管理について、１つのトランザクション内に入れ子型でトランザクションを作成した場合、

　　内側のトランザクションだけコミットしたいといった要件があっても、外側のトランザクションをロールバックした場合は、

　　内側も合わせてロールバックされてしまう認識で良いでしょうか。

　　参考URL: https://technet.microsoft.com/ja-jp/library/ms189336.aspx

　　上記要件を実現しようとしたときは、別コネクションを作成して

　　トランザクションを作成する必要がある認識で良いでしょうか。

　　→　 全体をコミットするには、

　　　　（１）親をコミットするか、

　　　　（２）子でコミット・ロールバックを行う場合、

　　　　　　　親・子すべてをコミットする

　　　　　必要があります。

　　　　　※ 子トランザクションのロールバックは全体のロールバックに繋がります。

　　　　なので、その要件（内側のトランザクションだけコミットしたいといった要件）

　　　　は、実現不可能であり、トランザクションを別にする必要があると思います。

　　　　トランザクションを別にする場合、MyFcBaseLogic.UOC_ConnectionOpenでDamを2つ生成するか、

　　　　https://github.com/OpenTouryoProject/OpenTouryo/blob/develop/root/programs/CS/Frameworks/Infrastructure/Business/Business/MyFcBaseLogic.cs#L113

　　　　別途、ビジネス・ロジック内でDamを生成して下さい。

　　　　入れ子構造のトランザクション

　　　　https://technet.microsoft.com/ja-jp/library/ms189336.aspx

　　　　FAQ - B層フレームワーク - Open 棟梁 Wiki > トランザクション管理

　　　　https://opentouryo.osscons.jp/index.php?FAQ%20-%20B%E5%B1%A4%E3%83%95%E3%83%AC%E3%83%BC%E3%83%A0%E3%83%AF%E3%83%BC%E3%82%AF#m12931af"

（５）中間コミットを実現する方法

　　Open棟梁を使って中間コミットを実現する方法があるか、ご教示を頂けませんでしょうか？

　　（なければ、ASP.NETの機能を直接呼ぶ方法でも構いません。）

　　→　以下のように手動トランザクション制御可能です。

　　　　this.GetDam().CommitTransaction()

　　　　this.GetDam().BeginTransaction()

　　　　※ BeginTransaction()しないとエラーになります。

　　　　　若しくはthis.SetDam()でNullを設定しても良いかも。

　　　　　DamがNullの場合は、何もしないで終わる。

　　　　FAQ - B層フレームワーク - Open 棟梁 Wiki > トランザクション管理 > 分割コミットなどは可能か？

　　　　https://opentouryo.osscons.jp/index.php?FAQ%20-%20B%E5%B1%A4%E3%83%95%E3%83%AC%E3%83%BC%E3%83%A0%E3%83%AF%E3%83%BC%E3%82%AF#nb4e23fd

　　　　https://github.com/OpenTouryoProject/OpenTouryo/blob/develop/root/programs/CS/Frameworks/Infrastructure/Framework/Business/BaseLogic.cs#L215

（６）SQL Serverのトランザクションの仕様について

　　調査を行ったのですが、以下のような結果になったのですが間違いないでしょうか？

　　・トランザクションを開始せずにDB更新を行う

　　　→デフォルトで即時コミットになり、DB更新直後に値がコミットされる

　　・トランザクションを開始してからDB更新し、コミット、ロールバックを行わない

　　　→コネクションがクローズされるときに自動でロールバックされる

　　　　この時、コネクションがクローズされるまでテーブルがロックされる

　　→　トランザクションを開始しない規定の動作は、「自動コミット モード」と言い、

　　　　・クエリが発行された時点で「既定の分離レベル」でトランザクション開始

　　　　・クエリが完了した時点でトランザクションを自動コミット

　　　　と言った感じの動作になるかと思います。

　　　　SQL Serverに限らず、多くのRDBMSで似たような動作になると思います。

（７）SQL Serverの自動コミットモードの仕様について

　　自動コミットモードはどこかの定義ファイルで記載されているものでしょうかそうなります。

　　→　「SET IMPLICIT_TRANSACTIONS が ON の場合は、

　　　　接続は暗黙のトランザクション モードに設定されます。

　　　　OFF の場合、接続は自動コミット トランザクション モードに戻ります。」とあります。

　　　　sp_configure 'user options'で設定できるようですが、

　　　　「dblib ネットワーク ライブラリ接続の場合、

　　　　ステートメントの実行時にトランザクションを暗黙的に開始するかどうかを制御します。

　　　　IMPLICIT_TRANSACTIONS の設定は、ODBC または OLEDB 接続には影響を与えません。」

　　　　とあります。

　　　　トランザクションの自動コミット

　　　　https://technet.microsoft.com/ja-jp/library/ms187878.aspx

　　　　user options サーバー構成オプションの構成 | Microsoft Docs

　　　　https://docs.microsoft.com/ja-jp/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option