読者です 読者をやめる 読者になる 読者になる

小物SEのメモ帳

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

【SQL server】ロックの種類とページ情報

デッドロックを調査するにあたりロックの種類について調べた内容を記しておきます。


ロックの粒度
上から順にロックの粒度は細かくなります。

Lock Type 説明
DB データベース単位のロック
TAB テーブル単位のロック
PAGE ページ単位のロック
ROW 行単位のロック
KEY キー単位のロック


代表的なロックモード

Request Mode Lock Mode 説明
S 共有ロック データの変更や更新を伴わない参照操作
U 更新ロック 更新可能なデータに対するロック
X 排他ロック DMLを実行してデータを変更する際のロック
IS インテント共有ロック 下位のLock Typeに位置する全てのリソースに対し他からの共有ロック要求から保護する
IU インテント更新ロック 下位のLock Typeに位置する全てのリソースに対し他からの更新ロック要求から保護する 更新操作時にIXロックに変換される
IX インテント排他ロック 下位のLock Typeに位置する全てのリソースに対し他からの排他ロック要求から保護する
SIU 共有インテント更新ロック 共有ロックとインテント更新ロックを組み合わせたロック
SIX インテント排他付き共有ロック 下位のLock Typeに位置する全てのリソースに対し他からの共有ロック要求から保護し、一部のリソースに対するインテント排他ロックから保護する
UIX 更新インテント排他ロック 更新ロックとインテント排他ロックを組み合わせたロック
Sch-S スキーマ安定度 テーブルのスキーマに依存する操作を行う時のロック トランザクションのロック要求をブロックはしない
Sch-M スキーマ修正 テーブルのスキーマに依存する操作を行う時のロック 外部からのすべてのロック要求をブロックする
BU 一括更新ロック データを一括で投入する際にTABLOCKヒント句を使用してロック

基本的には共有ロック、更新ロック、排他ロックを押さえておけばよいと思います。
現在ロック中の情報を得るためには以下のようなクエリで確認できます。

◆簡易版

sp_lock

◆詳細版

-- ===============================================
-- 現在のDBのロック詳細
-- ===============================================

USE Param_testDB
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])
			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_mode																							AS [request_mode],
		[locks].request_type																							AS [request_type],
		ISNULL([wait].[wait_duration_ms], 0)																	AS [wait_duration_ms],
		ISNULL([wait].[wait_type], N'')																			AS [wait_type],
		ISNULL([wait].[resource_description], N'')															AS [resource_description],
		ISNULL(CONVERT (varchar,[wait].[blocking_session_id]), '')									AS [blocking_session_id],
		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]
	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]
    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)


ロックされているページに格納されているデータを参照する場合、undocumentedな以下クエリで参照できます。

-- page内容の取得
DBCC TRACEON(3604)
--	DBCC PAGE(<Dabase_name>, <filenum>, <pagenum>, <printopt>)
	DBCC PAGE(N'Param_testDB', 1, 5294, 3) WITH TABLERESULTS
DBCC TRACEOFF(3604)

filenum, pagenumは現在のDBのロック詳細で取得したresource_description が filenum:pagenumで表示されています。
またページ内の詳細情報を参照する場合のprintoptは0~3を選択します。

printopt 出力情報
0 ページヘッダーのみ
1 ページヘッダー + ページスロット
2 ページヘッダー + ページダン
3 ページヘッダー + 行ごとに詳細情報

f:id:utiowa:20161103205217p:plain


またその他のページ情報が参照したい場合、下記にてページ番号を取得することができます。

-- page番号の取得
--  DBCC IND(<Database_name>,<Table_name>,<Index_id>)
    DBCC IND(N'Param_testDB',[Lock_test],1)


f:id:utiowa:20161103205242p:plain

広告を非表示にする