排他制御メカニズムから“待ち”原因を究明する：Dr. K's SQL Serverチューニング研修（4）（3/3 ページ）

[熊澤幸生, 工藤淳（インタビュアー），CSK Winテクノロジ/オフィスローグ] PC用表示関連情報

LINE

Hatena

ロックを複数の粒度でかける際のロックマネージャの動作

複数粒度でのロック

　ロックをかけるときに粒度に注意しなくてはならないのが、複数粒度でのロックです。あるオブジェクトに排他（X）モードでロックをかける場合、従属するページや行にもすべてロックがかかってしまいます。このため粒度の異なる複数のリソースに、最小限のロックを効率よくかけるためには、どういう粒度が最も適切なのかをよく考えないといけません。ここが粒度によるロック制御のうえでも難しい問題点になっています。

　図4ではトランザクション1がテーブルに対して排他（X）モードのロックをかけています。するとそのテーブルに属する行を更新しようとするトランザクション2は、トランザクション1がコミットするまで排他待ちとなります。逆に、トランザクション2が先に排他（X）モードの行ロックをかけていれば、トランザクション1は排他待ちとなります。

　図4　複数粒度でのロック待ち状態

複数粒度でのロックマネージャの動作

　SQL Serverは、ロックのための排他制御テーブルをメモリプールの中に持っていて、それは「ロックエリア」と呼ばれます。どういう仕組みでロックをかけているかを、図5のリソースフォーマットで説明しましょう。

　図5　ロックマネージャが使用するリソースフォーマット

　図5の一番左に矢印で示している「ロックリソースタイプ」という番号は、1～10番まであります（表3）。テーブル＝5、ページ＝6、行ID＝9、キー範囲＝7に対応します。これによってどの粒度でロックをかけているかが分かります。


ロックリソースタイプ	説明
1	NULLリソース（未使用）
2	データベース
3	ファイル
4	インデックス
5	テーブル
6	ページ
7	キー
8	エクステント
9	RID（行ID）
10	アプリケーション
表3　ロックリソースタイプの内容

　またその右にある1けたの数字は、「データベースID」を表しています。SQL Serverでは複数のテーブルを持てますから、ロックの対象がどのテーブルかをここで表示しているわけです。

　そして一番右の大きなセルの中に、「何のリソースに対して排他をかけているか」の実際の情報が出ています。例えばテーブルの場合はオブジェクトIDという、SQL Serverの内部で管理しているテーブルのID「325658」が格納されています。

　ページの場合は「ファイル番号：ページ番号」という形式で「2:328」というデータが、行IDでは「ファイル番号：ページ番号：ページ内スロット番号」という形式で「2:328:11」というデータが格納されています。ページ内スロット番号とは、後述するラッチの説明にも出てきますが、ページヘッダに書き込まれたデータ行の番号で、これと行オフセットの値を使うことで行を一意に特定します。

　あるオブジェクトに対して排他をかけようとする場合、リソースフォーマットの左から順に照合し、競合するリソースに対しては排他待ちを命じます。例えばページに対するロック「2:328」が存在する場合、行ロック「2:328:11」は前方一致になりますから、「2:328:11」が待ちになるわけです。

ページデータ更新時に瞬間的な排他制御をかけるのが「ラッチ」

　先ほど「ラッチ」について少し触れましたが、この性質についてもう少し説明しましょう。それにはまず、SQL Serverのページ構造を知る必要があります。ページとは8Kbytesの連続したディスク領域で、データ行やインデックスのデータを格納します。ページはSQL Serverがデータを読み書きする際の最小単位になります。

　行データを格納するデータページには、通常複数のデータ行が格納されますが、これ以外にもページそのものの情報を格納する「ページヘッダ」と「行オフセット」という領域があります（図6）。

　図6　データページの内部構造

　ページヘッダはページの先頭から96bytesの領域を割り当てられています。ここにはページの識別子（pageID）、前後のページへのポインタ（nextPage／prevPage）、格納されているデータ行の総数（slotCnt）といったシステム情報が書き込まれています。

　ページヘッダを除いた8096bytesは、データ行と行オフセットに割り当てられます。データ行はページヘッダの直後から連続的に配置されます。これに対し、行オフセットはページの末尾から開始されます。行オフセットには、ページ内の1つのデータ行に対して1つのエントリ（2bytes）が割り当てられ、その行がページ先頭から何byteだけ離れているかを記録します。

　データページに対し1行でもデータ行が追加されたり変更が行われると、ページヘッダや行オフセットの値に変更が生じます。例えば、ページに対して新しい行を1行追加する場合には、ページヘッダの「slotCnt」を1つ加算する作業が必要になります。このときに瞬間的に排他制御をかけるのがラッチです。ラッチはページのデータの更新を行うときに、ページヘッダに対して排他制御をかける役目を持っているのです。

　ラッチもロックと同様にパフォーマンス低下の原因となります。前述の「sysperfinfo」システムビューのパフォーマンス・カウンタで監視できます。

　SQL Serverの排他制御で一番やっかいなのが、インデックスの定義によってロックの粒度の組み合わせが全部変わってしまう点です。このあたりがSQL Serverの排他制御を理解する最も難しい部分であり、なおかつデッドロックなどの問題を解き明かすカギになります。そこで次回はインデックスとロックの関係やページ分割のメカニズムを解説します。（次回へ続く）

監修者プロフィール

株式会社CSK Winテクノロジ執行役員兼技術推進担当。

熊澤幸生（くまざわ・ゆきお）

メインフレーム環境で20年近くデータベース関連のITプロジェクトを数多く経験。また1979年から1983年まで米国に駐在し、データ主導型システム設計を実プロジェクトで学ぶ。1994年、アスキーNT（現、CSK Winテクノロジ）設立に参加し、SQL Server Ver 4.2からSQL Server 2000までシステム構築、教育にかかわってきた。現在同社執行役員 Chief Technology Officer。また、SQL Serverユーザー会「PASSJ」の理事として活動中。

「次回」へ

「Dr. Ks SQL Serverチューニング研修」バックナンバー