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

توجه داشته باشید هنگامی که یک فرآیند قفلی را بر روی منبعی نگه می دارد دیگر فرآیندها برای بدست آوردن قفل باید تا پایان کار فرآیند اول صبر کنند که این انتظار ساده برای بدست آوردن قفل بن بست نیست .

انواع بن بست (Deadlock) :
در sql server دو نوع عمده بن بست می تواند رخ دهد :

1- چرخه ی بن بست.

2- تبدیل بن بست .

در شکل زیر که از کتاب آنلاین sql server گرفته شده است ، یک نمونه ای از چرخه ی بن بست را نشان می دهد . تراکنش 1 آغاز می شود ،یک قفل انحصاری بر روی جدول Supplier بدست می آورد ، و در خواست یک قفل انحصاری برروی جدول part را می دهد.

هم زمان تراکنش 2 آغاز می شود ، یک قفل انحصاری بر روی جدول partبدست می آورد ، و در خواست یک قفل انحصاری بر روی جدول Supplier را می دهد . هر تراکنش منبع مورد نیاز تراکنش دیگر را نگه داشته است ، در این حالت بن بست رخ می دهد .

ما با استفاده از دستورات زیر می توانیم یک چرخه ی بن بست را تولید کنیم ، در عوض جدول part ، ما از جدول Production.Product و در عوض جدول Supplier ، ما از جدول Purchasing.PurchaseOrderDetail استفاده کرده ایم .

مرحله 1:
از پایگاه داده AdventureWorksاستفاده کنید

مرحله 2:
بر روی اولین اتصال تراکنش زیر را شروع کنید

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

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

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

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

[sql]
UPDATE Production .Product
SET ListPrice = ListPrice * 1.1
WHERE ProductID = 922;
GO
[/sql]

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

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

[sql]
UPDATE Purchasing .PurchaseOrderDetail
SET OrderQty = OrderQty – 200
WHERE ProductID = 922
AND PurchaseOrderID = 499;
[/sql]

در اینجا بن بست رخ می دهد و پیغام خطای زیر باز گردانده می شود

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

یافتن خودکار بن بست :
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 است . اگر هر دو جلسه دارای اولویت یکسانی باشند ، تراکنش جلسه ای که دارای کمترین هزینه است به عنوان قربانی انتخاب می شود ، اگر هر دو جلسه دارای اولویت یکسان و هزینه ی یکسانی باشند یکی از جلسات به صورت تصادفی به عنوان قربانی انتخاب می شود .

پیدا کردن علت بن بست :
علاوه بر پیدا کردن بن بست به صورت خودکار ، sql server اطلاعاتی از فرآیندها و پرس وجوهای دخیل در بن بست تهیه می کند ، ما با استفاده از این اطلاعات می توانیم دقیقاً تشخیص دهیم که چرا بن بست رخ داده است .

برای تعیین علت های یک بن بست ، ما نیاز به دانستن منابع درگیر ، انوع قفلهای در خواست شده و بدست آمده داریم . برای بدست آوردن این نوع اطلاعات اس کیو ال سرور Trace flag 1222 (1222 جایگزین 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 را با استفاده از دستور زیر فعال می کنیم و سپس خروجی آن را برای مثال قبل رخ دادن بن بست بررسی می کنیم

[sql]
DBCC TRACEON (1222, -1);
GO
[/sql]

Deadlock victim :
اولین بخش خروجی flag 1222 مربوط به قربانی بن بست است . این امر مفید است که ما در زمان بررسی خروجی 1222 بدانیم کدام اطلاعات مربوط به قربانی می شود .

[sql]
deadlock-list
deadlock victim= process593048
[/sql]

Processes :
بخش بعدی ، فرآیند های قربانی و بازمانده را مشخص می کند . این بخش دارای اطلاعات بیشتری می باشد . در مثال ما اولین process listed قربانی است که شما می توانید آن را از روی مقدار process id تشخیص دهید . توجه داشته باشید که همیشه اولین فرآیند قربانی نیست (برای مثال می تواند فرآیند بازمانده باشد ) .

[sql]
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;
[/sql]

برای اولین فرآیند (593048) اطلاعات زیادی وجود دارد اما برخی از اطلاعاتی که شما انتظار دارید وجود داشته باشد شامل : منبع انتظار (wait resource) ، شماره پایگاه داده (currentdb ) ، شماره جلسه (spid) و input buffer که دستور اجرا شده مسدود را در بردارد ، است . همچنین شما می توانید در قسمت executionStack دستور ی را که واقعاً منبع را بدست آورده است مشاهده کنید.

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

[sql]
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;
[/sql]

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

[sql]
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
[/sql]

در این حالت اگر ما ساختار جدول مان را بدانیم ، ما متوجه می شویم که هر دو ایندکس 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)