.NETエンタープライズ Webアプリケーション開発技術大全 SQL Serverのロック管理 マイクロソフト コンサルティング本部　赤間 信幸 2005/03/01

Page1 Page2 Page3 Page4

2.2 分離レベルによるロック挙動の変化

　SQL Serverでは、同時実行制御機能を弱めるパラメータとして4種類の分離レベルを利用することができる。この分離レベルパラメータの変更により、以下の2つが変化する。

• ロックを保持し続ける期間
• インデックスキーに対して発生するロックの種類

　これらについて以下に解説する。なお、「第1章　トランザクションと分離レベル」と見比べながら読み進めれば、より理解が深まるだろう。

2.2.1 分離レベルによるロック保持期間の変化

　まず、ロックが保持される期間は、分離レベルとロックの種類によって変化する（表2-2）。

　例えばSERIALIZABLE分離レベルを利用する場合、対象リソースに対してかけられたロックはトランザクションコミット（またはアボート）まで確保され続ける。これにより、他のユーザによる不正な処理が完全に防止される。

　しかし低い分離レベルを利用する場合には、これらのロックが早期解放されたりすることがある。まずREAD COMMITTED分離レベルの場合には、共有ロック（S-Lock）がトランザクションコミットまでは保留されず、S Q L 文実行直後には解放されてしまう。またREADUNCOMMITTEDの場合には、ロックそのものを立てずにデータを読み取るという特殊な動作が行われる。

分離レベル	共有ロック（S）	更新ロック（U）	排他ロック（X）
SERIALIZABLE	トランザクションコミットまで保持	トランザクションコミットまで保持	トランザクションコミットまで保持
REPEATABLE READ	トランザクションコミットまで保持	トランザクションコミットまで保持	トランザクションコミットまで保持
READ COMMITTED	SQL 文実行終了直後に解放まで保持	トランザクションコミットまで保持	トランザクションコミット
READ UNCOMMITTED （＝他者のロックを無視）	ロックそのものを立てないまで保持	トランザクションコミットまで保持	トランザクションコミット
表2-2 分離レベルによるロック保持期間の変化

　authorsテーブルのある行の著者名を読み取って書き換える処理を例に取り、分離レベルによるロック挙動の違いを確認してみよう※13。

*13 SERIALIZABLE分離レベルを用いた場合にはクエリによってロックの種類が変化するため、ここではREPEATABLE READ分離レベルを利用して解説する。なお、SEMのリソース部に表示される内部ハッシュキー値やページ番号は環境によって変化するため、各自で演習する場合に値が異なっていても特に気にする必要はない。

A. REPEATABLE READ分離レベルの場合

　すでにクエリアナライザなどが起動している場合にはいったんクエリアナライザ内のすべてのウィンドウを閉じてから、新規の接続を作り、そこからリスト2-2の手順に従ってトランザクション処理を進めてみる。ここではトランザクション処理を一気に行わず、ステップバイステップでSQL文を実行し、そのつどロック状態をSEMから確認してみて頂きたい。この実験結果を図2-11に示す。

Transact-SQL まず以下のクエリをクエリアナライザから実行し、pubsデータベースに移動し、トランザクションを開始する。 　（入力後、F5キーを一度だけ押して実行する。） USE pubs SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION この状態で、SQL Server Enterprise Manager(SEM)を開き、ロックの状態を確認する。 　（最新の利用状況に更新した上で確認する） 確認したら、で入力したクエリをすべて消去してから、以下のクエリを入力し、実行する。 　（入力後、F5キーを一度だけ押して実行する。） SELECT * FROM authors WHERE au_id = '172-32-1176' この状態で、SQL Server Enterprise Managerを開き、ロックの状態を確認する。 　（最新の利用状況に更新した上で確認する） 確認したら、で入力したクエリをすべて消去してから、以下のクエリを入力し、実行する。 　（入力後、F5キーを一度だけ押して実行する。） UPDATE authors SET phone = '408 496-7224' WHERE au_id='172-32-1176' この状態で、SQL Server Enterprise Managerを開き、ロックの状態を確認する。 　（最新の利用状況に更新した上で確認する） 確認したら、で入力したクエリをすべて消去してから、以下のクエリを入力し、実行する。 　（入力後、F5キーを一度だけ押して実行する。） COMMIT TRANSACTION この状態で、SQL Server Enterprise Managerを開き、ロックの状態を確認する。 　（最新の利用状況に更新した上で確認する）

リスト2-2 REPEATABLE READ分離レベルを利用した場合のロックの挙動の確認

　

図2-11 REPEATABLE READ分離レベルを利用した場合のロックの挙動

　表2-2にある通り、確かにSQL文を実行する際に取得されたロックがトランザクション終了時まで残存し続ける様子が見て取れる。

B. READ COMMITTED分離レベルの場合

　今度はステップをREAD COMMITTED分離レベルに変えて、リスト2-2の一連の作業を実施してみて頂きたい。その結果を図2-12に示す。

図2-12 READ COMMITTED分離レベルを利用した場合のロックの挙動

　REPEATABLE READ分離レベルを利用した場合には、共有ロック、排他ロックのいずれもトランザクション終了時まで残存した。しかし表2-2に示した通り、READ COMMITTED分離レベルを利用した場合、共有ロックについてはSQL文の実行終了直後にロックが解放されてしまう。このため、のタイミングでは共有ロックを観測することができない。

　以上の例をイラストにまとめると、図2-13のようになる。図2-11や図2-12の実験結果と比較して、じっくり確認してみて頂きたい※14。

*14 なおこの実験では、やといった、SQL文を実行している最中のロックの状態を確認することができないので、READ COMMITTED分離レベル時に共有ロックがかけられていることを確認できない。SQL文実行時に共有ロックが必要となることは、後述するブロック現象を起こしてみると確認できる。

　

図2-13 分離レベルによるロック残留の挙動の変化

　図2-13から分かるように、READ COMMITTED分離レベルを用いると、の期間中に共有ロックを解放してしまう。このため、他のトランザクション（他のユーザ）によるデータ更新が可能となり、ロストアップデートなどのデータ不整合問題が生じる危険性がある。

　これに対してREPEATABLE READやSERIALIZABLE分離レベルを利用した場合には、共有ロックが残存し続けるため、現在利用しているデータが他のトランザクションから更新されることはなくなる。

INDEX
	.NETエンタープライズWebアプリケーション 開発技術大全
	SQL Serverのロック管理
	1．SQL Serverのロックメカニズムの基礎
	2．SQL Serverの内部構造とロック対象となるリソース
	3．分離レベルによるロック保持期間の変化
	4．分離レベルによるインデックスキーに対するロックの種類の変化

　

「.NETエンタープライズWebアプリケーション開発技術大全」

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）