عیب یابی بن بست (Deadlock)

یک بن بست زمانی رخ می‌دهد که دو جلسه (Session) هر یک برای  منبعی که دیگری قفل کرده است منتظر باشد ، در این حالت هیچ کدام از آنها نمی‍توانند ادامه پیدا کنند زیرا دیگری از بدست آوردن منبع مورد نیاز جلوگیری می‌کند.
توجه داشته باشید هنگامی که یک فرآیند قفلی را بر روی منبعی نگه می‌دارد دیگر فرآیندها برای بدست آوردن قفل باید تا پایان کار فرآیند اول صبر کنند که این انتظار ساده برای بدست آوردن قفل بن بست نیست .

انواع بن بست (Deadlock)‌: 

در Sql Server دو نوع عمده بن بست می تواند رخ دهد:

1- چرخه‌ی بن‌بست
2- تبدیل بن‌بست
در شکل زیر که از کتاب آنلاین Sql Server گرفته شده است ، یک نمونه ای از چرخه‌ی بن‌بست را نشان می‌دهد . تراکنش 1 آغاز می شود ،یک قفل انحصاری بر روی جدول Supplier بدست می‌آورد ، و در خواست یک قفل انحصاری برروی جدول Part را می‌دهد.

هم زمان تراکنش 2 آغاز می‌شود ، یک قفل انحصاری بر روی جدول Part بدست می‌آورد ، و در خواست یک قفل انحصاری بر روی جدول Supplier را می‌دهد. هر تراکنش منبع مورد نیاز تراکنش دیگر را نگه داشته است ، در این حالت بن بست رخ می‌دهد.
ما با استفاده از دستورات زیر می‌توانیم یک چرخه‌ی بن‌بست را تولید کنیم‌، در عوض جدول Part‌، ما از جدول Production.Product و در عوض جدول Supplier‌، ما از جدول Purchasing.PurchaseOrderDetail استفاده کرده‌ایم .

مرحله 1:

از پایگاه داده  AdventureWorks استفاده کنید

مرحله 2:

بر روی اولین اتصال تراکنش زیر را شروع کنید.

BEGIN  TRAN
UPDATE  Purchasing .PurchaseOrderDetail
SET      OrderQty = OrderQty  + 200
WHERE    ProductID = 922
AND  PurchaseOrderID  = 499;
GO

مرحله 3:

اتصال جدید دیگر را باز کنید و تراکنش زیر را اجرا کنید.

BEGIN  TRAN
UPDATE  Production .Product
SET      ListPrice  = ListPrice * 0.9
WHERE    ProductID = 922;
GO

مرحله 4 :

به اتصال مرحله 2 باز‌گردید و دستور زیر را اجرا کنید

UPDATE  Production .Product
SET      ListPrice  = ListPrice * 1.1
WHERE    ProductID = 922;
GO

توجه داشته باشید که در این مرحله بن‌بستی رخ نمی‌دهد و فقط منتظر بدست آوردن قفل می‌ماند .

مرحله 5 :

به اتصال مرحله 3 بازگردید و دستور زیر را اجرا کنید

UPDATE  Purchasing .PurchaseOrderDetail
SET      OrderQty = OrderQty  - 200
WHERE    ProductID = 922
AND  PurchaseOrderID  = 499;

UPDATE  Purchasing .PurchaseOrderDetail
SET      OrderQty = OrderQty  - 200
WHERE    ProductID = 922
AND  PurchaseOrderID  = 499;

در اینجا بن‌بست ‌رخ می‌دهد و پیغام خطای باز گردانده می‌شود.
در مثال بالا به دلیل اینکه هر تراکنش منبع مورد نیاز تراکنش دیگر را نگه داشته است بن‌بست رخ می‌دهد .

یافتن خودکار بن بست‌: 

Sql Server به طور خودکار بن‌بست ها را پیدا می‌کند و به واسطه بررسی مدیر قفل، بن بست‌های مربوط به قفل را پیدا می‌کند. یک Thread جداگانه سیستم را هر 5 ثانیه برای بن‌بست‌ها بررسی می‌کند که این نخ  LOCK_MONITOR نامیده می‌شود .
اگر نخ lock Monitor بن‌بستی را پیدا کند بازه یافتن بن بست از 5 ثانیه به 100  میلی ثانیه کاهش پیدا می‌کند. اگر نرخ  بن بست کاهش پیدا کند فواصل بررسی به هر 5 ثانیه باز می‌گردد . هنگامی که lock Monitor بن‌بست را برای یک Thread خاص بررسی می‌کند ، آن منبعی که Thread منتظرش است را شناسایی می‌کند.
سپس lock Monitor مالکین منبع را پیدا می‌کند و به صورت بازگشتی بازرسی بن‌بست را برای آن دسته از Threadها (در حال انتظار قفل) ادمه می‌دهد تا زمانی که آن دوری را پیدا کند. دور شناسایی شده در این روش تشکیل یک بن‌بست را می‌دهد.
بعد از این که بن‌بست پیدا شد، موتور پایگاه داده برای خاتمه بن بست  یکی از Thread‌ها را به عنوان قربانی بن‌بست انتخاب می کند. تراکنش قربانی بن‌بست Roll Back می شود و خطای  1205 به برنامه کاربردی برگردانده می‌شود . با Rollback شدن تراکنش تمامی قفلهای نگه داشته شده توسط آن رها می‌شود و دیگر Thread‌ها از حالت مسدود خارج می‌شوند.
به طور پیش فرض موتور پایگاه داده تراکنشی که کم‌ترین هزینه Roll Back را دارد به عنوان قربانی بن‌بست انتخاب می‌کند. با استفاده از دستور SET DEADLOCK_PRIORITY ما می‌توانیم به یک فرآیند درگیر در بن‌بست اولویت دهیم.
فرآیندی که کم‌ترین اولویت را داشته باشد به عنوان قربانی انتخاب می‌شود. ما می‌توانیم برای دستور SET  DEADLOCK_PRIORITY مقادیر HIGH ،‌NORMAL ، LOW و یا هر مقدار صحیحی در بازه 10- تا 10 را در نظر بگیریم. مقدار HIGH معادل 5، مقدار NORMAL معادل 0 و مقدار LOW معادل 5- است. اگر هر دو جلسه دارای اولویت یکسانی باشند، تراکنش جلسه ای که دارای کم‌ترین هزینه است به عنوان قربانی انتخاب می‌شود ، اگر هر دو جلسه دارای اولویت یکسان و هزینه‌ی یکسانی باشند یکی از جلسات به صورت تصادفی به عنوان قربانی انتخاب می‌شود .

پیدا کردن علت بن بست :

علاوه بر پیدا کردن بن‌بست به صورت خودکار‌، اطلاعاتی از فرآیندها و پرس وجوهای دخیل در بن‌بست تهیه می‌کند، ما با استفاده از این اطلاعات می‌توانیم دقیقاً تشخیص دهیم که چرا بن‌بست رخ داده است.
برای تعیین علت‌های یک بن‌بست، ما نیاز به دانستن منابع درگیر، انوع قفل‌های در خواست شده و بدست آمده داریم .  برای بدست آوردن این نوع اطلاعات  (Trace Flag  1222 (Sql Server1222 جایگزین 1204  Flag است ،که عموماً 1204 در نسخه های قبلی Sql Server استفاده می شد) را ارائه می‌دهد. با‌فعال کردن این Flag ، Sql Server خروجی در قالب یک گراف بن بست ارائه می‌دهد . این خروجی دستورهای اجرا شده در زمان رخ دادن بن بست را نشان می‌دهد.
برای ایجاد تصویری کامل از آنچه که موجب بن بست شده است ، ما نیاز به دانستن دستورهایی که قفل‌ها را در ابتدا بدست آورده اند و باعث مسدود شدن دستورهای بعدی شده‌اند داریم.
Sql Server به طور خودکار تاریخچه ای کامل از همه ی دستورات اجرا شده توسط هر آفریند ثبت نمی‌کند ، بنابراین این اطلاعات در Trace Flag 1222 موجود نیست. ما برای بدست آوردن این تاریخچه باید نرم فزاری مانند Sql Trace را اجرا کنیم. هنگامی که ما flag 1222 را فعال می کنیم، خروجی آن به Sql Server Error Log ارسال می شود. بهترین راه برای خواندن خروجی 1222 کپی کردن error log به جای دیگر (Notepad‌) است و ما با استفاده از این کار می‌توانیم اطلاعات فرعی را حذف کنیم و فقط اطلاعات مربوط به بن بست را مشاهده کنیم .
خروجی دستور 1222 در یک قالبی شبیه به XML است اما آن با هیچ یک از قالبهای XSD مطابقت ندارد، بنابراین این خروجی نمی‌تواند با استفاده از ابزارهایی مانند XML Notepad خوانده شود.

ما می‌توانیم خروجی XML ،Trace Flag 1222 را به سه قسمت تقسیم کنیم:

1- قربانی بن‌بست (Deadlock Victim)
2- فرآیندهای شرکت کننده (Participant  Processes)
3- منابع
ما ابتدا Flag 1222 را با استفاده از دستور زیر فعال می‌کنیم و سپس خروجی آن را برای مثال قبل رخ دادن بن‌بست بررسی می‌کنیم

DBCC TRACEON (1222, -1);
GO

Deadlock victim:

اولین بخش خروجی Flag 1222 مربوط به قربانی بن‌بست است. این امر مفید است که ما در زمان بررسی خروجی 1222 بدانیم کدام اطلاعات مربوط به قربانی می‌شود .

deadlock-list
deadlock victim= process593048

Processes:

بخش بعدی، فرآیند‌های قربانی و بازمانده را مشخص می‌کند . این بخش دارای اطلاعات بیشتری می‌باشد. در مثال ما اولین Process Listed قربانی است که شما می‌توانید آن را از روی مقدار Process Id تشخیص دهید . توجه داشته باشید که همیشه اولین فرآیند قربانی نیست (برای مثال می تواند فرآیند بازمانده باشد ).

process-list
:process id=process593048 taskpriority=0 logused=248 waitresource=KEY
13:72057594045661184 (3e75cd3a78e7) waittime=4091 ownerId=7381
transactionname=user_transaction lasttranstarted=2012-05-14T16:27:27.520
XDES=0x8414d950 lockMode=U schedulerid=3 kpid=7696 status=suspended spid=52
sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-05-14T16:27:57.300
lastbatchcompleted=2012-05-14T16:27:27.520 clientapp=Microsoft SQL Server
Management Studio - Query hostname=TENAR hostpid=9148 loginname=TENAR\Kalen
isolationlevel=read committed (2) xactid=7381 currentdb=13 lockTimeout=4294967295
clientoption1=671090784 clientoption2=390200
executionStack
frame procname=adhoc line=1 sqlhandle=0x020000004fbb092c29dceca676884294d
f83a6c4d191eec8
(@1 int,@2 smallint,@3 smallint)UPDATE [Purchasing].[PurchaseOrderDetail] set
[OrderQty] = [OrderQty]-@1  WHERE [ProductID]=@2 AND [PurchaseOrderID]=@3
frame procname=adhoc line=1 sqlhandle=0x02000000a63fcb0cdeaa5cafca251aa15
18ff286a8a78917
UPDATE  Purchasing.PurchaseOrderDetail
SET     OrderQty = OrderQty - 200
WHERE   ProductID = 922
AND PurchaseOrderID = 499
inputbuf
UPDATE  Purchasing.PurchaseOrderDetail
SET     OrderQty = OrderQty - 200
WHERE   ProductID = 922
;AND PurchaseOrderID = 499

برای اولین فرآیند (593048) اطلاعات زیادی وجود دارد اما برخی از اطلاعاتی که شما انتظار دارید وجود داشته باشد شامل: منبع انتظار (wait resource)، شماره پایگاه داده (Currentdb )، شماره جلسه (Spid) و Input Buffer که دستور اجرا شده مسدود را در بردارد، است.  همچنین شما می‌توانید در قسمت ExecutionStack  دستور ی را که واقعاً منبع را بدست آورده است مشاهده کنید.

Process Listed بعدی هم شبیه به خروجی قبل است:

process-list
process id=process5934c8 taskpriority=0 logused=2176 waitresource=KEY:
13:72057594044678144 (bd095ec17235) waittime=17491 ownerId=7180
transactionname=user_transaction lasttranstarted=2012-05-14T16:27:17.970
XDES=0x844f7950 lockMode=X schedulerid=3 kpid=3528 status=suspended spid=55
sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-05-14T16:27:43.907
lastbatchcompleted=2012-05-14T16:27:17.970 clientapp=Microsoft SQL Server
Management Studio - Query hostname=TENAR hostpid=9148 loginname=TENAR\Kalen
isolationlevel=read committed (2) xactid=7180 currentdb=13 lockTimeout=4294967295
clientoption1=671090784 clientoption2=390200
executionStack
frame procname=adhoc line=1 stmtstart=58 sqlhandle=0x0200000040756627d058
1021091c2bcb38bd70b4892954f4
UPDATE [Production].[Product] set [ListPrice] = [ListPrice]*@1  WHERE
[ProductID]=@2
frame procname=adhoc line=1 sqlhandle=0x02000000288f20321de34ef8cb5d031cb
9a00cb157ae7069
UPDATE  Production.Product
SET     ListPrice = ListPrice * 0.9
WHERE   ProductID = 922;
inputbuf
UPDATE  Production.Product
SET     ListPrice = ListPrice * 0.9
;WHERE   ProductID = 922

منابع به بن‌بست رسیده:

قسمت پایانی خروجی 1222 لیستی از منابع درگیر در بن بست را نشان می‌دهد .

resource-list
keylock hobtid=72057594045661184 dbid=13 objectname=AdventureWorks.
Purchasing.PurchaseOrderDetail indexname=PK_PurchaseOrderDetail_PurchaseOrderID_
PurchaseOrderDetailID id=lock8010c780 mode=X associatedObjectId=72057594045661184
owner-list
owner id=process5934c8 mode=X
waiter-list
waiter id=process593048 mode=U requestType=wait

keylock hobtid=72057594044678144 dbid=13 objectname=AdventureWorks.
Production.Product indexname=PK_Product_ProductID id=lock82d75f00 mode=X associated
ObjectId=72057594044678144
owner-list
owner id=process593048 mode=X
waiter-list
waiter id=process5934c8 mode=X requestType=wait

در این حالت اگر ما ساختار جدولمان را بدانیم، ما متوجه می‌شویم که هر دو ایندکس PK PurchaseOrderDetail PurchaseOrderID PurchaseOrderDetailID و PK Product ProductID Clustered Primary Keys هستند.
همچنین ما می‌دانیم که یک key lock به یک سطری از جدول دلالت دارد. دریک نگاه ما می توانیم بگوییم که یک فرآیند یک قفل انحصاری بر روی سطری از جدول AdventureWorks.Purchasing.PurchaseOrderDetail را بدست آورده است و فرآیند دیگر منتظر بدست آوردن یک قفل U است.
در همان زمان، فرآیند دیگری قفل انحصاری را بر روی سطری از جدول AdventureWorks.Production.Product بدست آورده است و اولین فرآیند منتظر بدست آوردن قفل انحصاری بر روی آن است .

به حداقل رساندن بن‌بست‌ها‌:

توجه داشته باشید به ندرت می‌توان تضمین کرد که بن بست هرگز رخ نخواهد داد . Tuning برای بن‌بست‌ها عمدتاً شامل به حداقل رساندن وقوع آن‌ها است بیشتر روش‌های به حداقل رساندن بن‌بست‌ها شبیه به روش‌های عمومی حداقل رساندن مشکل Blocking است (برای کسب اطلاعات بیشتر در مورد Blocking فیلم Blocking و مسائل مربوط به آن در SQL Server را دانلود کنید) با این حال فقط یک روش برای پرهیز از وضعیت بن‌بست وجود دارد. اگر تراکنش‌ها بر اساس قانونی از پیش تعریف شوند می‌توان از چرخه بن‌بست پرهیز کرد .
برای مثال، اگر در مثال قبلی رخ دادن بن‌بست تراکنش‌ها همیشه  اول به جدول Product دسترسی داشته باشند و سپس به جدول PurchaseOrderDetail دسترسی داشته باشند می‌توان از بن بست جلوگیری کرد. زیرا اولین تراکنش در آغاز قفل X را بر روی جدول اول بدست می‌آورد و تراکنش دیگر منتظر آزادشدن قفل تراکنش اول می‌شود‌. انتظار فرآیند برای قفل طبیعی و عادی است.
توجه داشته باشید که انتظار برای قفل حتی در طولانی مدت با بن‌بست یکسان نیست .
همیشه سعی کنید پروتکل استانداردی را برای ترتیب دسترسی تراکنش‌ها به جداول پیاده‌سازی کنید.

برای مشاهده فیلم Deadlock چیست بر روی دکمه زیر کلیک کنید (قسمتی از دوره آموزشی Performance & Tuning)