第5回 SQL Server 2017勉強会 トランザクション分離レベルの違いによる SQL Serverの動作の違い 開催日：2018年04月21日 主催：JSSUG(Japan SQL Server User Group) 2018/04/21 JSSUG(Japan SQL Server User Group) 1

本日の発表の流れ １．トランザクション分離レベルの違いによるSQL Serverの動作の違いについて １．１．トランザクション分離レベルの種類 １．２．READ UNCOMMITED １．３．READ COMMITED １．４．READ COMMITED SNAPSHOT １．５．REPEATABLE READ １．６．SERIARIZABLE １．７．SNAPSHOT １．８．トランザクション分離レベルの確認方法 １．９．トランザクション分離レベルの変更方法 2018/04/21 JSSUG(Japan SQL Server User Group) 2

トランザクション分離レベルの違いによる SQL Serverの動作の違い 2018/04/21 JSSUG(Japan SQL Server User Group) 3

トランザクション分離レベルの種類(1) トランザクション 分離レベル ロックの 種類 ダーティ リード ノンリピータブル リード (ファジーリード) ファントム リード READ UNCOMITTED 悲観的ロック 〇 〇 〇 READ COMMITED 悲観的ロック × 〇 〇 REPEATABLE READ 悲観的ロック × × 〇 SERIALIZABLE 悲観的ロック × × × READ COMMITED SNAPSHOT 楽観的ロック × 〇 〇 SNAPSHOT 楽観的ロック × × × 2018/04/21 JSSUG(Japan SQL Server User Group) 4

トランザクション分離レベルの種類(2)  デモを交えながら、各トランザクション分離レベルの動作を見ていきます。 2018/04/21 JSSUG(Japan SQL Server User Group) 5

READ UNCOMMITED(1)  他のトランザクションで変更されたが、まだコミットされていない行を、ステートメントで読取れる ように指定する。  READ UNCOMMITTEDレベルで実行されるトランザクションでは共有ロックが取得されな いため、現在のトランザクションで読取り中のデータが他のトランザクションで変更される事が 有る。また、READ UNCOMMITTEDレベルのトランザクションは排他ロックによってブロックさ れないため、他のトランザクションで変更された後まだコミットされていない行を、現在のトラン ザクションで読取る事が出来る。このオプションが設定されている場合に、コミットされていない 変更が読取られた場合、これをダーティ リードと呼ぶ。 2018/04/21 JSSUG(Japan SQL Server User Group) 6 実際に動作させて、 確認してみました。

READ UNCOMMITED(2)  READ UNCOMMITEDの動作  同じトランザクション内でコミット前のUPDATE文の結果を読込んでしまった。  ダーティリード発生。  同じトランザクション内でコミット後のUPDATE文の結果を読込んでしまった。  反復不能読取り(ファジーリード)発生。  同じトランザクション内でコミット後のINSERT文の結果を読込んでしまった。  ファントムリード発生。 2018/04/21 JSSUG(Japan SQL Server User Group) 7

READ COMMITED(1)  他のトランザクションで変更されたが、まだコミットされていないデータを、ステートメントで読み 取れないように指定する。  これにより、ダーティ リードを防ぐ事が出来る。現在のトランザクション内にある各ステートメント 間では、他のトランザクションによるデータの変更が可能である。この結果、反復不能読取り (ファジーリード)やファントム リードが発生する事が有る。  READ COMMITEDレベルは、SQL Serverの既定のオプションとなる。  READ_COMMITTED_SNAPSHOT がOFFに設定されている場合 (既定)、データベー ス エンジンでは共有ロックが使用され、現在のトランザクションでの読取り操作中に他のトラン ザクションによって行が変更されるのを防ぐ事が出来る。また、ステートメントが他のトランザク ションで変更された行を読取ろうとしても、そのトランザクションが完了するまでステートメントは ブロックされる。いつ解放されるかは、共有ロックの種類によって決まる。行ロックは、次の行が 処理される前に解放される。ページ ロックは次のページの読取り時に解放され、テーブル ロッ クはステートメントの終了時に解放される。 2018/04/21 JSSUG(Japan SQL Server User Group) 8 実際に動作させて、 確認してみました。

READ COMMITED(2)  READ COMMITEDの動作  同じトランザクション内でコミット前のUPDATE文の結果を読込まなくなった。  ダーティリードは発生しない。  UPDATE文の排他ロックにSELECT文がブロックされている。  既定の動作だと、Oracleの様に変更前の値を読込む訳ではない。  同じトランザクション内でコミット後のUPDATE文の結果を読込んでしまった。  ファジーリード発生。  同じトランザクション内でコミット後のINSERT文の結果を読込んでしまった。  ファントムリード発生。 2018/04/21 JSSUG(Japan SQL Server User Group) 9

READ COMMITED SNAPSHOT(1)  他のトランザクションで変更されたが、まだコミットされていないデータを、ステートメントで読み 取れないように指定する。  これにより、ダーティ リードを防ぐ事が出来る。現在のトランザクション内にある各ステートメント 間では、他のトランザクションによるデータの変更が可能である。この結果、反復不能読取り (ファジーリード)やファントム データが発生する事が有る。  READ_COMMITTED_SNAPSHOTがONに設定されている場合、データベース エンジン では行バージョン管理が使用され、各ステートメントでは、トランザクション全体で一貫性の有 るデータのスナップショットが使用される。このスナップショットは、ステートメント開始時点に存 在したデータのスナップショットである。ただし、他のトランザクションによるデータ更新を防ぐため のロックは使用されない。 2018/04/21 JSSUG(Japan SQL Server User Group) 10 実際に動作させて、 確認してみました。

READ COMMITED SNAPSHOT(2)  READ COMMITEDの動作  同じトランザクション内でコミット前のUPDATE文の結果を読込まなくなった。  ダーティリードは発生しない。  UPDATE文の排他ロックにSELECT文がブロックされなくなった。  Oracleの様に変更前の値を読込んだ。  同じトランザクション内でコミット後のUPDATE文の結果を読込んでしまった。  ファジーリード発生。  同じトランザクション内でコミット後のINSERT文の結果を読込んでしまった。  ファントムリード発生。 2018/04/21 JSSUG(Japan SQL Server User Group) 11

REPEATABLE READ(1)  他のトランザクションで変更されたが、コミットされていないデータをステートメントで読取れない ように指定すると共に、現在のトランザクションが完了するまでは現在のトランザクションで読 取ったデータを他のトランザクションで変更できないように指定する。  トランザクションの各ステートメントで読み取った全てのデータには共有ロックが設定され、トラ ンザクションが完了するまでその状態が保持される。これにより、現在のトランザクションで読み 取った行は、他のトランザクションで変更出来なくなる。  他のトランザクションでは、現在のトランザクションで実行されているステートメントの検索条件 に一致する行を新しく挿入することが出来る。その後、現在のトランザクションでステートメント が再試行された場合は新しい行が取得されるので、この結果ファントムリードが発生する。  共有ロックはステートメントが完了するたびに解除されるのではなく、トランザクションが完了す るまで保持されるため、同時実行性は既定の READ COMMITTED分離レベルよりも低く なる。 2018/04/21 JSSUG(Japan SQL Server User Group) 12 実際に動作させて、 確認してみました。

REPEATABLE READ(2)  REPEATABLE READの動作  同じトランザクション内でコミット前のUPDATE文の結果を読込まなくなった。  ダーティリードは発生しない。  UPDATE文の排他ロックにSELECT文がブロックされなくなった。  Oracleの様に変更前の値を読込んだ。(READ COMMITED SNAPSHOTの様な動作 になった)  同じトランザクション内でコミット後のUPDATE文の結果を読込まなくなった。  ファジーリードは発生しない。  SELECT文の共有ロックにUPDATE文がブロックされている。  同じトランザクション内でコミット後のINSERT文の結果を読込んでしまった。  ファントムリード発生。 2018/04/21 JSSUG(Japan SQL Server User Group) 13

SERIALIZABLE(1)  次の指定を行う。  他のトランザクションで変更されたが、まだコミットされていないデータは、ステートメントで読取れない。  現在のトランザクションが完了するまで、現在のトランザクションで読取ったデータは他のトランザクションで変 更出来ない。  現在のトランザクションが完了するまで、現在のトランザクションのステートメントで読取ったキー範囲に該当 するキー値の行は、他のトランザクションで新しく挿入出来ない。  トランザクションで実行される各ステートメントの検索条件に一致するキー値の範囲には、範囲ロック が設定される。これにより、現在のトランザクションで実行されるステートメントの処理対象となる行は ブロックされ、他のトランザクションによる行の更新や挿入ができなくなります。つまり、トランザクションの ステートメントが 2度実行された場合は、2度目も同じ行セットが読み取られます。範囲ロックはトラン ザクションが完了するまで保持されます。このオプションではキー範囲全体がロックされ、トランザクション が完了するまでその状態が保持されるので、これは最も制限の厳しい分離レベルとなる。 2018/04/21 JSSUG(Japan SQL Server User Group) 14 実際に動作させて、 確認してみました。

SERIALIZABLE(2)  SERIALIZABLEの動作  同じトランザクション内でコミット前のUPDATE文の結果を読込まなくなった。  ダーティリードは発生しない。  UPDATE文の排他ロックにSELECT文がブロックされなくなった。  Oracleの様に変更前の値を読込んだ。(READ COMMITED SNAPSHOTの様な動作 になった)  同じトランザクション内でコミット後のUPDATE文の結果を読込まなくなった。  ファジーリードは発生しない。  SELECT文の共有ロックにUPDATE文がブロックされている。  同じトランザクション内でコミット後のINSERT文の結果を読込んでしまった。  ファントムリードは発生しない。  SELECT文の範囲ロックにINSERT文がブロックされている。 2018/04/21 JSSUG(Japan SQL Server User Group) 15

SNAPSHOT(1)  トランザクションの各ステートメントで、トランザクション全体で一貫性のあるデータを読取るように指定 する。このデータは、トランザクション開始時点に存在したデータである。データの変更は、トランザクショ ンの開始前にコミットされたものだけが認識される。現在のトランザクションが開始されてから他のトラン ザクションによってデータが変更されても、現在のトランザクションで実行されるステートメントではデータ の変更は認識されない。このオプションでは、トランザクションの各ステートメントにおいて、トランザクショ ンの開始時点でコミットされていたデータのスナップショットを取得するのと同じ効果が得られる。  データベースが復旧中の場合を除いて、SNAPSHOTトランザクションではデータの読取り時にロックが 要求されない。SNAPSHOTトランザクションでデータが読取られている間も、他のトランザクションによ るデータの書込みはブロックされない。トランザクションでデータが書込まれている間も、SNAPSHOTト ランザクションではデータを読取る事が出来る。 2018/04/21 JSSUG(Japan SQL Server User Group) 16

SNAPSHOT(2)  データベースの復旧でロールバックが行われており、ロールバック中のトランザクションによってデータがロッ クされている場合に、そのデータの読み取りが試行されると、SNAPSHOTトランザクションではロックが 要求される。トランザクションのロールバックが完了するまで、SNAPSHOTトランザクションはブロックさ れ、許可が与えられると、直ぐロックは解除される。  SNAPSHOT分離レベルを使用するトランザクションを開始する前には、 ALLOW_SNAPSHOT_ISOLATIONデータベース オプションをONに設定する必要が有る。 SNAPSHOT分離レベルを使用するトランザクションで複数のデータベースのデータへアクセスする場 合は、各データベースで ALLOW_SNAPSHOT_ISOLATION をONに設定する必要が有る。 2018/04/21 JSSUG(Japan SQL Server User Group) 17 実際に動作させて、 確認してみました。

SNAPSHOT(3)  SNAPSHOTの動作  同じトランザクション内でコミット前のUPDATE文の結果を読込まなくなった。  ダーティリードは発生しない。  UPDATE文の排他ロックにSELECT文がブロックされなくなった。  Oracleの様に変更前の値を読込んだ。(READ COMMITED SNAPSHOTの様な動作 になった)  同じトランザクション内でコミット後のUPDATE文の結果を読込まなくなった。  ファジーリードは発生しない。  SELECT文の共有ロックにUPDATE文がブロックされなくなった。  同じトランザクション内でコミット後のINSERT文の結果を読込んでしまった。  ファントムリードは発生しない。  SELECT文の範囲ロックにINSERT文がブロックされなくなった。 2018/04/21 JSSUG(Japan SQL Server User Group) 18

トランザクション分離レベルの確認方法(1)  トランザクション分離レベルの確認方法  「DBCC USEROPTIONS」を実行 2018/04/21 JSSUG(Japan SQL Server User Group) 19

トランザクション分離レベルの変更方法(1)  READ UNCOMMITED  「SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED」を実行  READ COMMITED  「SET TRANSACTION ISOLATION LEVEL READ COMMITTED」を実行  REPEATABLE READ  「SET TRANSACTION ISOLATION LEVEL REPEATABLE READ」を実行  SERIALIZABLE  「SET TRANSACTION ISOLATION LEVEL SERIALIZABLE」を実行 2018/04/21 JSSUG(Japan SQL Server User Group) 20

トランザクション分離レベルの変更方法(2)  READ COMMITED SNAPSHOT  「ALTER DATABASE [DB名] SET READ_COMMITTED_SNAPSHOT ON」を実行  ※SQL Server Management Studioのデータベースのプロパティの 「Is Read Committed Snapshot On」でも設定可能  「SET TRANSACTION ISOLATION LEVEL READ COMMITTED」を実行  SNAPSHOT  「ALTER DATABASE [DB名] SET ALLOW_SNAPSHOT_ISOLATION ON」を実行  ※SQL Server Management Studioのデータベースのプロパティの 「スナップショット分離を許可」でも設定可能  「SET TRANSACTION ISOLATION LEVEL SNAPSHOT」を実行 2018/04/21 JSSUG(Japan SQL Server User Group) 21

トランザクション分離レベルの変更方法(3) 2018/04/21 JSSUG(Japan SQL Server User Group) 22

終わりに 2018/04/21 JSSUG(Japan SQL Server User Group) 23

終わりに  皆さん、ご清聴、ありがとうございました。  検証すると、色々と疑問が解けて有意義です！ 2018/04/21 JSSUG(Japan SQL Server User Group) 24

参考URL  http://memorandom-nishi.hatenablog.jp/entry/2016/11/03/143533  https://technet.microsoft.com/ja-jp/library/ms173763(v=sql.105).aspx 2018/04/21 JSSUG(Japan SQL Server User Group) 25