یک بن بست زمانی رخ میدهد که دو جلسه (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)
4 دیدگاه
عاطفه حسن پور
این دوره اموزشی رو کیه دوباره میشه سفارش داد؟؟؟
ساناز احمدی
مرسی خیلی مفید بود
m
سلام
tiyara9090@hotmail.com
مرسی
خوب بود و لی سخت