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

Page1 Page2 Page3 Page4

2.2.2 分離レベルによるインデックスキーに対するロックの種類の変化

　さて、REPEATABLE READとSERIALIZABLE分離レベルは、ロックの保持期間に関しては同じ動作をする。しかしこの２つの分離レベルでは、ファントムが発生しうるクエリを発行した際には、インデックスキーに対して異なるロックが取得される。表2-3に示すように、SERIALIAZBLE分離レベルを用いた場合に限り、インデックスキー範囲ロックと呼ばれる特殊なロックが取得される※15。

分離レベル	インデックスキーに対して取得されるロック	読み取り処理	更新予約処理	更新処理
SERIALIZABLE	インデックスキー範囲ロック	RangeS-S	RangeS-U	RangeX-X
REPEATABLE READ	インデックスキーロック	S	U	X
READ COMMITTED	インデックスキーロック	S	U	X
READ UNCOMMITTED	インデックスキーロック	（ロックしない）	U	X
表2-3 ファントムが発生しうるクエリを発行した際の分離レベルによるロックの種類の変化

*15 なお、ファントムが発生する恐れのないクエリを発行した場合、SERIALIZABLE分離レベルであっても通常のインデックスキーロックが獲得されることがある。例えばSERIALIZABLE分離レベルでSELECT * FROM authorsWHERE au_id = '172-32-1176'といったクエリを発行してみると、インデックスキーロックのみが取得されることが確認できる

　このインデックスキー範囲ロックは、当該行そのものをロックすると同時に、その直前のキーとの間へのデータ挿入や更新、削除を防止する特殊なロックになっている。これにより、SERIALIZABLE分離レベルではファントムが発生しないようになっている。

　実際に、authorsテーブルのある範囲の行を検索し、その範囲がロックされることを確認してみよう。

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

　すでにクエリアナライザなどが起動している場合にはいったんクエリアナライザ内のすべてのウィンドウを閉じ、新規の接続を作る。そこからリスト2-3の手順に従ってトランザクション処理を進めてみる。この実験結果を図2-4に示す※16。

*16 図2-14には各データ行に対する内部ハッシュキー値が示されているが、これは残念ながら簡単に調べることができない。ここでは別途、各行単位にロックをかけてSEM上から各行に対応する内部ハッシュキー値を確認し、メモした上で実験を行っている。

　

Transact-SQL まず以下のクエリをクエリアナライザから実行し、pubsデータベースに移動し、以下の処理を実施する。（入力後、F5キーを一度だけ押して実行する。） 　※この検索条件は、テーブル上の3 件にヒットする。 USE pubs SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT * FROM authors WHERE au_id BETWEEN '250-00-0000' AND '400-00-0000' この状態で、SQL Server Enterprise Manager(SEM)を開き、ロックの状態を確認する。 　（最新の利用状況に更新した上で確認する）

リスト2-3 REPEATABLE READ分離レベルを利用した場合に取得されるロックの種類

　

図2-14 REPEATABLE READ分離レベルでの実験結果

　実験結果から分かるように、共有ロックは検索条件にヒットした行のインデックスキーに対してのみかけられている。しかし、あくまで対象「行」に対してしかロックがかけられていないため、例えばau_idが‘269-53-6538’のデータや‘389-34-6854’のデータなどをこのテーブルへ挿入することができる。すなわち、ファントム問題が発生してしまう。

B. SERIALIZABLE分離レベルの場合

　今度は同じ実験を、SERIALIZABLE分離レベルで実施してみる。

　クエリアナライザからCOMMIT TRANSACTIONを実施してトランザクションを解放したのち、リスト2-3の手順の分離レベルをSERIALIZABLEに変更して、再度検索SQL文を実行して頂きたい。ロックの状況を観察すると、図2-15のようになる。

　この実験結果から分かるように、SERIALIZABLE分離レベルを利用した場合には、検索条件にヒットした行の周辺へのレコード挿入を防止する、インデックスキー範囲ロックと呼ばれる特殊なロックが取得される。この範囲ロックにより、ファントム問題が発生しなくなる。

　このインデックスキー範囲ロックは、手前のキーとの間へのレコード挿入を防止するという点を除けば、通常のインデックスキーロックと変わりがない。すなわち、以下の通りとなる。

• インデックスキー範囲ロックも、インデックスキーロックも、いずれもインデックスキー（＝行）に対してかかる。

• 共有ロック、更新ロック、排他ロックの3種類が存在し、ロックの互換性も先に示した表2-1と同じになる。

図2-15 SERIALIZABLE分離レベルでの実験結果

　SERIALIZABLE分離レベルを利用する場合には、インデックスキー範囲ロックと呼ばれる特殊なロックが利用されることによって、ファントム問題の発生が防止されている、とだけ覚えておけばよいだろう※17、※18、※19。

*17 インデックスキー範囲ロックは、完全にWHERE句条件にヒットするもののみをロックしているわけではない。例えば、au_id = ’249-85-3576’ というデータは、250-00-0000〜400-00-0000というWHERE句条件にヒットしていないが、挿入することができない。キー範囲ロックはこのような意味で完璧とはいえないが、WHERE句条件そのものを保存してデータ挿入のつどチェックする方法（このような方法を述語ロックと呼ぶ）に比べると圧倒的に小さいオーバヘッドで済む。ちなみにSQL Serverの旧バージョン（6.5以前）ではファントム発生防止のためにページやテーブル全体がロックされていた。これに比べると、インデックスキー範囲ロックであれば細かい粒度でのロックができる。

　

*18 ここでは簡単のため、クラスタ化インデックスのみを利用したキー範囲ロックについて解説したが、検索に利用されるインデックスの種類や有無によって、インデックスキー範囲ロックのかかり方が変化する。場合によっては、検索に利用できる有効なインデックスが存在しないために、全レコードに対して範囲ロックが取得されてしまうようなこともある。興味がある方は、以下の2つのSQL文をSERIALIZABLE分離レベルで実行し、範囲ロックのかかり方を調べてみるとよい。 SELECT * FROM authors WHERE au_lname = 'Ringer' （非クラスタ化インデックスを利用した検索）SELECT * FROM authors WHERE phone = '801 826-0752' （インデックスが利用できない検索）

　

*19 より厳密に言えば、範囲ロックにもさらにいくつかの種類がある。興味がある方は、SQL ServerのオンラインマニュアルBooks Onlineの「キー範囲ロック」の項を参照のこと。

2.2.3 SQL Serverの基本的なロックの挙動のまとめ

　ここまでの解説をいったんまとめておこう。キーポイントは以下の通りである。

・ SQL Serverは、ロックメカニズムを用いて、トランザクション処理の同時実行制御を行う
・ 内部的には、主に3種類のロックが利用される
	・ 共有ロック（S）、更新ロック（U）、排他ロック（X）
	・ ３つのロックの間には、ロックの互換性と呼ばれる取得ルールが存在する（表2-1）。
・ 分離レベルを変更することにより、以下の2点が変化する
	・ ロックの保持期間：READ COMMITTED以下では、早期にロックが解放されてしまう（表2-2）
	・ 取得されるロックの種類：SERIALIZABLEの場合に限り、キー範囲ロックが利用される（表2-3）

　さて、ここまでは「ある１つのトランザクションが実行されている場合」におけるロックのかかり方を解説してきたが、今度は複数のトランザクションが同時に実行された場合の挙動について考えてみよう。

.NETエンタープライズWebアプリケーション開発技術大全 Vol.5 トランザクション設計編
	定価4,095円（本体3,900円+税5％） 赤間 信幸（マイクロソフト株式会社コンサルティング本部）　著 B5変型判／416p ISBN4-89100-431-2 日経BPソフトプレス発行 日経BPソフトプレスのページへ マイクロソフトプレスのページへ

　

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

　

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

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