اجازه بدین تو شروع بحث یه سوال مطرح کنم، آیا لاک شدن Object ها تو SQL Server اتفاق بدیه؟ نه، اینطور نیست. locking ابزار engine SQL برای حفظ خاصیت integrity دیتا در دیتابیس هست، تا تراکنش رو وادار کنه هر چهار خاصیت رو پاس کنه. در مورد این چهار خاصیت و جزییات مربوط به اون توی مطلب صحبت کردیم. درواقع SQL Server ، برای جلوگیری از مشکلات Concurrency نیاز به لاک کردن در سطوح مختلف دیتا داره. لاک ها ساختار in - memory دارن یعنی تو سطح رم اتفاق میوفتن و نه دیسک. سطح row : یک یا چند رکورد در سطح دیتابیس لاک می‌شه. سطح key : یک یا چند رکورد در ایندکس لاک می‌شه تا از بهم خوردن کلید ایندکس مورد استفاده جلوگیری کنه. سطح page : کوچکترین واحد نگهداری دیتا در SQL Server یعنی page با اندازه KB لاک میشه. سطح Extent : مجموعه page با هم یه extent تشکیل میدن گاهی یک extent لاک میشه. سطح table : گاهی پیش میاد که تمام جدول لاک بشه. سطح DB : بدترین نوع لاک، لاک شدن کل دیتابیس هست. لاک شدن Object ها در SQL Server تو سرور Develop به عنوان مشکل بروز نمیکنه چون ما به تنهایی یا به همراه چند برنامه نویس دیگه داریم رو اپلیکیشن کار میکنیم اما به محض انتقال پروژه به سرور production ، ممکنه بروز کنه. این همونجایی که تلفن شما زنگ میخوره و بهتون میگن که فلان صفحه لود نمیشه یا خیلی کند لود میشه. مثال lock و block . این میشه کل مفاهیمی که میخوایم در موردش صحبت کنیم. بیایین تعریف بالا رو به زبان SQL Server بگیم. فرض کنین کاربر A رو شروع کرده _ begin transaction داره با از دیتا کار میکنه _ یه لاک تو سطح Object اتفاق افتاده. مثلا سطح رکورد یا جدول کاربر B دیگه ای رو شروع میکنه _ begin transaction میاد سراغ که کاربر A هنوز باهاش کار داره Concurrency اینجا SQL سرور بنا به انجام شده Isolation Level به کاربر B میگه باید wait از نوع LCK که مربوط به blocking هست. تا کاربر A کارش Commit یا Rollback پس فهمیدم که locking وقتی پیش میاد که کاربری بخشی از دیتا رو به طور اختصاصی در اختیار بگیره و کاربر دیگه ای رو که میخواد با اون دیتا کار کنه Block کنه. اما Deadlock کی اتفاق می افته؟ مثال Deadlock : فرض کنین کاربر A بخش از دیتا رو لاک کرده، کابر B هم همزمان بخش دیگه ای از دیتا رو لاک کرده. کاربر A تو ادامه تراکنش نیاز به بخش دیتا داره که کاربر B لاکش کرده. کابر B هم تو ادامه کارش به بخش دیتایی نیاز داره که کابر A اول لاکش کرده. هیچ کدوم از تراکنش ها نمیتونه تموم بشه چون هر کدوم wait لاکی هست که توسط اون یکی ایجاد شده. به این حالت Deadlock گفته میشه. در این صورت SQL Server تفنگشو بر میداره و میگیره سمت تراکنشی که هزینه Rollback کمتری داره و اصطلاحا اون رو Kill میکنه. به طور پیش فرض مفهموم lock time out روی SQL Server وجود نداره. برای اپلیکیشنی که مراحل اولیه رو طی کرده و کارایی لازم رو داره کمتر پیش میاد مشکل Deadlock وجود داشته باشه، درضمن چون deadlock همراه با نتیجه fail هست پس این وظیفه برنامه نویس های که این موارد رو تو کد هندل کنن و retry کنن. چطور Blocking رو در SQL Server کنترل کنیم؟ دو راه حل وجود داره: تو این مطلب میخوایم مثالی برای راه حل اول بزنیم و راهکار ممکن رو بررسی کنیم. از محیط SQL Server استفاده میکنیم به همراه دیتابیس دمو Stack Overflow . تمام تستی هم که میخواهیم انجام بدیم مربوط به جدول users این دیتابیس هست. اول مطمین میشیم که جدول users فقط PK داشته باشه و هیچ ایندکسی جز کلاستر براش ساخته نشده باشه. چک میکنیم که Isolation level دیتابیس به حالت پیش فرض یعنی read commited باشه. کویری اجرا میکنیم تا ببینیم چنتا از کاربرا LastAccessDate مربوط به بازه مورد نظر دارن. کاربر با این شرط پیدا شد. حالا یه تراکنش را به شکل زیر اجرا میکنیم. یه کویری آپدیت روی جدول Users نوشتیم که reputation رو برای کاربرانی که LastAccessDate بین بازه مشخص شده رو دارن، تا زیاد کنه. دقت کنین که تراکنش رو Commit نکردیم یعنی تراکنش باز میمونه. یه تب جدید باز میکنیم و کویری select رو دوباره اجرا میکنیم. اتفاقی که میوفته اینکه کویری درحال اجرا باقی می مونه و هیچ وقت نتیجه ای بما برگردونده نمیشه، اما چرا؟ بهتره رو دانلود کنید و روی دیتابیستون ایجادش کنین تا بتونیم ادامه مراحل رو باهم پیش ببریم. با اجرا کویری زیر میتونیم بفهمیم چه کویری هایی درحال اجرا هستن و چه lock رو ایجاد کردن. نتیجه این شکلی میشه: همونطور که میبینیم کویری select توسط کویری Update که تراکنش commit نشده هست wait LCK شده. حالا کویری آپدیت رو Rollback میکنیم و یه ایندکس روی ستون های ID و LastAccessDate تو جدول Users ایجاد میکنیم. حالا مجددا کویری آپدیت رو اجرا میکنیم. و بعد کویری Select رو اجرا میکنیم. اینبار خیلی سریع کویری select جواب داد، در صورتی که کویری Update همچنان داخل تراکنش باز هست، مجددا sp _ whoisactive رو اجرا میکنیم. اینبار فقط یه رکورد برمیگردونه که مربوط به کویری Update هست، یعنی با اینکه کویری update لاک رو ایجاد کرده اما کویری Select تونست اجرابشه. ایندکس ها فقط سرعت اجرا کویری رو افزایش نمیدن به حل مسیله Concurrency هم کمک میکنن. چرا که با ساخت ایندکس Nonclustered ما یه کپی کوچکتری از جدولمون ایجاد کردیم که فقط شامل دو ستون مورد نیازمون هست. شبیه به اینکه یک جدول اصلی داشته باشیم و یه جدول خلاصه شده. وقتی کویری رو جدول اصلی اجرا شد و اونو لاک کرد، SQL Server تشخیص داد که کپی دیگه ای هم وجود داره که میتونه از اون استفاده کنه و با دونستن این مطلب که کویری اول تاثیری تو نتیجه کویری دوم نداره اجازه استفاده از ایندکس Nonclustered رو بهش داد. حالا یه تغییر کوچیک تو کویری Select میدیم. آیا همچنان کویری اجرا میشه یا block میشه؟ ما ستون DispayName رو هم به کویری اضافه کردیم. میدونیم که این ستون تو ایندکس ساخته شده مون نبود. یعنی کپی از جدول که این دیتا رو داشته باشه وجود ندارد، پس ناچاره بره سراغ دیتا اصلی و این اتفاق میوفته. کویری select جدیدمون توسط کویری update که تراکنش جاری هست Block شد. نتیجه گیری: همیشه ایندکس ها رو برای سریعتر کردن اجرا کویری ها ساختیم. اینجا دیدیم که ایندکس های Nonclustered بخاطر تعریفی که دارن و کپی مستقلی از جدول رو جای دیگه و تو Page های جداگانه نگه میدارن میتونن برای حل مشکل Concurrency و جلو گیری از Blocking مفید باشن. توصیه ای که وجود داره ساخت تعداد محدودی ایندکس برای هر جدول هست مثلا حداکثر ایندکس که شامل حداکثر فیلد هستن میتونه شروع خوبی باشه. اینکه SQL Server کی تصمیم میگیره لاک رو تو سطح رکورد نگه داره یا کل جدول رو لاک کنه از دست ما خارج هست و نمیتونیم کنترلش کنیم اما میدونیم که هرچقدر تعداد رکورد های درگیر در تراکنش کمتر باشه لاک سطح پایینتری خواهیم داشت، هرچی ایندکس های بهتری داشته باشیم Blocking کمتری و در نهایت عملکرد مناسب تری در کل اپلیکیشن. منبع: