اجازه بدین تو شروع بحث یه سوال مطرح کنم، آیا لاک شدن Object ها تو SQL Server اتفاق بدیه؟
نه، اینطور نیست. locking ابزار engine SQL برای حفظ خاصیت integrity دیتا در دیتابیس هست، تا تراکنش رو وادار کنه هر چهار خاصیت ACID رو پاس کنه.در مورد این چهار خاصیت و جزئیات مربوط به اون توی مطلب مشکل Concurrency - تئوری و راه حل در SQL Server صحبت کردیم.
درواقع SQL Server، برای جلوگیری از مشکلات Concurrency نیاز به لاک کردن در سطوح مختلف دیتا داره. لاک ها ساختار in-memory دارن یعنی تو سطح رم اتفاق میوفتن و نه دیسک.
لاک میتونه تو سطوح مختلف اتفاق بیوفته :
لاک انواع مختلفی داره :
لاک شدن Object ها در SQL Server تو سرور Develop به عنوان مشکل بروز نمیکنه چون ما به تنهایی یا به همراه چند برنامه نویس دیگه داریم رو اپلیکیشن کار میکنیم اما به محض انتقال پروژه به سرور production، ممکنه بروز کنه. این همونجایی که تلفن شما زنگ میخوره و بهتون میگن که فلان صفحه لود نمیشه یا خیلی کند لود میشه.
فرض کنین کاربر A تراکنشی رو شروع کرده و داره با بخشی از دیتا کار میکنه. کاربر B همزمان تراکنش دیگه ای رو شروع میکنه و میاد سراغ همون بخش دیتا که کاربر A هنوز باهاش کار داره. اینجا SQL سرور بنا به تنظیمات انجام شده به کاربر B میگه باید منتظر بمونی تا کاربر A کارش تموم بشه.
این میشه کل مفاهیمی که میخوایم در موردش صحبت کنیم. بیایین تعریف بالا رو به زبان SQL Server بگیم.
فرض کنین کاربر A تراکنشی رو شروع کرده ______ begin transaction
داره با بخشی از دیتا کار میکنه ______ یه لاک تو سطح Object اتفاق افتاده. مثلا سطح رکورد یا جدول
کاربر B تراکنش همزمان دیگه ای رو شروع میکنه ______ begin transaction
میاد سراغ همون بخش دیتا که کاربر A هنوز باهاش کار داره ______ Concurrency
اینجا SQL سرور بنا به تنظیمات انجام شده ______ Isolation Level
به کاربر B میگه باید منتظر بمونی ______ wait از نوع LCK که مربوط به blocking هست.
تا کاربر A کارش تموم بشه ________ Commit یا Rollback
پس فهمیدم که locking وقتی پیش میاد که کاربری بخشی از دیتا رو به طور اختصاصی در اختیار بگیره و کاربر دیگه ای رو که میخواد با اون دیتا کار کنه Block کنه. اما Deadlock کی اتفاق می افته؟
فرض کنین کاربر A بخش از دیتا رو لاک کرده، کابر B هم همزمان بخش دیگه ای از دیتا رو لاک کرده. کاربر A تو ادامه تراکنش نیاز به بخش دیتا داره که کاربر B لاکش کرده. کابر B هم تو ادامه کارش به بخش دیتایی نیاز داره که کابر A اول لاکش کرده. هیچ کدوم از تراکنش ها نمیتونه تموم بشه چون هر کدوم wait لاکی هست که توسط اون یکی ایجاد شده. به این حالت Deadlock گفته میشه. در این صورت SQL Server تفنگشو بر میداره و میگیره سمت تراکنشی که هزینه Rollback کمتری داره و اصطلاحا اون رو Kill میکنه.
تنها جایی که SQL Server دخالت میکنه و پروسسی رو Kill میکنه توی Deadlock هست و برای wait که بخاطر لاک ایجاد شده هیچ دخالتی نمیکنه. به طور پیش فرض مفهموم lock time out روی SQL Server وجود نداره.
برای اپلیکیشنی که مراحل اولیه رو طی کرده و کارایی لازم رو داره کمتر پیش میاد مشکل Deadlock وجود داشته باشه، درضمن چون deadlock همراه با نتیجه fail هست پس این وظیفه برنامه نویس های که این موارد رو تو کد هندل کنن و retry کنن.
دو راه حل وجود داره :
اول : داشتن ایندکس مناسب.
دوم: استفاده از Isolation Level مناسب در دیتابیس.
تو این مطلب میخوایم مثالی برای راه حل اول بزنیم و راهکار ممکن رو بررسی کنیم. از محیط SQL Server 2019 استفاده میکنیم به همراه دیتابیس دمو Stack Overflow . تمام تستی هم که میخواهیم انجام بدیم مربوط به جدول users این دیتابیس هست.
اول مطمئن میشیم که جدول users فقط PK داشته باشه و هیچ ایندکسی جز کلاستر براش ساخته نشده باشه.
چک میکنیم که Isolation level دیتابیس به حالت پیش فرض یعنی read commited باشه.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
کوئری اجرا میکنیم تا ببینیم چنتا از کاربرا LastAccessDate مربوط به بازه مورد نظر دارن.
select id from dbo.users where LastAccessDate >= '2013/01/10' and LastAccessDate < '2013/05/12'
1583 کاربر با این شرط پیدا شد. حالا یه تراکنش را به شکل زیر اجرا میکنیم.
begin tran update Users set Reputation = Reputation + 100 where LastAccessDate >= '2013/01/10' and LastAccessDate < '2013/05/12'
یه کوئری آپدیت روی جدول Users نوشتیم که reputation رو برای کاربرانی که LastAccessDate بین بازه مشخص شده رو دارن، 100 تا زیاد کنه. دقت کنین که تراکنش رو Commit نکردیم یعنی تراکنش باز میمونه.
یه تب جدید باز میکنیم و کوئری select رو دوباره اجرا میکنیم.
select id from dbo.users where LastAccessDate >= '2013/01/10' and LastAccessDate < '2013/05/12'
اتفاقی که میوفته اینکه کوئری درحال اجرا باقی می مونه و هیچ وقت نتیجه ای بما برگردونده نمیشه، اما چرا ؟
بهتره sp_whoisactive رو دانلود کنید و روی دیتابیستون ایجادش کنین تا بتونیم ادامه مراحل رو باهم پیش ببریم.
با اجرا کوئری زیر میتونیم بفهمیم چه کوئری هایی درحال اجرا هستن و چه lock رو ایجاد کردن.
sp_whoisactive
نتیجه این شکلی میشه:
همونطور که میبینیم کوئری select توسط کوئری Update که تراکنش commit نشده هست wait LCK شده.
حالا کوئری آپدیت رو Rollback میکنیم و یه ایندکس روی ستون های ID و LastAccessDate تو جدول Users ایجاد میکنیم.
CREATE NONCLUSTERED INDEX IX_LastAccessDate_ID ON [dbo].[Users] ( [Id] ASC, [LastAccessDate] ASC )
حالا مجددا کوئری آپدیت رو اجرا میکنیم.
begin tran update Users set Reputation = Reputation + 100 where LastAccessDate >= '2013/01/10' and LastAccessDate < '2013/05/12'
و بعد کوئری Select رو اجرا میکنیم.
select id from dbo.users where LastAccessDate >= '2013/01/10' and LastAccessDate < '2013/05/12'
اینبار خیلی سریع کوئری select جواب داد، در صورتی که کوئری Update همچنان داخل تراکنش باز هست، مجددا sp_whoisactive رو اجرا میکنیم.
اینبار فقط یه رکورد برمیگردونه که مربوط به کوئری Updateهست، یعنی با اینکه کوئری update لاک رو ایجاد کرده اما کوئری Select تونست اجرابشه. ایندکس ها فقط سرعت اجرا کوئری رو افزایش نمیدن به حل مسئله Concurrency هم کمک میکنن. چرا که با ساخت ایندکس Nonclustered ما یه کپی کوچکتری از جدولمون ایجاد کردیم که فقط شامل دو ستون مورد نیازمون هست. شبیه به اینکه یک جدول اصلی داشته باشیم و یه جدول خلاصه شده. وقتی کوئری رو جدول اصلی اجرا شد و اونو لاک کرد، SQL Server تشخیص داد که کپی دیگه ای هم وجود داره که میتونه از اون استفاده کنه و با دونستن این مطلب که کوئری اول تاثیری تو نتیجه کوئری دوم نداره اجازه استفاده از ایندکس Nonclustered رو بهش داد.
حالا یه تغییر کوچیک تو کوئری Select میدیم.
select id ,DisplayName from dbo.users where LastAccessDate >= '2013/01/10' and LastAccessDate < '2013/05/12'
آیا همچنان کوئری اجرا میشه یا block میشه؟
ما ستون DispayName رو هم به کوئری اضافه کردیم. میدونیم که این ستون تو ایندکس ساخته شده مون نبود. یعنی کپی از جدول که این دیتا رو داشته باشه وجود ندارد، پس ناچاره بره سراغ دیتا اصلی و این اتفاق میوفته.
کوئری select جدیدمون توسط کوئری update که تراکنش جاری هست Block شد.
همیشه ایندکس ها رو برای سریعتر کردن اجرا کوئری ها ساختیم. اینجا دیدیم که ایندکس های Nonclustered بخاطر تعریفی که دارن و کپی مستقلی از جدول رو جای دیگه و تو Page های جداگانه نگه میدارن میتونن برای حل مشکل Concurrencyو جلو گیری از Blocking مفید باشن.
توصیه ای که وجود داره ساخت تعداد محدودی ایندکس برای هر جدول هست مثلا حداکثر 5 ایندکس که شامل حداکثر 5 فیلد هستن میتونه شروع خوبی باشه. اینکه SQL Server کی تصمیم میگیره لاک رو تو سطح رکورد نگه داره یا کل جدول رو لاک کنه از دست ما خارج هست و نمیتونیم کنترلش کنیم اما میدونیم که هرچقدر تعداد رکورد های درگیر در تراکنش کمتر باشه لاک سطح پایینتری خواهیم داشت، هرچی ایندکس های بهتری داشته باشیم Blocking کمتری و در نهایت عملکرد مناسب تری در کل اپلیکیشن.
منبع :
