🥇Decrypting Key le Leqephe WaitResource ka li-deadlocks le linotlolo

Haeba u sebelisa tlaleho e koetsoeng ea ts'ebetso kapa u bokella li-graph tsa "deadlock" tse fanoeng ke SQL Server nako le nako, u tla kopana le lintho tse kang tsena:

waitresource="LEQEPHE: 6:3:70133"

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

Ka linako tse ling, ho tla ba le tlhahisoleseling e eketsehileng ho XML e kholo eo u ithutang eona (li-graph tsa "deadlock" li na le lethathamo la lisebelisoa tse u thusang ho fumana mabitso a ntho le index), empa eseng kamehla.

Sengoliloeng sena se tla u thusa ho li utloisisa.

Litaba tsohle tse teng mona li fumaneha Inthaneteng libakeng tse fapaneng, li ajoa haholo! Ke batla ho li kopanya kaofela, ho tloha DBCC PAGE ho ea ho hobt_id ho ea ho %%physloc%% le %%lockres%% e sa ngolisoang.

Pele, ha re bue ka ho leta ho linotlolo tsa LEQEPHE, ebe re fetela ho linotlolo tsa KEY.

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

Haeba kopo ea hau e emetse senotlolo sa LEQEPHE, SQL Server e tla u fa aterese ea leqephe leo.

Ho qhekella "TSEBE: 6:3:70133" re fumana:

database_id = 6
data_file_id = 3
page_nomoro = 70133

1.1) Hlakola database_id

Fumana lebitso la database u sebelisa potso:

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

Ke ea sechaba DB WideWorldImporters ho SQL Server ea ka.

1.2) Ho batla lebitso la faele ea data - haeba u thahasella

Re tla sebelisa data_file_id mohatong o latelang ho fumana lebitso la tafole. U ka tlolela mohatong o latelang, empa haeba u thahasella lebitso la faele, u ka le fumana ka ho botsa potso maemong a database e fumanoeng, ho kenya data_file_id potsong ena:

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

Bobolokelong ba WideWorldImporters ena ke faele e bitsoang WWI_UserData 'me ke e khutlisetse ho C:MSSQLDATAWideWorldImporters_UserData.ndf. (Oops, u ntšoere ke kenya lifaele ho system drive! Che! Hoa hlabisa lihlong).

1.3) Fumana lebitso la ntho ho DBCC LEQEPHE

Hona joale rea tseba hore leqephe la #70133 ho datafile 3 ke la WorldWideImporters database. Re ka sheba litaba tsa leqephe lena re sebelisa DBCC LEQEPHE le sa ngolisoang le ho latela folakha ea 3604.
Tlhokomeliso: Ke khetha ho sebelisa DBCC PAGE ho kopi e tsosolositsoeng kae-kae ho seva se seng, hobane ke lintho tse sa ngolisoang. Maemong a mang, o e ka fella ka thotobolo (hoo e ka bang. mofetoleli - sehokelo, ka bomalimabe, ha se lebise kae kapa kae, empa ho ahlola ka url, re bua ka li-index tse tlhotliloeng.).

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

Ka ho tsamaisetsa liphetho, u ka fumana object_id le index_id.

O se o tla qeta! Joale u ka fumana tafole le mabitso a index ka potso:

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

Mme mona re bona hore ho leta loko ho ne ho le ho PK_Sales_OrderLines index of the Sales.OrderLines table.

Tlhokomeliso: Ho SQL Server 2014 le holimo, lebitso la ntho le ka fumanoa ho sebelisoa DMO sys.dm_db_database_page_allocations e sa ngolisoang. Empa u tlameha ho botsa leqephe le leng le le leng sebakeng sa polokelo ea litaba, se sa shebahaleng se le monate bakeng sa li-database tse kholo, kahoo ke sebelisitse DBCC LEQEPHE.

1.4) Na hoa khoneha ho bona lintlha tse leqepheng le neng le koetsoe?

Nuuu, ho. Empa ... na u na le bonnete ba hore ua e hloka?
E lieha le litafoleng tse nyane. Empa ho monate, kahoo kaha u balile ho fihlela joale ... ha re bue ka %%physloc%%!

%%physloc%% ke karolo ea boselamose e sa ngolisoang e khutlisang ID ea 'mele bakeng sa keno e ngoe le e ngoe. u ka sebelisa %%physloc%% mmoho le sys.fn_PhysLocFormatter ho SQL Server 2008 le ho feta.

Kaha joale rea tseba hore re ne re batla ho thibela leqephe ho Sales.OrderLines, re ka sheba lintlha tsohle tse tafoleng ena, tse bolokiloeng faeleng ea data #3 leqepheng la #70133, ka potso e latelang:

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

Joalokaha ke boletse, e lieha le litafoleng tse nyane. Ke kentse NOLOCK kopong hobane re ntse re sena tiisetso ea hore data eo re batlang ho e sheba e ts'oana hantle le kamoo e neng e le ka teng nakong eo senotlolo se neng se sibolloa - kahoo re ka bala ka mokhoa o sireletsehileng.
Empa, hooray, potso e mpusetsa eona mela e 25 eo potso ea rona e e loanelang.

Ho lekane ka liloko tsa PAGE. Ho thoe'ng haeba re emetse senotlolo sa KEY?

2) waitresource = "KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magic hash e ka hlakoloang ka %%lockres%% haeba u hlile u batla)

Haeba potso ea hau e leka ho notlela lethathamong la index 'me ea notleloa ka bo eona, u fumana mofuta o fapaneng oa aterese.
Ho arola "6: 72057594041991168 (ce52f92a058c)" likarolo, re fumana:

database_id = 6
hobt_id = 72057594041991168
magic hash = (ce52f92a058c)

2.1) Hlakola database_id

E sebetsa hantle joalo ka mohlala o kaholimo! Re fumana lebitso la database re sebelisa potso:

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

Tabeng ea ka, hoa tšoana DB WideWorldImporters.

2.2) Hlakola hobt_id

Boemong ba database e fumanoeng, o hloka ho botsa sys.partitions ka maqhama a 'maloa a tla thusa ho tseba mabitso a tafole le index ...

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

E mpolella hore kopo e ne e emetse Kopo.Dinaha di notlela ho sebedisa index ya PK_Application_Countries.

2.3) Joale bakeng sa %%lockres%% magic - haeba u batla ho tseba hore na keno efe e notletsoeng.

Haeba ke hlile ke batla ho tseba hore na senotlolo se ne se hlokahala moleng ofe, nka tseba ka ho botsa tafole ka boeona. Re ka sebelisa ts'ebetso e sa ngolisoang ea %%lockres%% ho fumana keno e lumellanang le hash ea boselamose.
Hlokomela hore potso ena e tla hlahloba tafole eohle, 'me litafoleng tse kholo sena se ka se be monate ho hang:

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

Ke kentse NOLOCK (ka keletso ea Klaus Aschenbrenner ho twitter) hobane liloko e ka ba bothata. Re batla feela ho sheba se teng hona joale, eseng se neng se le teng ha transaction e qala - ha ke nahane hore ho lumellana ha data ho bohlokoa ho rona.
Voila, rekoto eo re e loanneng!

Liteboho le ho bala ho tsoelang pele

Ha ke hopole hore na ke mang ea qalileng ho hlalosa tse ngata tsa lintho tsena, empa mona ke tse peli tse mabapi le lintho tse seng kae tse ngotsoeng tseo u ka li ratang:

Poso ea Paul Randal mabapi le %%physloc%% le sys.fn_PhysLocFormatter (joalokaha re na le data ea rona mohlaleng oa pele)
Potso ho StackOverflow mabapi le ho sebelisa %%lockres%% (joalokaha re fumane data mohlaleng oa bobeli). E 'ngoe ea likarabo e lebisa posong Grant Fritchey e ka bang %%lockres%% e ngotsoeng morao koana ka 2010.

Source: www.habr.com

Decipher Key le Page WaitResource ka li-deadlocks le liloko