Decipher Key आणि Page WaitResource डेडलॉक आणि लॉकमध्ये

तुम्ही ब्लॉक केलेला प्रक्रिया अहवाल वापरत असल्यास किंवा SQL सर्व्हरने वेळोवेळी दिलेला डेडलॉक आलेख गोळा केल्यास, तुम्हाला यासारख्या गोष्टींचा सामना करावा लागेल:

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

तुमची विनंती पृष्ठ लॉकवर प्रतीक्षा करत असल्यास, SQL सर्व्हर तुम्हाला त्या पृष्ठाचा पत्ता देईल.

"PAGE: 6:3:70133" खंडित केल्याने आम्हाला मिळते:

• डेटाबेस_आयडी = 6
• data_file_id = 3
• पृष्ठ_क्रमांक = ७०१३३

१.१) डेटाबेस_आयडी डिक्रिप्ट करा

क्वेरी वापरून डेटाबेसचे नाव शोधा:

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

हे सार्वजनिक आहे डीबी वाइडवर्ल्ड इम्पोर्टर्स माझ्या SQL सर्व्हरवर.

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 पृष्‍ठ आणि ट्रेस फ्लॅग 3604 वापरून पाहू शकतो.
टीप: मी दुस-या सर्व्हरवर कुठेतरी पुनर्संचयित कॉपीवर DBCC PAGE वापरण्यास प्राधान्य देतो, कारण ती कागदपत्र नसलेली सामग्री आहे. काही प्रकरणांमध्ये, ती डंप होऊ शकते (अंदाजे अनुवादक - दुवा, दुर्दैवाने, कोठेही नेत नाही, परंतु url नुसार, आम्ही फिल्टर केलेल्या निर्देशांकांबद्दल बोलत आहोत).

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

परिणामांवर स्क्रोल करून, तुम्ही ऑब्जेक्ट_आयडी आणि इंडेक्स_आयडी शोधू शकता.

जवळजवळ पूर्ण! आता आपण क्वेरीसह सारणी आणि अनुक्रमणिका नावे शोधू शकता:

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

आणि येथे आपण पाहतो की लॉकवरील प्रतीक्षा Sales.OrderLines टेबलच्या PK_Sales_OrderLines निर्देशांकावर होती.

टीप: SQL सर्व्हर 2014 आणि त्यावरील, ऑब्जेक्टचे नाव कागदोपत्री नसलेले DMO sys.dm_db_database_page_allocations वापरून देखील आढळू शकते. परंतु तुम्हाला डेटाबेसमधील प्रत्येक पृष्ठाची चौकशी करावी लागेल, जे मोठ्या डेटाबेससाठी फार छान दिसत नाही, म्हणून मी DBCC पृष्ठ वापरले.

1.4) अवरोधित केलेल्या पृष्ठावरील डेटा पाहणे शक्य आहे का?

Nuuu, होय. पण… तुम्हाला याची खरोखर गरज आहे का?
अगदी लहान टेबलांवरही ते धीमे आहे. पण हे खूप छान आहे, म्हणून तुम्ही आतापर्यंत वाचले आहे... %%physloc%% बद्दल बोलूया!

%%physloc%% हा जादूचा एक कागदोपत्री नसलेला भाग आहे जो प्रत्येक एंट्रीसाठी एक भौतिक आयडी देतो. आपण वापरू शकता SQL सर्व्हर 2008 आणि त्यावरील sys.fn_PhysLocFormatter सह %%physloc%%.

आता आम्हाला माहित आहे की आम्हाला 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)” भागांमध्ये मोडून, ​​आम्हाला मिळते:

• डेटाबेस_आयडी = 6
• hobt_id = 72057594041991168
• मॅजिक हॅश = (ce52f92a058c)

१.१) डेटाबेस_आयडी डिक्रिप्ट करा

हे वरील उदाहरणाप्रमाणेच कार्य करते! आम्हाला क्वेरी वापरून डेटाबेसचे नाव सापडते:

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

माझ्या बाबतीत, ते समान आहे डीबी वाइडवर्ल्ड इम्पोर्टर्स.

2.2) hobt_id डिक्रिप्ट करा

सापडलेल्या डेटाबेसच्या संदर्भात, तुम्हाला sys.partitions ला काही जोडण्यांसह क्वेरी करणे आवश्यक आहे जे टेबल आणि इंडेक्सची नावे निश्चित करण्यात मदत करेल ...

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

PK_Application_Countries इंडेक्स वापरून Application.Countries lock वर विनंतीची प्रतीक्षा करत असल्याचे ते मला सांगते.

2.3) आता काही %%lockres%% जादूसाठी - तुम्हाला कोणती एंट्री लॉक केली आहे हे शोधायचे असेल तर

लॉक कोणत्या पंक्तीवर आवश्यक आहे हे मला खरोखर जाणून घ्यायचे असल्यास, मी टेबलवरच क्वेरी करून शोधू शकतो. मॅजिक हॅशशी जुळणारी एंट्री शोधण्यासाठी आम्ही कागदपत्र नसलेले %% lockres%% फंक्शन वापरू शकतो.
लक्षात घ्या की ही क्वेरी संपूर्ण टेबल स्कॅन करेल आणि मोठ्या टेबलवर हे कदाचित मजेदार नसेल:

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

मी NOLOCK जोडले (twitter वर क्लॉस Aschenbrenner च्या सल्ल्यानुसार) कारण लॉक एक समस्या असू शकतात. आम्हाला फक्त आता तिथे काय आहे ते पहायचे आहे, आणि व्यवहार सुरू झाला तेव्हा काय नव्हते - मला वाटत नाही की डेटाची सातत्य आमच्यासाठी महत्त्वाची आहे.
व्होइला, आम्ही ज्या विक्रमासाठी लढलो!

पावती आणि पुढील वाचन

यापैकी बर्‍याच गोष्टींचे वर्णन प्रथम कोणी केले हे मला आठवत नाही, परंतु तुम्हाला आवडतील अशा किमान दस्तऐवजीकरण केलेल्या गोष्टींबद्दल येथे दोन पोस्ट आहेत:

• पॉल रँडल यांची पोस्ट बद्दल %%physloc%% आणि sys.fn_PhysLocFormatter (पहिल्या उदाहरणात आमचा डेटा आहे)
• StackOverflow बद्दल प्रश्न %%lockres%% वापरत आहे (जसे आम्हाला दुसऱ्या उदाहरणात डेटा सापडला). उत्तरांपैकी एक पोस्ट नेतो ग्रँट फ्रिचे बद्दल %%lockres%% 2010 मध्ये परत लिहिले.

स्त्रोत: www.habr.com