排他ロックヒントを指定した行の列を読み取れる動作について
皆さん、こんにちは。 今回は、SQL Server で、排他ロックヒントを指定した行の列を読み取れる動作について紹介したいと思います。
排他ロックヒントを指定した行の列を読み取れるという動作の説明をするために、まずは以下のスクリプトでサンプルデータベースを作成します。
-----------------------------------------------------------------
CREATE DATABASE DB1;
GO
USE DB1
GO
CREATE TABLE tab1 (
c1 int,
c2 varchar(50),
c3 varchar(50),
CONSTRAINT PK_tab1_c1 PRIMARY KEY CLUSTERED
(
c1 ASC
));
CREATE UNIQUE NONCLUSTERED INDEX IX_tab1_c2 ON tab1 (c2);
GO
INSERT INTO tab1
VALUES(1,'SQL','Data1'),(2,'Server','Data2'),(3,'Database','Data3'),(4,'SQL Database','Data4');
GO
-----------------------------------------------------------------
1) SQL Server Management Studio などから、新しいクエリを選択し、以下のクエリを実行します。
-----------------------------------------------------------------
BEGIN TRAN
SELECT c1 FROM tab1 WITH (xlock)
WHERE c1 = 1
-----------------------------------------------------------------
このクエリの実行プラン と ロック情報 (sp_lock) を確認すると、
### 実行プラン ###
SELECT [c1] FROM [tab1] WITH(xlock) WHERE [c1]=@1
|--Clustered Index Seek(OBJECT:([DB1].[dbo].[tab1].[PK_tab1_c1]), SEEK:([DB1].[dbo].[tab1].[c1]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
### sp_lock 情報 ###
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
51 19 21575115 1 KEY (8194443284a0) X GRANT
クラスタ化インデックス PK_tab1_c1 のキー KEY (8194443284a0) に対して、排他ロック (X) が獲得され、保持されていることが確認できます。
2) 再度 新しいクエリを選択し、別セッションから テーブル tab1 と同じ行 (c1=1) の C2 に対して、以下のクエリを実行します。
-----------------------------------------------------------------
BEGIN TRAN
SELECT c2 FROM tab1 WITH (xlock)
WHERE c2 = 'SQL'
-----------------------------------------------------------------
1) で実行されたクエリで、クラスタ化インデックス PK_tab1_c1 のキー KEY (8194443284a0) に対して、排他ロック (X) が獲得されている状況で、同じ行の c2 に対して、上記のクエリを実行した場合、ブロッキングが発生せず、SELECT 文の結果が返されます。
上記の動作が行われる理由を説明します。
まずは、1) と同じく、このクエリの実行プラン と ロック情報 (sp_lock) を確認してみましょう。
### 実行プラン ###
SELECT [c2] FROM [tab1] WITH(xlock) WHERE [c2]=@1
|--Index Seek(OBJECT:([DB1].[dbo].[tab1].[IX_tab1_c2]), SEEK:([DB1].[dbo].[tab1].[c2]=[@1]) ORDERED FORWARD)
### sp_lock 情報 ###
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
51 19 21575115 1 KEY (8194443284a0) X GRANT
56 19 21575115 2 KEY (7a9801a11a80) X GRANT
2) のクエリでは、非クラスタ化インデックス IX_tab1_c2 のキー KEY (7a9801a11a80) に対して、排他ロック (X) が獲得され、保持されていることが確認できます。
1) のクエリでは、クラスタ化インデックス PK_tab1_c1 のキー KEY (8194443284a0) にのみ排他ロック (X) が獲得され、2) のクエリでは、非クラスタ化インデックス IX_tab1_c2 のキー KEY (7a9801a11a80) にのみ、排他ロック (X) が獲得されていることが確認できると思います。
1)、2) のクエリでは、排他ロック (X) を獲得するキーが異なっているため、ブロッキングが発生しません。
これは、2) のクエリでは、非クラスタ化インデックス IX_tab1_c2 に指定した列 “c2” のみを検索しており、非クラスタ化インデックスのリーフレベルに存在するデータのみで検索結果を取得することができたためとなります。
なお、以下のクエリのように、クラスタ化インデックス、非クラスタ化インデックスに含まれていない “c3” 列を SELECT 文に含めると、 非クラスタ化インデックスのみではすべての検索結果を取得することができず、クラスタ化インデックスに対して Nested Loops オペレーションによるデータの取得を行う必要が出てくるため、 クラスタ化インデックス PK_tab1_c1 のキー KEY (8194443284a0) に対するロックの獲得が必要となり、ブロッキングが発生します。
-----------------------------------------------------------------
BEGIN TRAN
SELECT c2,c3 FROM tab1 WITH (xlock)
WHERE c2 = 'SQL'
-----------------------------------------------------------------
### 実行プラン ###
SELECT [c2],[c3] FROM [tab1] WITH(xlock) WHERE [c2]=@1
|--Nested Loops(Inner Join, OUTER REFERENCES:([DB1].[dbo].[tab1].[c1]))
|--Index Seek(OBJECT:([DB1].[dbo].[tab1].[IX_tab1_c2]), SEEK:([DB1].[dbo].[tab1].[c2]=[@1]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([DB1].[dbo].[tab1].[PK_tab1_c1]), SEEK:([DB1].[dbo].[tab1].[c1]=[DB1].[dbo].[tab1].[c1]) LOOKUP ORDERED FORWARD)
今回 紹介しました排他ロックヒントを指定した行の列を読み取れるという動作は、ロックがロックリソースに対して獲得され、ロックを獲得しようとしたロックリソースに獲得済みのロックと互換性がなければブロックされるという、基本動作の中で発生しうる動作となります。
補足ですが、今回 紹介した条件のまま、SELECT文 を UPDATE 文に変更した場合、同じタイミングで 非クラスタ化インデックスとクラスタ化インデックスが更新されるため、今回 紹介しました 排他ロックヒントを指定したSELECT 文のように、何れかのインデックスにのみロックが獲得されるという動作は行われず、ブロッキングが発生します。
### 実行プラン ###
UPDATE [tab1] set [c2] = @1 WHERE [c2]=@2
|--Clustered Index Update(OBJECT:([DB1].[dbo].[tab1].[PK_tab1_c1]), OBJECT:([DB1].[dbo].[tab1].[IX_tab1_c2]), SET:([DB1].[dbo].[tab1].[c2] = [Expr1003]))
|--Compute Scalar(DEFINE:([Expr1010]=[Expr1010]))
|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(varchar(50),[@1],0), [Expr1010]=CASE WHEN CASE WHEN [DB1].[dbo].[tab1].[c2] = CONVERT_IMPLICIT(varchar(50),[@1],0) THEN (1) ELSE (0) END THEN (0) ELSE (1) END))
|--Top(TOP EXPRESSION:((1)))
|--Index Seek(OBJECT:([DB1].[dbo].[tab1].[IX_tab1_c2]), SEEK:([DB1].[dbo].[tab1].[c2]=[@2]) ORDERED FORWARD)
テーブル ヒント (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms187373(v=sql.105).aspx
行レベルのロックを取得するロック ヒント ROWLOCK、UPDLOCK、および XLOCK では、実際のデータ行ではなくインデックス キーに対してロックが実行される場合があります。たとえば、テーブルに非クラスター化インデックスがあり、ロック ヒントを使用する SELECT ステートメントがカバーするインデックスによって処理される場合、ベース テーブルのデータ行ではなく、カバーするインデックスのインデックス キーに対してロックが取得されます。
*抜粋