SQLServer利用時はトランザクション分離レベルに気を付けて
DBにSQLServerを使用している場合になりまして、事象と対応方法については簡単なのですが、私には詳しい説明が難しいため、先に簡潔に現象を説明します。
「長い更新処理が行われている間、別のユーザーがそのテーブルにアクセスするとデータが取得できない」
<事象の再現>
※SQLServerをデフォルトのまま使用していると再現できます。
Azure for SQLServerの場合は対応されているので、再現しません。
①SQLServer Management Studioのクエリから一つのテーブルをUpdateを行います。
その際、トランザクションを開始してロックしたままの状態にしてみます。
BEGIN TRAN
Update [KM01_KOKYAKU] Set [KM01_REV_NO] = 7
Where [KM01_KOKYAKU_CD] = 'CC0002'
※Update文は適時調整してください。
この状態だとまだデータは確定されておらず、CommitかRollbackするまではロック状態です。
②ロック状態のままGeneXusで生成したシステムから、
上記のテーブルの一覧画面や登録画面を開いて、データにアクセスしてみてください。
データが表示されないはずです。(画面が表示されないことも)
データがロックされているため、Selectできない状態となります。
③次にSQLServer Management StudioのクエリからCommitを実行してみてください。
すると、GeneXusの画面でデータが表示されると思います。
<原因>
SQLServerのトランザクション分離レベルがデフォルトだと「READ COMMITTED」であること、
GeneXusが生成するSQL文にはWITH(NOLOCK)が記述されていないことが合わさって上記の現象となります。
GeneXusは以前はSQLServerの場合にSQL文にWITH(NOLOCK)をつけていたのですが、
それだとダーティリードが発生してしまうため、WITH(NOLOCK)を外すように修正されました。
GeneXus コラボDB
http://www.gxsupport.jp/gxfaq/faqdisplay.aspx?2961
GeneXus日本語SAC
https://sac.genexus.jp/viewtr_sac.aspx?30566,
WITH(NOLOCK)がついていると高速であり、Selectできない状態にならないメリットはあるのですが、
他者がCommitしていないデータをSelectできてしまうので、業務システムとしては致命的です。(ダーティリード)
<対応方法>
上記のSAC 30566にも記載がありますが、トランザクション分離レベルを「READ_COMMITTED_SNAPSHOT」にすることで、Select時にロックされているかどうかは関係なくなります。
SQL
ALTER DATABASE <データベース名> SET READ_COMMITTED_SNAPSHOT ON
※DBにアクセスがあるとSQLが終了しないので、DBを再起動してから実行するのが良いです。
ちなみにAzure for SQLServerは、デフォルトで「READ_COMMITTED_SNAPSHOT」です。
トランザクション分離レベルの「READ_COMMITTED_SNAPSHOT」については
こちらのURLが参考になるかと思います。
SQL Serverのスナップショット分離レベル導入によるデータ基盤連携の課題解決
https://techblog.zozo.com/entry/sqlserver-transaction-isolation-level-snapshot
ただし、「READ_COMMITTED_SNAPSHOT」にすることによって、
多少HDDの容量やSelect時の負荷といった問題が発生しますが、
誰かが更新している間はSelectで待ち状態になることに比べれば些細な影響だと思います。
コメント
コメントを投稿