Kilitlenmelerde ve kilitlenmelerde Anahtarın ve Sayfa WaitResource'un şifresini çözün

Engellenen süreç raporunu kullanırsanız veya SQL Server'ın sağladığı deadlock grafiklerini periyodik olarak toplarsanız aşağıdaki gibi durumlarla karşılaşırsınız:

waitresource=“SAYFA: 6:3:70133“

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

Bazen üzerinde çalıştığınız dev XML'de daha fazla bilgi olabilir (kilitlenme grafikleri, nesne ve dizin adlarını bulmanıza yardımcı olan kaynakların bir listesini içerir), ancak her zaman değil.

Bu metin bunları çözmenize yardımcı olacaktır.

Burada bulunan tüm bilgiler internette çeşitli yerlerde bulunmaktadır, çok dağıtılmıştır! DBCC PAGE'den hobt_id'ye ve belgelenmemiş %%physloc%% ve %%lockres%% işlevlerine kadar her şeyi bir araya getirmek istiyorum.

Öncelikle PAGE kilitlerindeki beklemelerden bahsedelim, ardından KEY kilitlerine geçeceğiz.

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

Sorgunuz bir PAGE kilidinde bekliyorsa, SQL Server size o sayfanın adresini verecektir.

"PAGE: 6:3:70133" ifadesinin dökümü yapıldığında şunu elde ederiz:

• veritabanı_kimliği = 6
• veri_dosyası_kimliği = 3
• sayfa_numer = 70133

1.1) Database_id'nin şifresini çözün

Sorguyu kullanarak veritabanı adını bulalım:

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

Bu halka açık DB WideWorldImporters SQL Server'ımda.

1.2) Veri dosyasının adını arıyorsunuz - eğer ilgileniyorsanız

Bir sonraki adımda tablo adını bulmak için data_file_id kullanacağız. Basitçe bir sonraki adıma geçebilirsiniz, ancak dosya adıyla ilgileniyorsanız, bu sorguda data_file_id yerine, bulunan veritabanı bağlamında bir sorgu çalıştırarak onu bulabilirsiniz:

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

WideWorldImporters veritabanında bu, WWI_UserData adlı bir dosyadır ve onu C:MSSQLDATAWideWorldImporters_UserData.ndf konumuna geri yükledim. (Hata, beni sistem diskine dosya koyarken yakaladın! Hayır! Bu garipti).

1.3) Nesne adını DBCC PAGE'den alın

Artık veri dosyası 70133'teki #3 sayfasının WorldWideImporters veritabanına ait olduğunu biliyoruz. Belgelenmemiş DBCC PAGE'i ve izleme bayrağı 3604'ü kullanarak bu sayfanın içeriğine bakabiliriz.
Not: Belgelenmemiş bir şey olduğundan, DBCC PAGE'i başka bir sunucudaki bir yedekten geri yüklenen bir kopya üzerinde kullanmayı tercih ederim. Bazı durumlarda o bir dökümün oluşmasına neden olabilir (yaklaşık. çevirmen - bağlantı ne yazık ki hiçbir yere gitmiyor, ancak URL'ye bakılırsa filtrelenmiş dizinlerden bahsediyoruz).

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

Sonuçlar arasında gezinerek object_id ve index_id'yi bulabilirsiniz.

Neredeyse bitti! Artık sorguyu kullanarak tablo ve dizin adlarını bulabilirsiniz:

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

Ve şimdi kilit beklemenin Sales.OrderLines tablosunun PK_Sales_OrderLines dizininde olduğunu görüyoruz.

Not: SQL Server 2014 ve sonrasında nesne adı, belgelenmemiş DMO sys.dm_db_database_page_allocations kullanılarak da bulunabilir. Ancak veritabanındaki her sayfayı sorgulamanız gerekiyor ki bu büyük veritabanları için pek hoş görünmüyor, bu yüzden DBCC PAGE kullandım.

1.4) Engellenen sayfadaki verileri görmek mümkün müdür?

İyi evet. Ama... buna gerçekten ihtiyacın olduğundan emin misin?
Küçük masalarda bile yavaş. Ama bu çok hoş, madem bu kadar okudunuz... hadi %%physloc%% hakkında konuşalım!

%%physloc%%, her giriş için fiziksel bir tanımlayıcı döndüren, belgelenmemiş bir sihir parçasıdır. kullanabilirsiniz %%physloc%%, SQL Server 2008 ve üzeri sürümlerde sys.fn_PhysLocFormatter ile birlikte.

Artık Sales.OrderLines'da sayfayı kilitlemek istediğimizi bildiğimize göre, bu sorguyu kullanarak sayfa #3'teki veri dosyası #70133'te saklanan bu tablodaki tüm verilere bakabiliriz:

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

Dediğim gibi minik masalarda bile yavaş. İsteğe NOLOCK'u ekledim çünkü bakmak istediğimiz verilerin kilidin algılandığı andaki verilerle tam olarak aynı olduğuna dair hala bir garantimiz yok; böylece kirli okumaları güvenle yapabiliriz.
Ama yaşasın, sorgu bana sorgumuzun mücadele ettiği 25 satırın aynısını döndürüyor

PAGE kilitleri hakkında bu kadar yeter. Peki ya ANAHTAR kilidini bekliyorsak?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (eğer gerçekten istiyorsanız %%lockres%% kullanılarak şifresi çözülebilen sihirli karma)

Sorgunuz dizindeki bir kaydı kilitlemeye çalışırsa ve kendisi kilitlenirse, tamamen farklı türde bir adresle karşılaşırsınız.
“6:72057594041991168 (ce52f92a058c)”yi parçalara ayırdığımızda şunu elde ederiz:

• veritabanı_kimliği = 6
• hobt_id = 72057594041991168
• sihirli karma = (ce52f92a058c)

2.1) Database_id'nin şifresini çözün

Bu, yukarıdaki örnekle tamamen aynı şekilde çalışır! Sorguyu kullanarak veritabanı adını bulun:

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

Benim durumumda hala aynı DB WideWorldImporters.

2.2) hobt_id'nin şifresini çözün

Bulunan veritabanı bağlamında, tablo ve dizin adlarını belirlemeye yardımcı olacak bir çift birleştirmeyle sys.partitions'a bir sorgu yürütmeniz gerekir...

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

İsteğin, PK_Application_Countries dizinini kullanarak Application.Countries kilidini beklediğini söylüyor.

2.3) Şimdi biraz sihir %%lockres%% - hangi girişin kilitli olduğunu bulmak istiyorsanız

Kilidin hangi satırda olduğunu gerçekten bilmek istersem tabloyu sorgulayarak öğrenebilirim. Sihirli karmayla eşleşen bir girişi bulmak için belgelenmemiş %%lockres%% işlevini kullanabiliriz.
Lütfen bu sorgunun tüm tabloyu tarayacağını ve büyük tablolarda bunun hiç de eğlenceli olmayabileceğini unutmayın:

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

NOLOCK'u ekledim (Twitter'da Klaus Aschenbrenner'ın tavsiyesi üzerine) çünkü tıkanıklıklar sorun haline gelebilir. Biz sadece şu anda orada olana bakmak istiyoruz, işlem başladığında orada olana değil; veri tutarlılığının bizim için önemli olduğunu düşünmüyorum.
Voila, uğruna savaştığımız rekor!

Teşekkür ve daha fazla okuma

Bunların çoğunu ilk olarak kimin tanımladığını hatırlamıyorum, ancak hoşunuza gidebilecek en az belgelenmiş şeyler hakkında iki gönderi:

• Paul Randal'ın hakkındaki yazısı %%physloc%% ve sys.fn_PhysLocFormatter (ilk örnekte verilerimiz olduğu gibi)
• StackOverflow ile ilgili soru %%lockres%% kullanılıyor (İkinci örnekte verileri bulduğumuz gibi). Yanıtlardan biri bir gönderiye yönlendiriyor Grant Fritchey'e %%lockres%% hakkında, 2010'da yazılmış.

Kaynak: habr.com