ممنون بابت مطلب خوب تون، در ادامه صحبتهای شما خواستم چند مورد رو اضافه کنم
1- مشکل مطرح شده اصطلاحا Lost Update نام داره (که در مثال جاری باعث میشه یکی از بروزرسانیهای عمل خرید گم بشه!)
این مشکل توسط Isolation Level سطوح Repeatable Read و Serializable قابل حل هست.
جدول زیر لیست مشکلات همزمانی به ازای هر سطح از Isolation Level رو نشون میده.
2- استفاده از سطح Isolation Level بالاتر به معنی سخت گیری و احتیاط بیشتر هست و باعث افزایش میزان Blocking و متعاقبا احتمال وقوع Deadlock و نیز کاهش Performance و ظرفیت Concurrency (همزمانی) دیتابیس میشه (و بلعکس)
پس اگر مشکلی رو تونستین با Isolation Level سطح پایینتری مثل Repeatable Read حل کنید بهتره نسبت به اینکه Isolation Level سطح بالاتری مثل Serializable رو انتخاب کنین
در تصویر زیر نحوه حل اش با Isolation Level سطح Repeatable Read رو مشاهده میکنین
3- برخلاف روشهای دیگه، استفاده از Isolation Level سطح Repeatable Read و نیز Serializable در مثال جاری میتونه باعث وقوع Deadlock (بن بست) بشه و این بستگی به این داره که 2 تراکنش در چه نقطه ای به همزمانی میخورن
همونطور که در 2 تصویر زیر میبینین WAITFOR DELAY اولی باعث قوقع Deadlock میشه ولی دومی نمیشه
مثال وقوع Deadlock
توضیح:
اجازه بدید قبل از توضیح چرایی وقوع Deadlock مروری بر چیستی اون داشته باشیم
این مشکل زمانی پیش میاد که 2تا تراکنش مانع اجرای هم دیگه میشن و در بن بستی گیر میکنن که هیچ کدوم نمیتونن کارشون رو تموم کن. به عنوان مثال تراکنش اول قفل A رو به دست میگیره و منتظر آزاد شدن قفل B میشه در حالی که تراکنش دوم قفل B رو به دست گرفته و منتظر آزاد شدن قفل A میشه، در این حالت هر دو تراکنش منتظر اتمام کار یکدیگر هستند و در بن بستی گیر میکنن (Deadlock) که هیچ کدوم نمتونن کارشون رو تموم کنن
در این شرایط SQL Server به ناچار یکی از اونها (در واقع تراکنشی که Rollback اش هزینه کمتری داره) رو به عنوان Victim (قربانی) حساب میکنه و اون رو Rollback و سپس Kill میکنه تا حداقل دیگری بتونه به کارش ادامه بده
در Isolation Level سطح Serializable و Repeatable Read هر رکوردی که خونده (SELECT) بشه، از تغییر (UPDATE و DELETE) شدن همون رکورد توسط دیگر تراکنشها جلوگیری میشه مادامی که تراکنش اول کارش تموم بشه
پس ترکنش اول مقدار balance رو SELECT میکنه، در همین حال تراکنش دوم نیز مقدار balance رو SELECT میکنه
سپس تراکنش اول میخواد مقدار balance رو UPDATE کنه ولی Block (مسدود) میشه چرا کنه همین رکورد قبلا توسط تراکنش دوم قفل شده، پس منتظر (Wait) تراکنش دوم میشه
تراکنش دوم نیز میخواد مقدار balance رو UPDATE کنه و این هم Block (مسدود) میشه چرا کنه همین رکورد قبلا توسط تراکنش اول قفل شده، پس منتظر (Wait) تراکنش اول میشه و BOOM !! بن بست یا Deadlock رخ میده، چرا که هر دو تراکنش Block یکدیگه شدن و منتظر آزاد شدن قفل توسط دیگری هستند
مثال عدم وقوع Deadlock
توضیح:
در این حالت اما تراکنش اول عمل SELECT و UPDATE رو زودتر از تراکنش دوم انجام میده و عمل UPDATE اش توسط تراکنش دوم بلاک (Block) نمیشه چرا که تراکنش دوم هنوز شروع نشده
دقت داشته باشین که در این مثال از WAITFOR TIME استفاده نکردیم که بخواد دقیقا در یک زمان مشخص، هر دو تراکنش رو اجرا بکنه بلکه چون دستی داریم کوئریها رو اجرا میکنیم، همین تاخیر یک ثانیه ایی باعث میشه تراکنش اول کارش رو زودتر شروع کنه و فقط در میانه راه و بعد از عمل UPDATE به همزمانی بخورن
4- هینت
HOLDLOCK معادل Isolation Level سطح Serializable هست، برای استفاده از سطح Repeatable Read میتونیم از هینت
REPEATABLEREAD استفاده کنیم
صرفا جهت مرور:
عبارات Table Hints دستور هایی هستند که رفتار پیشفرض Query Optimizer (بهینه ساز کوئری) رو به هنگام دستورات DML (مثل SELECT/INSERT/UPDATE/DELETE) تغییر میده (override میکنن) و معمولا برای تغییر سطح قفل و Isolation Level و یا انتخاب Index دلخواه استفاده میشه
5- هینت
UPDLOCK دو تا کار انجام میده
1- باعث میشه به جای قفل Shared Lock یا (S) از قفل Update Lock یا (U) بر روی رکوردهای خوانده شده استفاده بشه
2- همانند سطح Repeatable Read و Serializable (هینت HOLDLOCK) قفل رو تا اتمام Trasanction (و نه صرفا Statement) نگه میداره (Hold میکنه)
پس در این مثال خاص (و نه همه جا، که دلیل اون رو جلوتر بررسی میکنیم) میتونیم بدون HOLDLOCK هم انجامش بدیم و نیازی به اون نخواهیم داشت.
هینت UPDLOCK معمولا زمانی استفاده میشه که میخوایم رکورد یا رکورد هایی رو در Statementهای بعدی تراکنش جاری UPDATE کنیم و نمیخوایم در این بین تراکنش همزمان دیگری این دیتا رو تغییر بده
6- دقت داشته باشین که این تصور که چون UPDLOCK و HOLDLOCK هر دو در نگه داشتن (Hold کردن) قفل تا انتهای تراکنش (و نه Statement جاری) مشترک هستند پس به هنگام استفاده از UPDLOCK دیگر نیازی به HOLDLOCK نداریم تصور اشتباهی هست و علت ظریفی داره
یا بهتره این سوال رو اینطور مطرح کنیم که:
پس چرا استفاده از HOLDLOCK در کنار UPDLOCK رایج هست؟ و چه فرقی میکنه که HOLDLOCK در کنار UPDLOCK استفاده بکنیم یا خیر؟
قبل از بررسی چرایی این موضوع بهتره مروری بر روی Repeatable Read و Serializable از منظر Lock Modeها (حالات قفل) داشته باشیم
سطح Repeatable Read
در این سطح قفل Shared صرفا به ازای رکوردهای SELECT شده ایجاد میشه ولی برخلاف Read Committed قفل رو تا اتمام Transaction نگه میداره داره (Hold میکنه) - (نه به محض اتمام Statement)
در نتیجه تا پایان تراکنش جاری از هر گونه تغییر بر روی دیتای Read شده توسط دیگر تراکنشهای همزمان جلوگیری میکنه
سطح Serializable
این سطح مشابه سطح Repeatable Read عمل میکنه (یعنی قفل رو تا اتمام تراکنش و نه صرفا Statement جاری نگه میداره) با این تفاوت که از قفل Key-Range Lock به جای Shared Lock استفاده میکنه (البته نه همیشه و استثنا هایی هم وجود داره که جلوتر بررسی میکنیم) و کل بازه (محدوده) رکوردهای SELECT شده بر اساس شرط WHERE رو قفل گذاری میکنه (بر خلاف Repeatable Read که صرفا به ازای رکوردهای SELECT شده قفل ایجاد میکرد)
و بدین صورت از مشکل Phantom Read (مانند INSERT شدن رکورد جدیدی در بازه/محدوده قفل شده) جلوگیری میشه
به عنوان مثال در Serializable شرط WHERE Age BETWEEN 18 AND 35 یک قفل Key-Range Lock بر روی بازه (محدوده) 18 تا 35 گذاشته میشه و تمامی اعداد داخل این بازه رو شامل میشه (حتی اگه هیچ رکوردی در این بازه نداشته باشیم) در صورتی که Repeatable Read چون صرفا به ازای رکوردهای SELECT شده قفل گذاری میشه، که اگه فرض کنیم هیچ رکوردی در این بازه نداریم، هیچ قفلی هم ایجاد نخواهد شد
بررسی نحوه عملکرد Serializable و استثناهای اون
در سطح Serializable بر اساس یکی از حالات زیر قفل ایجاد میشه
1- قفل Shared یا (S) روی کل Table
اگه جدول Index ایی نداشته باشه بر روی کل Table قفل Shared Lock یا (S) میگذاره (فرقی هم نمیکنه شرط WHERE داشته باشیم یا نه)
2- قفل Shared یا (S) روی رکورد (Row/Key)های Read شده
اگه شرط WHERE مون بر روی یک ستون Index باشه و مستقیما با مقدار مقایسه کنه (مثل عملگر = یا IN) روی رکورد (Row/Key)های Read شده قفل Shared Lock یا (S) میگذاره
3- قفل RangeS-S روی رکورد (Row/Key)های Read شده
اگه شرط WHERE مون بر روی یک ستون Index باشه و دستوراتی که بازه (محدوده) رو مقایسه میکنن (مثل عملگر < و > و... یا BETWEEN) روی سطح رکورد (Row/Key) قفل Key-Range Lock یا (RangeS-S) میگذاره
4- قفل RangeS-S روی تمام رکوردها (حتی Read نشده)
اگه شرط WHERE نداشته باشیم یا شرط WHERE مون بر روی یک ستون Index نباشه روی تمام رکوردها (Row/Key) حتی Read نشده، قفل Key-Range Lock یا (RangeS-S) میگذاره
حال بر گردیم به سوال اولمون
در نکته قبلی (شماره 5) دیدیم که در این مثال «خاص» میتونیم بدون استفاده از HOLDLOCK در کنار UPDLOCK کارمون رو انجام بدیم
اما چی چیزی این مسئله رو «خاص» کرده؟
چون شرط WHERE مون بر روی Index جدول (یعنی فیلد user_id) هست و با عملگر (=) که مستقیما مقایسه میکنه (و نه در یک بازه - محدوده)
پس صرفا بر روی رکوردهای Read شده (SELECT شده) قفل Shared Lock یا (S) گذاشته میشه دقیقا مانند کاری که Repeatable Read انجام میده (پس در این مورد خاص، سطح Repeatable Read و Serializable فرقی با هم ندارن)
همچنین گفتیم که هینت UPDLOCK هم عمل قفل گذاری رو صرفا بر روی رکوردهای Read شده ایجاد میکنه (پس در این مثال خاص کاملا مشترک و شبیه هستند) و به همین دلیل هست که میتونیم بدون نیاز به HOLDLOCK در کنار UPDLOCK به همون نتیجه برسیم
در تصویر زیر Lockهای ایجاد شده در 3 حالت رو مشاهده میکنین که هیچ تفاوتی با هم ندارن (از DMV سیستمی sys.dm_tran_locks کمک گرفتیم تا لیست Lockهای در حال اجرا رو مشاهده کنیم)
7- چه زمانی استفاده از HOLDLOCK در کنار UPDLOCK مفید هست؟
زمانی که به Range-Key Lock نیاز داریم و میخوایم بر روی بازه (محدوده) رکوردهای SELECT شده قفل بگذاریم (مانند Serializable) و نه صرفا خود رکوردهای SELECT شده (مانند Repeatable Read)
در واقع شرط WHERE مون بر روی Index و توسط عملگر مساوی هایی که مستقیما مقدار رو چک میکنین (مانند عملگر = یا IN) نیست
و چون این نکته ای ظریف و نیازمند دقت هست برنامه نویسان ترجیح میدن جهت محکم کاری بیشتر از HOLDLOCK در کنار UPDLOCK استفاده کنند و همین دلیل رایج بودنش هست