#はじめに
前回記事を投稿してから約1年。今年の正月はMCSA取得を念頭に、消化不良の分野を攻略することにしました。まず第一弾は分離レベルとロックの関係です。実はMeasureUpというE-Learningサイトで勉強しているのですが、ここで解説されている内容・MS公式サイトの記述・私が実機でテストした内容がそれぞれ食い違っており、どうしても理解に苦しむというのが投稿の理由だったりします。ははは。
#E-Learningのサイトでの例題の要約
- データベース HumanResources
- テーブル dbo.Employees 主キーはEmployeeID int
- 以下のSQL文を実行後のロック状態を回答せよ
- SET TRANSACTION ISOLATION LEVEL [X];
- BEGIN TRANSACTION;
- SELECT EmployeeName From dbo.Employee Where EmployeeID = 12;
#E-Learningのサイトの解説
- [X]がSNAPSHOTまたはSERIALIZABLEの場合
- A shared lock on the HumanResources database.
- An intent shared lock on the dbo.Employees table.
- An intent shared lock on the page holding the row for EmployeeID 12.
- A key range lock for the key value 12.
- [X]がREPEATABLE READの場合
- A shared lock on the HumanResources database.
- An intent shared lock on the dbo.Employees table.
- An intent shared lock on the page holding the row for EmployeeID 12.
- [X]がREAD UNCOMMITTEDまたはREAD COMMITTEDの場合
- A single shared lock on the HumanResources database.
#E-Learningで理解できる部分
- SERIALIZABLEの場合はファントム防止のためにKey range lockがかかる。
- REPEATABLE READの場合はトランザクション開始時に存在するレコードのみを担保するため、key range lockはかからず、EmployeeID=12に該当するレコードが存在するpageに対するlockに留まる。（同一key rangeでもロック対象外のpageに対するInsert, deleteが発生し得る）
- READ COMMITTEDの場合は、読み取りで共有ロックが発生する。
#E-Learningで理解できない部分
- SNAPSHOTの場合はRead Committedと同様と理解してましたが、ここではSERIALIZABLEと同じ結果になると書かれています。でも実際そうなったら使う意味ないんじゃないのかなと思うのですが．．．
- READ UNCOMMITTEDの場合はロックを要求しないと今まで理解してました。公式にもそう書いてあります。しかしE-LearningのサイトではDBに共有ロックがかかると書いてあります。うーん．．．
#実験
- 例題と同じ状況を作り、各分離レベルで実行後にsp_lockを実行して状況を確認しました。
##READ UNCOMMITTEDの場合
- E-Learningに書かれているとおりDBに対して共有ロックが発生しました。
##READ COMMITTEDの場合
- DBに対して共有ロックが発生しました。
- DBに対してはISでTABにSだと予想していましたが、違っていました。
##REPEATABLE READの場合
- keyロックまで発生しました。これは理解不能です。
##SNAPSHOTの場合
-
事前準備としてDBのプロパティで「スナップショット分離を許可」をTrueに、「Is Read Committed Snapshot On」をFalseにしています。
-
READ COMMITTED, UNCOMMITTEDと同じ結果となりました。
##SERIALIZABLEの場合
- 予定どおりkeyロックまで発生しました。
#総括
-
公式サイトに書かれていること、E-Learningの例題と回答、私の理解、そして実験結果のいずれも異なる部分があり、あまり理解は深まりませんでした。
特に分からない部分は次の３点です。 -
REPEATABLE READの実験でkey lockが発生したこと。
-
SNAPSHOTについてE-LearningではSERIALIZABLEと同じでkey lockまで発生すると回答していること。
-
READ UNCOMMITTEDでも共有ロックが発生したこと。
-
残念ながらE-Learningでは回答のみ掲載されていて、そのメカニズムの解説などは一切書かれていないんですよね。ということでいったんここで終わります。