JSSUG(Japan SQL Server User Group):第5回 SQL Server 2017勉強会の資料
第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
View Slide
本日の発表の流れ1.トランザクション分離レベルの違いによるSQL Serverの動作の違いについて1.1.トランザクション分離レベルの種類1.2.READ UNCOMMITED1.3.READ COMMITED1.4.READ COMMITED SNAPSHOT1.5.REPEATABLE READ1.6.SERIARIZABLE1.7.SNAPSHOT1.8.トランザクション分離レベルの確認方法1.9.トランザクション分離レベルの変更方法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).aspx2018/04/21 JSSUG(Japan SQL Server User Group) 25