小物SEのメモ帳

IT関係の試したことや調べたことの備忘録

【SQL server】インデックス間のデッドロック

SQL Server

一つのテーブルにクラスタ化インデックスと非クラスタ化インデックスが貼られている場合に、同一テーブル内のインデックス間で発生するデッドロックについて検証してみました。

テストデータとテーブルを準備

以下のようなテーブルにテストデータを用意します。

CREATE TABLE [test].[Deadlock](
	[col1] [int] NOT NULL,
	[col2] [int] NOT NULL,
	[col3] [int] NOT NULL,
	[col4] [int] NOT NULL,
	[col5] [int] NOT NULL
) ON [PRIMARY]

適当なテストデータを投入しておきます。
f:id:utiowa:20170702152418p:plain

デッドロックさせるインデックスとして以下インデックスを準備します。

CREATE CLUSTERED INDEX [Cix_col1] ON [test].[Deadlock]
(
	[col1] ASC
)
GO


CREATE NONCLUSTERED INDEX [NixIn_col2] ON [test].[Deadlock]
(
	[col2] ASC
)
INCLUDE ( [col4])
GO

デッドロック用のクエリを実行

UPDATE側のクエリでは、クラスタ化インデックスを使って更新するクエリを実行します。
その際、更新対象の項目は付加列インデックスで使用している列を更新します。

/*************************************/
-- UPDATE側
/*************************************/
USE sales
GO

DECLARE @counter INT = 0
DECLARE @j INT 

WHILE @counter < 1000000
BEGIN
	BEGIN TRAN;
		SET @j = CONVERT(INT, RAND() * 100)
		UPDATE [test].[Deadlock]
		SET [col4] = @j
		WHERE [col1] = 1
	COMMIT TRAN;
	SET @counter += 1
END

f:id:utiowa:20170702152834p:plain

このUPDATE側のクエリでは次の実行プランからもわかる通り、クラスタ化インデックスを排他ロックにて参照し、付加列インデックスを更新します。
f:id:utiowa:20170702152820p:plain



SELECT側のクエリでは、非クラスタ化インデックスを使用してkey lookupでクラスタ化インデックスへの参照が発生するようなクエリを実行します。

/*************************************/
-- SELECT側
/*************************************/
USE sales
GO

DECLARE @counter INT = 0
DECLARE @dummy INT

WHILE @counter < 1000000
BEGIN
	SELECT
		@dummy = [col3]
	FROM [test].[Deadlock]
	WHERE [col2] = 1
	SET @counter += 1		
END

f:id:utiowa:20170702153143p:plain

こちらのSELECT側のクエリでは、WHERE句の条件に合致する行を非クラスタ化インデックスを使用して共有ロックにて行を特定し、key lookupを使用してクラスタ化インデックスから列の参照していることがわかるかと思います。
f:id:utiowa:20170702153033p:plain

#key lookupを使ってしまうパターンなどインデックスの仕組みについては以下をご参照ください。
memorandom-nishi.hatenablog.jp


デッドロック発生時のロック状況を確認してみると、UPDATE側は排他ロック、SELECT側は共有ロックを取る際にデッドロックが発生していることがわかります。
f:id:utiowa:20170702153359p:plain

USE sales
GO

SELECT 
		[locks].[request_session_id]									AS	[spid],
		ISNULL(DB_NAME([resource_database_id]), '')							AS	[db_name],
		CASE	
			WHEN	[resource_type] = 'OBJECT'			THEN	OBJECT_NAME([resource_associated_entity_id], [resource_database_id])
			WHEN	[resource_associated_entity_id] =	0	THEN	''
			ELSE	OBJECT_NAME([p].[object_id])
		END												AS [entity_name],
		[ind].[index_id]										AS [index_id],
		[ind].[name]											AS [index_name],
		ISNULL([locks].[resource_type], '')								AS [resource_type],
		[locks].resource_description									AS [resource_description],
		[locks].request_type										AS [request_type],
		[locks].request_mode										AS [request_mode],
		[requests].[command]										AS [command],
		[locks].[request_status]									AS [request_status],
		ISNULL([wait].[wait_duration_ms], 0)								AS [wait_duration_ms],
		ISNULL([wait].[wait_type], N'')									AS [wait_type],
		ISNULL(CONVERT (varchar,[wait].[blocking_session_id]), '')					AS [blocking_session_id],
		ISNULL([wait].[resource_description], N'')							AS [resource_description],
		ISNULL(REPLACE(REPLACE([query_text].[text],CHAR(13), ''), CHAR(10), ' '), N'')			AS [query_text],
		[plan].[query_plan]										AS [query_plan]
FROM			[sys].[dm_tran_locks]									AS [locks] WITH (NOLOCK)
	LEFT JOIN	[sys].[partitions]									AS [p] WITH (NOLOCK)
		ON	[p].[partition_id]		= [locks].[resource_associated_entity_id]
	LEFT JOIN	[sys].[dm_os_waiting_tasks]								AS [wait] WITH (NOLOCK)
		ON	[locks].[lock_owner_address]	= [wait].[resource_address]
		AND	[locks].[request_session_id]	= [wait].[session_id]
	LEFT JOIN	[sys].[indexes]										AS [ind] WITH (NOLOCK)
		ON	[p].[object_id]			= [ind].[object_id]
		AND	[p].[index_id]			= [ind].[index_id]
	LEFT JOIN	[sys].[dm_exec_requests]								AS [requests] WITH (NOLOCK)
		ON	[locks].[request_session_id]	= [requests].[session_id]
		AND	[wait].[session_id]		= [requests].[session_id]
    OUTER APPLY	[sys].[dm_exec_sql_text]([requests].[sql_handle])						AS [query_text] 
	OUTER APPLY [sys].[dm_exec_query_plan]([requests].[plan_handle]) 					AS [plan] 
WHERE	resource_database_id = DB_ID()
AND	resource_type <> 'DATABASE'
ORDER BY spid
OPTION (RECOMPILE)


実際にインデックス間でデッドロックが発生した場合のデッドロックグラフを確認してみると、お互いのインデックスのロック解放待ち状態となっていることが確認できるかと思います。
f:id:utiowa:20170702153909p:plain
#デッドロックグラフはデフォルトで取得されている拡張イベント「system_health」から確認できます。

＜UPDATE側＞
UPDATE側クエリがクラスタ化インデックスの排他ロックを獲得
UPDATE側クエリが非クラスタ化インデックスの排他ロック獲得待ち

＜SELECT側＞
SELECT側クエリが非クラスタ化インデックスの共有ロックを獲得
SELECT側クエリがクラスタ化インデックスの共有ロック獲得待ち


また、今回はキーロックのデッドロック時の情報としてwaitresourceの値からどのデータでデッドロックが発生してしまったか確認することができます。

拡張イベントのsystem_healthのSSMSにて開き、カラムの上で右クリック、「列の選択」を選択します。
f:id:utiowa:20170702154221p:plain

「選択した列」に「xml_report」を追加します。
f:id:utiowa:20170702154307p:plain

xml_reportを列として追加できたら、name列が「xml_deadlock_report」であるレコードのxml_report列をダブルクリックします。
f:id:utiowa:20170702154437p:plain

デッドロック情報がxml形式で確認できるため、xmlの「waitresource」のカッコ()の値を確認します。
f:id:utiowa:20170702154711p:plain

カッコ()の値を使って、以下クエリで検索します。

/**************************************/
--デッドロックしたレコードの確認
/**************************************/
USE sales
GO

SELECT * 
FROM  [test].[Deadlock]
WHERE %%lockres%% = '(de42f79bc795)'

f:id:utiowa:20170702154812p:plain

検索結果から、デッドロック時のキーが確認できます。

同一テーブル内のインデックス間デッドロックの解決策

このようなインデックス間のデッドロックが発生する場合の解決策としては以下が考えられるかと。
（１）別のインデックス使用して、非クラスタ化インデックス使用時にkey lookupが発生しないようにする
（２）トランザクション分離レベルをREAD COMMITED SNAPSHOTにする

ただし（２）は、参照時に直接共有ロックをかけないものの、その分tempdbにおける負荷が高くなることはデメリットとなることは考慮すべきかと思います。

ロックやトランザクション分離レベルについてはこちらを参照いただければ。
memorandom-nishi.hatenablog.jp
memorandom-nishi.hatenablog.jp