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 Serv...
Search
masaru1006
April 21, 2018
Technology
0
1.2k
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
790
JSSUG(Japan SQL Server User Group):第2回 SQL Server 2017勉強会
masaru1006
0
430
JSSUG(Japan SQL Server User Group):第4回 SQL Server 2017勉強会
masaru1006
0
500
Other Decks in Technology
See All in Technology
Clineを含めたAIエージェントを 大規模組織に導入し、投資対効果を考える / Introducing AI agents into your organization
i35_267
4
1.6k
より良いプロダクトの開発を目指して - 情報を中心としたプロダクト開発 #phpcon #phpcon2025
bengo4com
1
3.1k
Amazon ECS & AWS Fargate 運用アーキテクチャ2025 / Amazon ECS and AWS Fargate Ops Architecture 2025
iselegant
16
5.6k
mrubyと micro-ROSが繋ぐロボットの世界
kishima
2
310
Абьюзим random_bytes(). Фёдор Кулаков, разработчик Lamoda Tech
lamodatech
0
340
エンジニア向け技術スタック情報
kauche
1
260
なぜ私はいま、ここにいるのか? #もがく中堅デザイナー #プロダクトデザイナー
bengo4com
0
460
解析の定理証明実践@Lean 4
dec9ue
0
180
Understanding_Thread_Tuning_for_Inference_Servers_of_Deep_Models.pdf
lycorptech_jp
PRO
0
130
MySQL5.6から8.4へ 戦いの記録
kyoshidaxx
1
250
Yamla: Rustでつくるリアルタイム性を追求した機械学習基盤 / Yamla: A Rust-Based Machine Learning Platform Pursuing Real-Time Capabilities
lycorptech_jp
PRO
3
120
第9回情シス転職ミートアップ_テックタッチ株式会社
forester3003
0
240
Featured
See All Featured
Side Projects
sachag
455
42k
Documentation Writing (for coders)
carmenintech
72
4.9k
No one is an island. Learnings from fostering a developers community.
thoeni
21
3.3k
Chrome DevTools: State of the Union 2024 - Debugging React & Beyond
addyosmani
7
700
Automating Front-end Workflow
addyosmani
1370
200k
Writing Fast Ruby
sferik
628
61k
How to Create Impact in a Changing Tech Landscape [PerfNow 2023]
tammyeverts
53
2.8k
Product Roadmaps are Hard
iamctodd
PRO
54
11k
Designing Experiences People Love
moore
142
24k
10 Git Anti Patterns You Should be Aware of
lemiorhan
PRO
657
60k
A better future with KSS
kneath
239
17k
Performance Is Good for Brains [We Love Speed 2024]
tammyeverts
10
930
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