🥇Ukususa ukubethela kokhiye kanye ne-Page WaitResource kuma-deadlock kanye nezingidi

Uma usebenzisa umbiko wenqubo evinjiwe noma uqoqa amagrafu e-deadlock ahlinzekwa yi-SQL Server ngezikhathi ezithile, uzohlangabezana nezinto ezinjengalezi:

waitresource="IKHASI: 6:3:70133"

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

Kwesinye isikhathi, kuzoba nolwazi olwengeziwe kuleyo XML enkulu oyifundayo (amagrafu e-deadlock aqukethe uhlu lwezinsiza ezikusiza ukuthi uthole amagama ento nenkomba), kodwa hhayi njalo.

Lo mbhalo uzokusiza ukuthi uzichaze.

Lonke ulwazi olulapha liku-inthanethi ezindaweni ezihlukahlukene, lusakazwa kakhulu! Ngifuna ukukuhlanganisa konke, kusukela ku-DBCC PAGE kuye ku-hobt_id kuya emisebenzini engabhaliwe %%physloc%% kanye %%%lockres%%.

Okokuqala, ake sikhulume ngokulinda izingidi ze-PAGE, bese sidlulela kokuthi KEY lock.

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

Uma isicelo sakho silinde kukhiye we-PAGE, i-SQL Server izokunikeza ikheli lalelo khasi.

Ukuhlukanisa "IKHASI: 6:3:70133" sithola:

database_id = 6
idatha_file_id = 3
inombolo_yekhasi = 70133

1.1) Khipha ubunikazi besizindalwazi

Thola igama lesizindalwazi usebenzisa lo mbuzo:

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

Kusesidlangalaleni DB WideWorldImporters kuseva yami ye-SQL.

1.2) Ifuna igama lefayela ledatha - uma uthanda

Sizosebenzisa idatha_file_id esinyathelweni esilandelayo ukuze sithole igama lethebula. Ungavele weqele esinyathelweni esilandelayo, kodwa uma unentshisekelo egameni lefayela, ungalithola ngokusebenzisa umbuzo kumongo wesizindalwazi esitholiwe, ufake i-data_file_id kulo mbuzo:

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

Kusizindalwazi se-WideWorldImporters leli ifayela elibizwa ngokuthi WWI_UserData futhi ngilibuyisele ku-C:MSSQLDATAWideWorldImporters_UserData.ndf. (Eshu, ungibambe ngifaka amafayela ku-system drive! Cha! Kuyaphoxa).

1.3) Thola igama lento ku-DBCC PAGE

Manje sesiyazi ukuthi ikhasi #70133 ku-datafile 3 lingele-WorldWideImporters database. Singabheka okuqukethwe kwaleli khasi sisebenzisa i-DBCC PAGE esingabhaliwe futhi silandele ifulegi elithi 3604.
Qaphela: Ngincamela ukusebenzisa i-DBCC PAGE ekhophini ebuyiselwe ndawana thize kwenye iseva, ngoba iyinto engabhaliwe. Kwezinye izimo, yena kungase kubangele ukulahlwa (cishe. umhumushi - isixhumanisi, ngeshwa, asiholeli ndawo, kodwa ukwahlulela nge-url, sikhuluma ngezinkomba ezihlungiwe).

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

Ngokuskrola emiphumeleni, ungathola okuthi object_id kanye ne index_id.

Sengiyaqeda! Manje ungathola ithebula namagama ezinkomba ngombuzo:

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

Futhi lapha siyabona ukuthi ukulinda kukhiye bekukunkomba ye-PK_Sales_OrderLines yethebula le-Sales.OrderLines.

Qaphela: Ku-SQL Server 2014 nangaphezulu, igama lento lingatholakala kusetshenziswa i-DMO sys.dm_db_database_page_allocations engabhaliwe. Kodwa kufanele ubuze wonke amakhasi kusizindalwazi, okungabukeki kupholile kusizindalwazi esikhulu, ngakho ngisebenzise i-DBCC PAGE.

1.4) Ingabe kungenzeka ukubona idatha ekhasini elivinjiwe?

Nuuu, yebo. Kodwa... uqinisekile ukuthi uyayidinga ngempela?
Ihamba kancane ngisho ematafuleni amancane. Kodwa kuhle, njengoba usufunde kuze kube manje...ake sikhulume %%physloc%%!

I-%%physloc%% iwucezu lomlingo olungabhaliwe olubuyisela i-ID yangempela kokufakiwe ngakunye. ungasebenzisa %%physloc%% kanye ne-sys.fn_PhysLocFormatter ku-SQL Server 2008 nangaphezulu.

Manje njengoba sesiyazi ukuthi besifuna ukuvimba ikhasi kokuthi Sales.OrderLines, singabheka yonke idatha kuleli thebula, egcinwe kufayela ledatha #3 ekhasini #70133, nalo mbuzo olandelayo:

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

Njengoba ngishilo, ihamba kancane ngisho ematafuleni amancane. Ngengeze u-NOLOCK esicelweni ngoba namanje asinaso isiqiniseko sokuthi idatha esifuna ukuyibheka ifana ncamashi neyayinjalo ngesikhathi kutholwa ilokhi - ukuze sikwazi ukufunda ngokuphepha ngokuphepha.
Kodwa, hawu, umbuzo ungibuyisela yona kanye imigqa engama-25 umbuzo wethu owalwela yona.

Kwanele mayelana nokhiye we-PAGE. Kuthiwani uma silinde ukhiye we-KEY?

2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = I-Dabase_Id, HOBT_Id (i-hashi yomlingo engase isuswe ukubethela nge-%%lockres%% uma ufuna ngempela)

Uma umbuzo wakho uzama ukukhiya kokufakwayo kwenkomba bese ukhiyeleka wodwa, uthola uhlobo oluhluke ngokuphelele lwekheli.
Ukuphula okuthi “6:72057594041991168 (ce52f92a058c)” kube izingxenye, sithola:

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

2.1) Khipha ubunikazi besizindalwazi

Isebenza ngokufana ncamashi nesibonelo esingenhla! Sithola igama lesizindalwazi sisebenzisa umbuzo:

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

Endabeni yami, kuyafana DB WideWorldImporters.

2.2) Khipha i-hobt_id

Kumongo wesizindalwazi esitholiwe, udinga ukubuza ama-sys.partitions ngokujoyina okumbalwa okuzosiza ukucacisa amagama ethebula nenkomba ...

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

Ingitshela ukuthi isicelo besilindile kuhlelo lokusebenza.Amazwe akhiya kusetshenziswa inkomba ye-PK_Application_Countries.

2.3) Manje nge-%%lockres%% magic - uma ufuna ukuthola ukuthi yikuphi okufakiwe okukhiyiwe

Uma ngempela ngifuna ukwazi ukuthi iyiphi irowu ebidingeka kulo ilokhi, ngingathola ngokubuza ngethebula ngokwalo. Singasebenzisa umsebenzi ongabhaliwe %%lockres%% ukuze sithole okufakiwe okufana ne-hashi yomlingo.
Qaphela ukuthi lo mbuzo uzoskena ithebula lonke, futhi emathebula amakhulu lokhu kungase kungabi mnandi nhlobo:

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

Ngengeze i-NOLOCK (ngeseluleko sika-Klaus Aschenbrenner ku-twitter) ngoba izingidi zingaba yinkinga. Sifuna nje ukubheka ukuthi yini ekhona manje, hhayi ukuthi yini eyayikhona ngenkathi umsebenzi uqala - angicabangi ukuthi ukufana kwedatha kubalulekile kithi.
Voila, irekhodi esalilwela!

Ukubongwa nokufunda okuqhubekayo

Angikhumbuli ukuthi ubani oqale wachaza eziningi zalezi zinto, kodwa nakhu okuthunyelwe okubili mayelana nezinto ezibhalwe kancane ongazithanda:

Okuthunyelwe kukaPaul Randal mayelana %%physloc%% kanye ne-sys.fn_PhysLocFormatter (njengoba sinedatha yethu esibonelweni sokuqala)
Umbuzo ku-StackOverflow mayelana usebenzisa %%lockres%% (njengoba sithole idatha esibonelweni sesibili). Enye yezimpendulo iholela kokuthunyelwe U-Grant Fritchey cishe %%lockres%% owabhalwa emuva ngo-2010.

Source: www.habr.com

I-Decipher Key kanye ne-Page WaitResource kuma-deadlock kanye nezingidi