【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 | ページヘッダー + 行ごとに詳細情報 |
またその他のページ情報が参照したい場合、下記にてページ番号を取得することができます。
-- page番号の取得 -- DBCC IND(<Database_name>,<Table_name>,<Index_id>) DBCC IND(N'Param_testDB',[Lock_test],1)
