Dešifreerime Key ja Page WaitResource deadlock’id ja lukustused

Kui kasutate blokeeritud protsesside aruannet (blocked process report) või kogute SQL Serveri poolt pakutavaid deadlock graafe perioodiliselt, siis võite aeg-ajalt kokku puutuda järgmist tüüpi asjadega:

waitresource="PAGE: 6:3:70133"

waitresource="KEY: 6:72057594041991168 (ce52f92a058c)"

Mõnikord on selles hiiglaslikus XML-is, mida te uurite, rohkem teavet (deadlock graafid sisaldavad ressursside loetelu, mis aitab tuvastada objekti ja indeksi nimed), aga see ei ole alati nii.

See tekst aitab teil neid dešifreerida.

Kogu teave, mis siin on, on internetis eri kohtades, lihtsalt väga hajutatuna! Ma tahan kõik kokku koguda – alates DBCC PAGE'ist kuni hobt_id ja dokumenteerimata %%physloc%% ja %%lockres%% funktsioonideni.

Esmalt räägime PAGE-blokeeringute ootamisest, seejärel liigume KEY-blokeeringute juurde.

1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber

Kui teie päring ootab PAGE-blokeeringule, annab SQL Server teile selle lehe aadressi.

Läbi «PAGE: 6:3:70133» saame:

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

1.1) Dešifreerime database_id

Otsime andmebaasi nime päringu abil:

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

See on avalik andmebaas WideWorldImporters minu SQL Serveris.

1.2) Otsime andmefaili nime – kui teid huvitab

Kavandame kasutada data_file_id järgmisel sammul, et leida tabeli nimi. Te võite lihtsalt minna järgmisele sammale, aga kui teid huvitab faili nimi, võite selle leida, tehes päringu leitud andmebaasi kontekstis, asendades data_file_id selles päringus:

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

Andmebaasis WideWorldImporters on see fail nimega WWI_UserData ja see on taastatud minu C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, tabasite mind failide ketasüsteemi panemise pealt! Ei! Nüüd on kohatu!).

1.3) Saame objekti nime DBCC PAGE'ist

Nüüd teame, et leht #70133 andmefailis 3 kuulub andmebaasile WorldWideImporters. Saame vaadata selle lehe sisu dokumendivälise DBCC PAGE'i ja jälgimislipu 3604 abil.
Märkus: ma eelistan kasutada DBCC PAGE'i taastatud varukoopia koopia peal kuskil teisel serveril, sest see on dokumendiväline asi. Mõnes olukorras võib see viia dumpi loomise (tõlki märkus – link viib kahjuks tühjast kohta, aga URL-i põhjal tõenäoliselt räägime filtreeritud indeksitest).

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

Tulemusi sirvides võib leida object_id ja index_id.

Peaaegu valmis! Nüüd saab tabeli ja indeksi nimed leida päringu abil:

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

Ja nüüd näeme, et ooteaeg lukustamisel oli indeksi PK_Sales_OrderLines tabelis Sales.OrderLines.

Märkus: SQL Server 2014 ja uuemates saab objekti nime leida ka dokumenteerimata DMO sys.dm_db_database_page_allocations abil. Kuid peate küsima iga lehe kohta andmebaasis, mis ei tundu väga hea suurte andmebaaside puhul, seega kasutasin DBCC PAGE'i.

1.4) Kas on võimalik näha andmeid lukustatud lehel?

Hmm, jah. Aga... kas olete kindel, et see on teile tõesti vajalik?
See on aeglane isegi väikestes tabelites. Kuid see on kuidagi lahe, seega, kuna olete siiani lugenud... räägime %%physloc%%-ist!

%%physloc%% on dokumenteerimata tükk maagia, mis tagastab füüsilise identifikaatori iga kirje jaoks. Saate kasutada %%physloc%% koos sys.fn_PhysLocFormatter'iga SQL Server 2008 ja uuemates.

Nüüd, kui me teame, et tahtsime lukustada lehte tabelis Sales.OrderLines, saame vaadata kõiki selle tabeli andmeid, mis on salvestatud andmefailis #3 lehel #70133, kasutades sellist päringut:

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

Nagu ma ütlesin — see on aeglane isegi tillukestes tabelites. Lisasin päringule NOLOCK, kuna meil pole ikkagi mingeid garantiisid, et andmed, mida soovime vaadata, on täpselt need, mis olid lukustamise avastamise hetkel — nii et saame rahulikult teha musti lugemisi.
Aga, jee, päring tagastab mulle need 25 rida, mille nimel meie päring võitles

Jäta PAGE-lukustused kõrvale. Mis siis, kui ootame KEY-lukustust?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (maagiline hash, mille saab dekrüpteerida %%lockres%%-iga, kui soovite seda tõesti teha)

Kui teie päring üritab lukustada kirjet indeksis ja jääb ise lukku, saad accessun teistsuguse aadressi tüübi.
Tükeldades “6:72057594041991168 (ce52f92a058c)” saame:

• database_id = 6
• hobt_id = 72057594041991168
• maagiline hash = (ce52f92a058c)

2.1) Dekrüpteerime database_id

See töötab täpselt samamoodi nagu eespool toodud näites! Leiame andmebaasi nime päringu abil:

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

Minu puhul on see ikka sama andmebaas WideWorldImporters.

2.2) Dekodeerime hobt_id

Kontekstis leitud andmebaasis tuleb teha päring sys.partitions tabelisse, koos paari join'iga, mis aitavad tuvastada tabeli ja indeksi nimed...

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

See ütleb mulle, et päring ootas blokkeeringut Application.Countries, kasutades indeksi PK_Application_Countries.

2.3) Nüüd väike maagia %%lockres%% — kui soovite teada, milline rekord oli blokeeritud

Kui ma tõesti tahan teada, millisel real blokkeerimine toimus, võin seda välja selgitada päringuga iseenda tabeli vastu. Saame kasutada dokumenteerimata funktsiooni %%lockres%%, et leida rekord, mis vastab maagilisele hash'ile.
Pidage meeles, et see päring skaneerib kogu tabelit, ja suurte tabelite puhul ei pruugi see olla just kõige toredam:

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

Lisasin NOLOCK (Klaus Aschenbrenneri soovitusel Twitteris) kuna lukud võivad probleemiks osutuda. Soovime lihtsalt vaadata, mis seal hetkel on, mitte mis seal oli, kui tehing algas — ei arva, et andmete järjepidevus on meile oluline.
Voilà, rekord, mille nimel me võitlesime!

Tänud ja edasi lugemine

Ei mäleta, kes esimesena paljusid neist asjadest kirjeldas, aga siin on kaks postitust kõige vähem dokumenteeritud asjadest, mis võivad teile meeldida:

• Paul Randi postitus %%physloc%% ja sys.fn_PhysLocFormatter (kuidas me oma andmed esimeses näites leidisime)
• Küsimus StackOverflow's %%lockres%% kasutamise kohta (kuidas me andmed teises näites leidsime). Üks vastus viib postituseni Grant Fritchey %%lockres%% kohta, mis on kirjutatud juba 2010. aastal.

Allikas: habr.com