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

SQL Serverのロック管理

マイクロソフト コンサルティング本部 赤間 信幸
2005/03/01
Page1 Page2 Page3 Page4

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

 さて、REPEATABLE READとSERIALIZABLE分離レベルは、ロックの保持期間に関しては同じ動作をする。しかしこの2つの分離レベルでは、ファントムが発生しうるクエリを発行した際には、インデックスキーに対して異なるロックが取得される。表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)
  ・ 3つのロックの間には、ロックの互換性と呼ばれる取得ルールが存在する(表2-1)。
 
・ 分離レベルを変更することにより、以下の2点が変化する
  ・ ロックの保持期間:READ COMMITTED以下では、早期にロックが解放されてしまう(表2-2)
  ・ 取得されるロックの種類:SERIALIZABLEの場合に限り、キー範囲ロックが利用される(表2-3)

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

.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アプリケーション開発技術大全」


Insider.NET フォーラム 新着記事
  • 第2回 簡潔なコーディングのために (2017/7/26)
     ラムダ式で記述できるメンバの増加、throw式、out変数、タプルなど、C# 7には以前よりもコードを簡潔に記述できるような機能が導入されている
  • 第1回 Visual Studio Codeデバッグの基礎知識 (2017/7/21)
     Node.jsプログラムをデバッグしながら、Visual Studio Codeに統合されているデバッグ機能の基本の「キ」をマスターしよう
  • 第1回 明瞭なコーディングのために (2017/7/19)
     C# 7で追加された新機能の中から、「数値リテラル構文の改善」と「ローカル関数」を紹介する。これらは分かりやすいコードを記述するのに使える
  • Presentation Translator (2017/7/18)
     Presentation TranslatorはPowerPoint用のアドイン。プレゼンテーション時の字幕の付加や、多言語での質疑応答、スライドの翻訳を行える
@ITメールマガジン 新着情報やスタッフのコラムがメールで届きます(無料)

注目のテーマ

Insider.NET 記事ランキング

本日 月間