ردیابی کردن deadlock در Sql server

ردیابی کردن deadlock در Sql server

یک مسئله ی متداول در مورد SQL Server مسئله ی deadlock می باشد. deadlock زمانی اتفاق می افتد که یک یا چند فرایند روی یک منبع مشترک در انتظار هستند و هر فرایند منتظر تکمیل شدن فرایند دیگر می باشد. وقتی چنین موقعیتی اتفاق می افتد و راهی برای حل این تضاد وجود ندارد، SQL Server یکی از این فرایندها را به عنوان قربانی deadlock انتخاب خواهد کرد و آن را به عقب باز می گرداند. بنابراین فرایند و یا فرایندهای دیگر می توانند به جلو پیش بروند.

وقتی چنین چیزی اتفاق می افند، به طور پیش فرض برنامه ی شما ممکن است با خطاهایی مواجه شود، اما هیچ موردی در SQL Server Error Log یا Windows Event Log وجود ندارد که چنین اتفاقی را به شما اطلاع رسانی کند. پیغام خطایی که SQL Server به کاربر ازسال می کند، مشابه پیغام زیر میباشد:

Msg 1205, Level 13, State 51, Line 3‎
Transaction (Process ID xx) was deadlocked on {xxx} resources with another ‎process ‎
and has been chosen as the deadlock victim. Rerun the transaction.

در این آموزش به بررسی مراحلی خواهیم پرداخت که اطلاعاتی در مورد deadlock و حل مشکل مربوط به آن می کند.

توضیح

اطلاعات deadlock از طریق SQL Server Error Log یا با استفاده از Profiler / Server Side Trace به دست می آید.

Trace Flags

اگر بخواهید این اطلاعات را در SQL Server Error Log بگیرید، نیاز به فعالسازی یک هردوی این پرچم ها دارید:

• 1204: اطلاعاتی در مورد گره هایی موجود در deadlock ارائه می دهد.
• 1222: اطلاعات deadlock را در یک فرمت XML ارائه می دهد.

شما می توانید هر کدام از آنها را به طور مجزا یا با یکدیگر مورد استفاده قرار دهید.

برای روشن کردن آنها باید دستورت زیر را در یک پنجره ی query منتشر کنید و یا می توانید startup parameters را اضافه کنید. اگر این پرچم ها از یک پنجره ی query روشن شوند، دفعه ی بعدی که SQL Server آنها را آغاز کند، فعال نخواهند بود. بنابراین اگر می خواهید همیشه به این داده ها دسترسی داشته باشید، startup parameter بهترین گزینه می باشد.

DBCC TRACEON (1204, -1)
DBCC TRACEON (1222, -1)‎

Trace Flag 1222 Output

Trace Flag 1204 Output

Trace Flag 1204 Output

Profiler بدون روشن شدن trace flag ها کار می کند و سه رویداد وجود دارند که می توانند برای deadlock دریافت شوند. هر کدام از این رویدادها در گرو رویداد Locks قرار دارد.

• Deadlock graph - همزمان با گروه رویداد Lock:Deadlock اتفاق می افتد. گروه رویداد Deadlock Graph یک توصیف XML از deadblock ارائه می دهد.
• Lock: Deadlock – نشان می دهد که دو تراکنش همزمان با سعی در برقراری قفل های ناسازگار روی منابعی که دیگر تراکنش ها دارند، یکدیگر را بلاک کرده اند.
• Lock: Deadlock Chain- برای هرکدام از رویدادهایی که منجر به deadlock می شوند، تولید می شود.

Event Output

در تصویر زیر تنها سه رویداد ذکر شده در بالا را دریافت کرده ام.

Deadlock Graph Output

در زیر deadlock graph می باشد که خروجی رویداد Deadlock graph می باشد. مشاهده می کنیم که در سمت چپ id 62 به عنوان قربانی deadlock (deadlock victim) انتخاب شده است. همچنین اگر روی بیضی با علامت ضربدر (X) دقت کنید، تراکنشی که در حال اجرا بود را مشاهده خواهید کرد:

Finding Objects Involved in Deadlock

در هر سه خروجی object IDs را برای آبجکت هایی که در مغایرت هستند، هایلایت کرده ام. می توانید از query زیر برای یافتن آبجکت و جایگزینی object ID برای partition_id در زیر استفاده کنید.

SELECT OBJECT_SCHEMA_NAME([object_id]), 
OBJECT_NAME([object_id]) 
FROM sys.partitions 
WHERE partition_id = 289180401860608;‎

Saving Deadlock Graph Data in XML File

از آنجایی که داده deadlock graph در یک فرمت XML ذخیره می شود، می توانید رویدادهای XML را به طور مجزا ذخیره کنید. در هنگام تنظیم Trace Propertiesروی Events Extraction Settings کلیک کرده و این گزینه را مانند زیر فعال سازید.