Расшифровка Key и Page WaitResource при дедлоках и блокировках

Ако използвате отчет за блокировки (blocked process report) или събирате графики на дедлокове, предоставяни от 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-блокировка, SQL Server ще ви даде адреса на тази страница.

Разбивайки "PAGE: 6:3:70133", получаваме:

• database_id = 6
• data_file_id = 3
• page_number = 70133

1.1) Разшифроваме database_id

Нека намерим името на базата данни с помощта на запитване:

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

Това е публичната БД WideWorldImporters на моя SQL Server.

1.2) Търсим името на файла с данни — ако ви интересува

Ще използваме data_file_id на следващата стъпка, за да намерим името на таблицата. Можете да преминете направо към следващата стъпка, но ако се интересувате от името на файла, можете да го намерите, като изпълните запитване в контекста на намерената БД, подменяйки data_file_id в това запитване:

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

В БД WideWorldImporters това е файл, наречен WWI_UserData и той се намира при мен в C:MSSQLDATAWideWorldImporters_UserData.ndf. (Упс, хванахте ме как поставям файлове на диска с системата! Не! Неловко излезе).

1.3) Получаваме името на обекта от DBCC PAGE

Сега знаем, че страница #70133 във файл с данни 3 принадлежи на БД WorldWideImporters. Можем да разгледаме съдържанието на тази страница с помощта на недокументирания DBCC PAGE и trace-флага 3604.
Забележка: предпочитам да използвам DBCC PAGE на възстановена копие от резервно копие на друг сървър, защото това е недокументирано. В някои случаи, това може да доведе до създаване на дамп (бележка на преводача — връзката за съжаление води до никъде, но съдя по URL, става дума за filtered-индекси).

/* 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

И ето, виждаме, че изчакването за блокирането е било на индекса PK_Sales_OrderLines на таблицата Sales.OrderLines.

Забележка: в SQL Server 2014 и по-нови версии името на обекта може също да бъде намерено чрез не-документираният DMO sys.dm_db_database_page_allocations. Но ще трябва да заявявате всяка страница в БД, което не изглежда особено удобно за големи бази данни, затова използвах DBCC PAGE.

1.4) Може ли да видим данните на страницата, която беше блокирана?

Ну, да. Но... уверени ли сте, че наистина ви е необходимо?
Това е бавно дори на малки таблици. Но изглежда интересно, така че, след като стигнахте до този момент... да поговорим за %%physloc%%!

%%physloc%% е не-документиран малък магически трик, който връща физическия идентификатор за всяка запись. Можете да използвате %%physloc%% заедно със sys.fn_PhysLocFormatter в SQL Server 2008 и по-нови..

Сега, когато знаем, че искахме да наложим блокировка на страницата в Sales.OrderLines, можем да видим всички данни в тази таблица, които се съхраняват в файл данни #3 на страница #70133, с помощта на следната заявка:

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 с двойни JOIN, които ще помогнат да се определят имената на таблицата и индекса...

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

Той ми казва, че запитването е чакало на блокировката Application.Countries, използвайки индекса PK_Application_Countries.

2.3) Сега малко магия %%lockres%% — ако искате да разберете коя запись е била блокирана

Ако действително искам да разбера на коя редица е била необходима блокировката, мога да разбера това с помощта на запитване към самата таблица. Можем да използваме недокументираната функция %%lockres%%, за да намерим запис, съвпадащ с чудодейния хеш.
Имайте предвид, че това запитване ще сканира цялата таблица, и на големи таблици това може да не е много забавно:

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

Добавих NOLOCK (по съвет на Klaus Aschenbrenner в Twitter) защото блокировките могат да станат проблем. Ние просто искаме да видим какво има там в момента, а не какво е имало, когато е започнала транзакцията — не мисля, че последователността на данните е важна за нас.
Ето я, записа, за който се борихме!

Благодарности и допълнително четене

Не помня кой първи описа много от тези неща, но ето две публикации за най-слабо документираните неща, които може да ви харесат:

• Публикация на Paul Randal за %%physloc%% и sys.fn_PhysLocFormatter (както нашите данни в първия пример)
• Въпрос на StackOverflow за използването на %%lockres%% (както намерихме данните във втория пример). Един от отговорите води до публикация на Grant Fritchey за %%lockres%%, написана още през 2010.

Източник: habr.com