Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
JSSUG(Japan SQL Server User Group):第5回 SQL Server 2017勉強会
Search
masaru1006
April 21, 2018
Technology
0
850
JSSUG(Japan SQL Server User Group):第5回 SQL Server 2017勉強会
JSSUG(Japan SQL Server User Group):第5回 SQL Server 2017勉強会の資料
masaru1006
April 21, 2018
Tweet
Share
More Decks by masaru1006
See All by masaru1006
JSSUG(Japan SQL Server User Group):第8回 SQL Server 2017勉強会
masaru1006
0
670
JSSUG(Japan SQL Server User Group):第2回 SQL Server 2017勉強会
masaru1006
0
380
JSSUG(Japan SQL Server User Group):第4回 SQL Server 2017勉強会
masaru1006
0
450
Other Decks in Technology
See All in Technology
2024/4/26 コンピュータ歴史博物館解説告知
toshi_atsumi
0
200
テストプロセスで大事にしていること #jasstnano
makky_tyuyan
0
130
OpenTelemetry を使ったトレースエグザンプラーの活用 / otel-trace-exemplar
k6s4i53rx
2
640
自動生成を活用した、運用保守コストを抑える Error/Alert/Runbook の一元集約管理 / Centralized management of Error/Alert/Runbook to minimize operational costs using automated code generation
biwashi
9
2.1k
Garoon 開発チーム / Garoon development team
cybozuinsideout
PRO
2
2.9k
オーナーシップを持つ領域を明確にする
konifar
11
2.6k
Janus
bkuhlmann
1
490
Tebiki株式会社 エンジニア採用資料
tebiki
0
4.1k
TransitGatewayの基礎
toru_kubota
0
230
反実仮想機械学習とは何か
usaito
PRO
7
2.3k
4年前、あるじゃん老害エンジニアLT合戦に登壇、米国西海岸コンピュータ歴史博物館体験記の続編
toshi_atsumi
0
200
コンパウンドスタートアップのためのスケーラブルでセキュアなInfrastructure as Codeパイプラインを考える / Scalable and Secure Infrastructure as Code Pipeline for a Compound Startup
yuyatakeyama
3
2.7k
Featured
See All Featured
Understanding Cognitive Biases in Performance Measurement
bluesmoon
6
990
Designing for humans not robots
tammielis
247
25k
The Pragmatic Product Professional
lauravandoore
24
5.8k
RailsConf 2023
tenderlove
2
530
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
119
38k
We Have a Design System, Now What?
morganepeng
42
6.7k
Happy Clients
brianwarren
91
6.4k
Reflections from 52 weeks, 52 projects
jeffersonlam
344
19k
YesSQL, Process and Tooling at Scale
rocio
163
13k
From Idea to $5000 a Month in 5 Months
shpigford
377
45k
jQuery: Nuts, Bolts and Bling
dougneiner
59
7.1k
Git: the NoSQL Database
bkeepers
PRO
422
63k
Transcript
第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
本日の発表の流れ 1.トランザクション分離レベルの違いによるSQL Serverの動作の違いについて 1.1.トランザクション分離レベルの種類 1.2.READ UNCOMMITED 1.3.READ COMMITED 1.4.READ COMMITED
SNAPSHOT 1.5.REPEATABLE READ 1.6.SERIARIZABLE 1.7.SNAPSHOT 1.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).aspx 2018/04/21 JSSUG(Japan SQL Server User
Group) 25