【SQL Server】トランザクション分離レベルを設定する

SQL
スポンサーリンク

SQL Serverにはトランザクション分離レベルというものがあり、結構システムのタイムアウトやデッドロックなどはこの設定を変更することで発生しなくなったりすることもあるので一度確認しておく必要があると思います。
ちなみに私が保守しているシステムはREAD_COMMITTED_SNAPSHOTに設定しています。

データベースのトランザクション分離レベルを確認する。

対象のデータベースのトランザクション分離レベルを確認するには、以下のSQLを実行します。

DBCC USEROPTIONS

Set Option のisolation levelに設定されている値がトランザクション分離レベルです。SQLServer 2017では,デフォルトはREAD_COMMITTEDになっています。
isolationレベル

トランザクションの同時実行時の問題

同時に複数のトランザクション(BEGIN TRAN)を実行する場合に、実行タイミングによって
以下の4種類の問題が発生する可能性があります。

更新データの損失

トランザクションAで更新を確定したデータを、トランザクションBの更新によって
上書きされてしまう現象

ダーティーリード

トランザクションAのデータを確定させる前に、トランザクションBがそのデータを読み取ってしまう現象

反復不可能読み取り

同じトランザクション内で同じデータを読み取った場合に値が都度違ってしまう現象。
(Aが参照したデータをBが更新してしまい、再度Aが同じデータを読み取る際に発生)

ファントムリード

トランザクションの中で読み込んだデータ行が削除される、または存在していなかったデータ行が追加されてしまう現象。(Aが参照しているデータに対してBがデータ削除や挿入を行った場合に発生する。)

トランザクション分離レベルの種類

上記の4つの問題現象から保護するレベルのことで、ロック方式の分離レベルと、行バージョン方式の分離レベルが使用できます。

SQL Server2017のデフォルト設定のREAD COMMITTEDは、ロック方式に分類されます。
この方式では、[更新データの損失]と[ダーティリード]は発生しないが[反復不可能読み取り]と[ファントムリード]は、発生する可能性があります。

ロック方式のトランザクション分離レベル

ロック方式のトランザクション分離レベルは全部で4種類のレベルがあります。

1.READ UNCOMMITTED

分離レベルの中で一番制限が緩いレベルです。基本的にロックをかけずに読み取りを行ってしまうので、複数のトランザクションが同時に実行される可能性があるシステムでは、データの整合性が保てないのでこの設定を行っているシステムはあまりないと思います。よく現在のデータ状況を
確認するときにSELECT文にNOLCKを使いますがそれと同じような動きになります。

2.READ COMMITTED

SQL SERVER 2017では、既定の分離レベルで他のトランザクションで変更され、またコミットされていないデータ行は読み取れないようにしている。現在のトランザクションが変更していないデータ行は、ロックを掛けず他のトランザクションから変更できてしまうので[反復不可能読み取り]と[ファントムデータ]が発生する可能性がある。

3.REPEATABLE READ

他のトランザクションで変更され、未コミットのデータ行を読み取れないようにしつつ、現在のトランザクションが完了するまでは、読み取ったデータは他のトランザクションに更新されないようにしている。同時実行性は上に比べて落ちてしまうのでシステムによっては更新を待つロックタイムアウトが発生する可能性が高くなる。

4.SERIALIZABLE

4つの中で最も同時実行性が低いが、トランザクションの同時実行時問題は発生しない。

行バージョン方式のトランザクション分離レベル

行バージョン方式の分離レベルには、以下の2種類があります。

READ COMMITTED SNAPSHOT

ロック方式ではなく、データベースエンジンで行バージョン管理が行われ、他のトランザクションによるデータ更新を防ぐためのロックは発生しない。SQLのステートメントが実行開始時のデータスナップショットを生成する。他のトランザクションは、スナップショットからデータを取得してくるので同時実行性を損なわずに一貫性のあるデータを参照できる。
READ COMMITTEDでロックを使わずスナップショットを生成していると考えると良いです。

SNAPSHOT

データベースエンジンで行バージョン管理が行われ、他のトランザクションによるデータ更新を防ぐためのロックは発生しない。SQLのステートメントが実行開始時のデータスナップショットを生成する。他のトランザクションは、トランザクションが完了するまでは、スナップショットを参照するので同時実行性は損なわずに一貫性のあるデータを参照できる。

SERIALIZABLEでロックを使わずスナップショットを生成していると考えると良いです。

行バージョン方式のトランザクション分離レベルでの注意点

上の説明でもあるように、行バージョン方式にしておけばロックの概念がなくなるからロックタイムアウトは発生せず、READ COMMITTED SNAPSHOTにしておけば間違いないと思うかもしれませんが、以下の注意点があります。必ず変更するときはチェックしておきましょう。

  • 行バージョン管理はtempDBで管理するのでtempDBが肥大化する可能性がある。
  • 行バージョン管理はtempDBへの処理が大きく、オーバーヘッドも大きいのでCPUの負荷も高くなる。
  • ロックが発生しないので読み込みには有利だが更新/削除などは、むしろ遅くなる。

私が保守しているシステムは、オンライン操作とバッチ処理が重なり、ロックタイムアウトらしきエラーが頻発していましたが、サーバーの新しく更新した際に、READ COMMITTED SNAPSHOTにしてシステムを利用したところロックタイムアウトが発生しなくなりました。

READ COMMITTED SNAPSHOTを設定する方法

行バージョン方式のトランザクション分離レベルを設定するには以下のSQLを実行します。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ALTER DATABASE <データベース名>
SET READ_COMMITTED_SNAPSHOT ON

DBCC USEROPTIONSでisolation levelがread committed snapshotになっていればOKです。

まとめ

  • ロック待ちのタイムアウトが頻発するようであれば一度DBCC USEROPTIONSで分離レベルを確認する。
  • SQL Serverの分離レベルには、ロック方式と行バージョン方式がある
  • 行バージョン方式は、スナップショットを作成するためロック待ちが少なくなる可能性がある。

コメント

タイトルとURLをコピーしました