Clever Geek Handbook

デッドロックとロックでのキーとページのWaitResourceの復号化

ブロックされたプロセスレポートを使用したり、SQL Serverによって提供されるデッドロックグラフを収集したりすると、次のことが発生します。



waitresource =“ PAGE：6：3：70133“



waitresource =“ KEY：6：72057594041991168（ce52f92a058c）“


学習している巨大なXMLには、より多くの情報が含まれる場合があります（デッドロックされたグラフには、オブジェクト名とインデックス名を見つけるのに役立つリソースのリストが含まれています）が、常にそうとは限りません。



このテキストは、それらを解読するのに役立ちます。



ここにあるすべての情報は、インターネット上のさまざまな場所にあり、非常に配布されています。DBCC PAGEからhobt_id、文書化されていない%% physloc %%および%% lockres %%関数まですべてをまとめたいと思います。



まず、PAGEロックの待機について説明し、次にKEYロックに移ります。



1）waitresource =“ PAGE：6：3：70133” = Database_Id：FileId：PageNumber



クエリがPAGEロックを待機している場合、SQLServerはそのページのURLを提供します。



「PAGE：6：3：70133」の内訳は次のとおりです。



  • database_id = 6
  • data_file_id = 3
  • page_numer = 70133




1.1）database_idを復号化します



クエリを使用してデータベースの名前を見つけましょう。 

SELECT 
    name 
FROM sys.databases 
WHERE database_id=6;
GO


これは、SQLServer上のパブリックWideWorldImportersデータベースです。



1.2）データファイルの名前を探す-興味がある場合



次のステップでdata_file_idを使用して、テーブル名を見つけます。次の手順に進むことができますが、ファイル名に関心がある場合は、見つかったDBのコンテキストでクエリを実行し、このクエリにdata_file_idを代入することで見つけることができます。 

USE WideWorldImporters;
GO
SELECT 
    name, 
    physical_name
FROM sys.database_files
WHERE file_id = 3;
GO


WideWorldImportersデータベースでは、これはWWI_UserDataというファイルであり、C：\ MSSQL \ DATA \ WideWorldImporters_UserData.ndfに復元しました。（おっと、あなたは私がシステムディスクにファイルを置いているのを見つけました！いいえ！それは厄介でした）。



1.3）DBCCPAGEからオブジェクトの名前を取得します



データファイル3のページ＃70133がWorldWideImportersデータベースに属していることがわかりました。このページの内容は、文書化されていないDBCC PAGEとトレースフラグ3604

を使用して確認できます。注：文書化されていないため、別のサーバーのどこかにあるバックアップから復元されたバックアップでDBCCPAGEを使用することをお勧めします。場合によっては、ダンプの作成につながる可能性があります（翻訳者のコメント-残念ながら、リンクはどこにもつながりませんが、URLから判断すると、filtered-indexsについて話します）。 

/* This trace flag makes DBCC PAGE output go to our Messages tab
instead of the SQL Server Error Log file */
DBCC TRACEON (3604);
GO
/* DBCC PAGE (DatabaseName, FileNumber, PageNumber, DumpStyle)*/
DBCC PAGE ('WideWorldImporters',3,70133,2);
GO


結果まで下にスクロールすると、object_idとindex_idが見つかります。



ほとんど準備ができています！これで、クエリを使用してテーブル名とインデックス名を見つけることができます。 

USE WideWorldImporters;
GO
SELECT 
    sc.name as schema_name, 
    so.name as object_name, 
    si.name as index_name
FROM sys.objects as so 
JOIN sys.indexes as si on 
    so.object_id=si.object_id
JOIN sys.schemas AS sc on 
    so.schema_id=sc.schema_id
WHERE 
    so.object_id = 94623380
    and si.index_id = 1;
GO


これで、ロックの待機がSales.OrderLinesテーブルのPK_Sales_OrderLinesインデックスで行われたことがわかります。



注：SQL Server 2014以降では、オブジェクト名は、文書化されていないDMOsys.dm_db_database_page_allocationsを使用して見つけることもできます。ただし、データベース内のすべてのページをクエリする必要があります。これは、大規模なデータベースではあまり見栄えがよくないため、DBCCPAGEを使用しました。



1.4）ブロックされたページのデータを確認できますか？



ヌウ、はい。しかし...本当に必要ですか？

小さなテーブルでも遅いです。しかし、これはちょっとクールなので、これまで読んだので... %% physloc %%について話しましょう！



%% physloc %%は、各エントリの物理IDを返す文書化されていない魔法のピースです。SQL Server 2008以降では、%% physloc %%をsys.fn_PhysLocFormatterと一緒に使用できます。



Sales.OrderLinesのページをブロックする必要があることがわかったので、次のクエリを使用して、ページ＃70133のデータファイル＃3に格納されているこのテーブルのすべてのデータを表示できます。 

Use WideWorldImporters;
GO
SELECT 
    sys.fn_PhysLocFormatter (%%physloc%%),
    *
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GO




私が言ったように-それは小さなテーブルでも遅いです。調べたいデータがロックが見つかったときとまったく同じであるという保証がまだないため、クエリにNOLOCKを追加しました。これにより、ダーティ読み取りを安全に実行できます。

しかし、万歳、クエリは私たちのクエリが戦ったのと同じ25行を返します。PAGE



ロックについては十分です。KEYロックを待っている場合はどうなりますか？



2）waitresource =“ KEY：6：72057594041991168（ce52f92a058c）” = Database_Id、HOBT_Id（本当に必要な場合は%% lockres %%で復号化できるマジックハッシュ）





クエリがインデックスエントリをロックしようとしてそれ自体をロックしようとすると、まったく異なるタイプのアドレスが取得されます。

「6：72057594041991168（ce52f92a058c）」をパーツに分解すると、次のようになります。

  • database_id = 6
  • hobt_id = 72057594041991168
  • マジックハッシュ=（ce52f92a058c）




2.1）database_idを復号化します



これは、上記の例とまったく同じように機能します。クエリを使用してデータベースの名前を見つけます。 

SELECT 
    name 
FROM sys.databases 
WHERE database_id=6;
GO


私の場合、これは同じWideWorldImportersデータベースです。



2.2）hobt_idを復号化します



見つかったデータベースのコンテキストでは、いくつかの結合を使用してsys.partitionsへのクエリを実行する必要があります。これは、テーブル名とインデックス名を決定するのに役立ちます... 

USE WideWorldImporters;
GO
SELECT 
    sc.name as schema_name, 
    so.name as object_name, 
    si.name as index_name
FROM sys.partitions AS p
JOIN sys.objects as so on 
    p.object_id=so.object_id
JOIN sys.indexes as si on 
    p.index_id=si.index_id and 
    p.object_id=si.object_id
JOIN sys.schemas AS sc on 
    so.schema_id=sc.schema_id
WHERE hobt_id = 72057594041991168;
GO


PK_Application_Countriesインデックスを使用して、リクエストがApplication.Countriesロックで待機していたことを示しています。



2.3）今度はいくつかの魔法の%% lockres %%-どのレコードがロックされているかを知りたい場合



ロックが必要な行を本当に知りたい場合は、テーブル自体にクエリを実行することでそれを把握できます。文書化されていない%% lockres %%関数を使用して、マジックハッシュに一致するエントリを見つけることができます。

このクエリはテーブル全体をスキャンすることに注意してください。大きなテーブルでは、これはまったく面白くない場合があります。 

SELECT
    *
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO


ブロッキングが問題になる可能性があるため、 NOLOCKを追加しました（twitterのKlaus Aschenbrennerのアドバイスに基づいて）。トランザクションが開始されたときに何があったかではなく、現在何が存在するかを確認したいだけです。データの一貫性は私たちにとって重要ではないと思います。

出来上がり、私たちが戦った記録！





謝辞とさらなる読み物



誰がこれらのことの多くを最初に説明したかは覚えていませんが、ここでは、最も文書化されていないものについての2つの投稿を示します。



More articles:


All Articles