انواع isolation در SQL Server: بررسی کامل ایزوله‌سازی تراکنش‌ها

انواع isolation level، همانطور که اطلاع دارید از اصول اولیه هر RDBMS ای حفظ جامعیت داده ای (data integrity) و پایداری داده ها (durability) در هر شرایطی می باشد. SQL Server نیز به عنوان یکی از RDBMSهای معروف از این قاعده مستثنا نمی‌باشد و در این رابطه از مفهوم Transactionها کمک گرفته است. طبق ویژگی Isolation در تراکنش‌ها تمامی تغییرات انجام شده در هر تراکنش تا زمان Commit شدن, از دید سایر تراکنش‌ها مخفی و ایزوله می‌باشد و به تعبیری عامیانه فضای هر تراکنش را می‌توان همانند حریم شخصی آن در نظر گرفت.

انواع isolation level

بر خلاف یک دیتابیس روی لپ تاپ شخصیتان, متاسفانه و یا خوشبختانه در یک محیط عملیاتی واقعی شما تنها شخصی نخواهید بود که از یک دیتابیس استفاده خواهید کرد و همزمان با شما احتمالا چند صد و یا چند هزار کاربر در حال کار با آن دیتابیس می‌باشند و احیانا درخواست استفاده از منابع مشترکی را خواهند داشت. در چنین شرایطی Transaction Isolation تضمین خواهد کرد که نتیجۀ حاصل از اجرای همزمان این درخواست/تراکنش‌ها مشابه با اجرای سریالی آنها خواهد بود یعنی اجرای همزمان تراکنش‌ها تاثیر منفی بر روی یکدیگر نداشته و یا سبب نقض قانونی نخواهد شد. چنین نتیجه‌ای با تنظیم و کنترل Transaction Isolation Levelها بدست خواهد آمد.
Transaction Isolation Levelها تعیین خواهند کرد که سایر تراکنش‌ها تا چه میزان حق دسترسی به منابع در اختیار و یا تغییر یافته توسط تراکنش فعلی را خواهند داشت. پشتیبانی از چنین رفتاری با نحوه قفل گذاری و مدت زمان نگه داشتن قفل‌ها روی منابع بدست می‌آید.

هنگام طراحی پکیج‌ها در(SSIS) SQL Server Integration Service نیز می‌توان علاوه بر ایجاد و تعیین محدوده Transaction, نوع Transaction Isolation Level مورد استفادۀ آن تراکنش را نیز تعیین نمود. در ادامه با انواع Isolation Levelها و مثال‌هایی از آن در SSIS آشنا خواهیم شد.

پیش نیاز

پیش از شروع به توضیح انواع Isolation Level لازم است که با مفهوم قفل (Lock) و انواع آن آشنایی داشته باشید. هر چند این مقوله در این مجال نمی‌گنجد اما در اینجا چند نمونه از معروفترین قفل‌ها را به اختصار معرفی خواهیم کرد.

• قفل:

  SQL Server از مفهوم قفل برای پشتیبانی و پیاده سازی ویژگی Isolation تراکنش استفاده می‌کند. قفل ها می‌توانند روی منابع مختلف از جمله رکوردها, Pageها، Partitionها، جدول و دیتابیس بدست آمده و نگه داشته شوند. شما باید به خاطر داشته باشد که ریزدانگی (Granularity) قفل‌ها قطعا بر کاهش و یا افزایش میزان همزمانی (Concurrency) تاثیر گذار خواهد بود.

• Exclusive (X) Lock:

  این نوع قفل توسط هر دستوری که قصد تغییر دیتای یک منبع را داشته باشد یا به اصطلاح Writerها, بدست آورده می‌شود. این قفل ناسازگارترین نوع قفل می‌باشد بدین معنی که اگر تراکنشی بخواهد منبعی را بدست آورد که از قبل قفل X روی آن وجود داشته باشد, تا زمان اتمام آن تراکنش (Commit و یا Rollback شدن) بلاک خواهد شد و محکوم به انتظار خواهد بود. بدیهی است که در هر لحظه تنها یک تراکنش قادر به بدست آوردن این نوع قفل روی یک منبع خواهد بود.
  نکته مهمی که باید به خاطر داشته باشید این است که تعیین Transaction Isolation Level برای این نوع تراکنش ها هیچ تاثیری بر مدت زمان نگهداری این نوع قفل نخواهد داشت و قفل‌ها همواره تا پایان یافتن تراکنش روی منابع باقی خواهند ماند.

• Shared (S) Lock:

  این نوع قفل توسط دستورات Read روی منابع بدست آورده می‌شوند و دارای سازگاری با سایر قفل‌های هم نوع خود هستند. از این رو یک منبع یکسان می‌تواند مابین چندین Reader به اشتراک گذاشته شود.

• Update (U) Lock:

  این نوع قفل‌ها نیز توسط Writerها بدست آورده می‌شوند. SQL Server پیش از بروزرسانی یک منبع و بدست آوردن قفل (X) روی آن, از این نوع قفل جهت ارزیابی آنکه آیا این سطر نیاز به بروزرسانی خواهد داشت یا نه استفاده می‌کند. پس از آنکه قفل (U) روی یک سطر بدست آورده شود , SQL Server شرط کوئری را بررسی خواهد کرد. در صورتی که آن سطر نیاز به آپدیت شدن داشته باشد قفل U را به X تبدیل کرده و در غیر این صورت این قفل را آزاد خواهد کرد.
  سازگاری این نوع قفل با قفل (S) سبب می‌شود که از بلاک شدن و یا بلاک کردن غیر ضروری (پیش از موعد) Writerها و Readerها جلوگیری شود.

انواع Isolation Level

انواع Transaction Isolation Levelها را می‌توان به دو دستۀ کلی:

• Isolation Level‌های بدبینانه (Pessimistic Isolation Level)
• Isolation Levelهای خوشبینانه (Optimistic Isolation Level) طبقه بندی نمود.

هر یک از این روش‌ها معایب و مزایای خاص خود را دارند و هر کدام بخشی از مشکلات همزمانی را مرتفع خواهند کرد.
مجددا تاکید می‌شود که تعیین Isolation Level تاثیری بر قفل‌های بدست آمده توسط دستوراتData Modification نخواهد داشت. یک تراکنش هنگام تغییر اطلاعات صرف نظر از Transaction Isolation Level تعیین شده برای آن, همیشه قفل exclusive (X) بدست آورده را تا پایان تراکنش نگه خواهد داشت.

• Pessimistic Isolation Level

در روش‌های بدبینانه فرض بر این خواهد بود که به احتمال زیاد چندین تراکنشِ همزمان با قصد تغییر به یک منبع مشترک دسترسی خواهند یافت. اساس کار این گونه روش‌ها بر قفل گذاری و بلاک کردن سایر تراکنش‌های همزمانی است که درخواست قفل ناسازگار دارند.

• Read Uncommitted
  زمانی که از این Isolation Level استفاده می‌کنید هیچگونه قفلی (Shared Lock) به ازای منابع مورد نیاز خود ایجاد نخواهید کرد. در این صورت تراکنش فعلی قادر به وارد شدن به حریم (Isolation) سایر تراکنش‌ها و خواندن دیتای Commit نشده آنها خواهد بود زیرا بدون استفاده از قفل سایر تراکنش‌ها از وجود این تراکنش مطلع نخواهند بود و در نتیجه Blockingای نیز به دلیل وجود قفل‌های ناسازگار رخ نخواهد داد. با استفاده از این Isolation Level شما خواهید پذیرفت که از بروز تمامی مشکلات همزمانی از جمله Dirty Readها آگاهی دارید.
• Read Committed
  هنگام استفاده از این نوع Isolation Level روی منابع مورد نیاز Shared Lock ایجاد خواهد شد. در نتیجه تراکنش‌هایی با این نوع Isolation Level می‌توانند موجب Block شدن سایر تراکنش‌ها با قفل‌های ناسازگار شوند و یا خود توسط اینگونه تراکنش‌ها Block شوند. نکته مهم در مورد این نوع Isolation Level این است که قفل یا قفل‌های بدست آمده روی منابع در هر سطح از ریزدانگی (granularity) , پس از اتمام عملیات خواندن آزاد خواهند شد. به طور مثال یک Row Lock پس از پردازش و خوانده شدن آن سطر و یا یک Page Lock پس از پردازش آن page آزاد خواهند شد.
  واضح است که در این Isolation Level مشکل Dirty Read روش قبل مرتفع شده است اما همچنان مشکلات دیگری وجود دارد که در ادامه به معرفی آنها خواهیم پرداخت.
• Repeatable Read
  در این نوع Isolation Level نیز همانند روش Read Committed از Shared Lock روی منابع در اختیار استفاده می‌شود اما بر خلاف روش قبل قفل‌های بدست آمده تا لحظه پایان تراکنش باقی خواهند ماند. هر چند که این کار سبب بلاک کردن طولانی مدت سایر تراکنش‌های ناسازگار خواهد شد اما اکثریت مشکلات همزمانی را مرتفع خواهد کرد.
• Serializable
  در این روش که سختگیرانه ترین نوع Isolation Level می‌باشد, نه تنها Shared Lockهای بدست آمده روی منابع تا پایان تراکنش باقی خواهند ماند بلکه استفادۀ SQL Server از نوعی قفل به نام Range Lock سبب خواهد شد که به جای سطرهای تکی, محدوده و رنجی از کلید ایندکس محافظت شود. در این شرایط اگر تراکنش دیگری بخواهد سطر جدیدی را که مقدار کلید آن در رنج مذکور(محافظت شده) قرار دارد درج نماید, بلاک خواهد شد. در این نوع Isolation Level تمامی مشکلات همزمانی مرتفع شده است. هر چند که میزان همزمانی (Concurrency) در پایین ترین حد ممکن قرار دارد.
  لازم به ذکر است که Isolation Level پیش فرضِ تراکنش‌ها در SSIS بر خلاف SQL Server, Serializable می‌باشد.

• رایجترین مشکلات همزمانی در Isolation Levelهای مختلف

• Dirty Read: این مشکل زمانی رخ خواهد داد که یک تراکنش به دیتای Commit نشدۀ تراکنش دیگری دسترسی پیدا کرده و آن را بخواند.
• Non-Repeatable Read: این مشکل زمانی بروز خواهد کرد که تلاش‌های متوالی برای خواندن دیتایی یکسان در داخل یک تراکنش منجر به نتایج مختلف شود. علت این مشکل را می‌توان آزاد کردن قفل‌های بدست آمده توسط دستورات داخل یک تراکنش پیش از اتمام آن تراکنش دانست.
• Phantom Read: این مشکل زمانی رخ خواهد داد که تلاش‌های متوالی برای خواندن دیتایی یکسان داخل یک تراکنش منجر به بازگرداندن رکوردهای جدیدی شود که تراکنش در خواندنِ قبلی آنها را نخوانده بود.

• Optimistic Isolation Level

روش های خوشبینانه با هدف رفع مشکلات بلاکینگ و همچنین برطرف کردن مشکلات Data Consistency ارائه شده‌اند. در این روش‌ها فرض بر این است که به احتمال کم، یک منبع مشترک توسط تراکنش‌های همزمان تغییر خواهد کرد. مبنای کار این نوع روش‌ها بر Row Versioning در کنار قفل گذاری است.

• Read Committed Snapshot
  در حقیقت این نوع Isolation Level یک تنظیم در سطح دیتابیس است و نمی‌توان به نوعی آن را یک Isolation Level مستقل در نظر گرفت, در واقع این روش همان Read Committed Isolation Level است که تنها نحوه رفتار تراکنش‌های Reader را تغییر داده و هیچ تاثیری بر رفتار ها نخواهد داشت. زمانی که این تنظیم روی دیتابیسی فعال شود هر تغییرِ دیتا سبب خواهد شد که نسخه قبل از تغییر در فضایی از دیتابیس TempDB به نام Version Store نگهداری شود. حال اگر یک دستور Read هنگام دسترسی به دیتا با نسخۀ Commit نشده‌ای از تراکنش دیگری مواجه شود به جای بلاک شدن از آخرین نسخۀ Commit شدۀ آن دیتا قبل از شروع آن دستور (Statement) استفاده می‌کند (داده‌هایی که پس از شروع دستور (Statement) Commit شوند توسط این دستور قابل دسترس نیستند).
  هر چند که با این روش مشکل بلاکینگ readerها و writerها مرتفع شده است اما Writerها همچنان بدلیل استفاده از قفل‌های U و X یکدیگر را بلاک خواهند کرد.
• Snapshot
  پیش از استفاده از این نوع Isolation Level لازم است که این قابلیت را روی دیتابیس مورد نظرتان فعال کنید. یکی از تفاوت‌های عمدۀ این روش با روش‌های قبلی در این است که این روش بدون بلاکینگ یک Data Consistency در سطح Transaction ایجاد می‌کند(Transaction-Level Data Consistency) و دستورات داخل یک تراکنش تنها به آخرین نسخۀ Commit شده از اطلاعات قبل از شروع تراکنش دسترسی دارند. عملکرد writerها در این Isolation Level متفاوت خواهد بود. زمانی که یک Writer با سطر تغییر یافته ای مواجه شود جهت بررسی شرطِ بروزرسانی , بدون بلاک شدن بدلیل ناسازگاری قفل U و X , به نسخۀ آن سطر در Version Store مراجعه می‌کند. تنها زمانی که نیاز به بروزرسانی داشته باشد اقدام به قرار دادن قفل X روی آن سطر خواهد کرد که در این صورت این اقدام به یکی از سه وضعیتِ موفق به بروزرسانی و یا بلاک شدن توسط قفل X دیگر و یا دریافت خطا بدلیل مواجه شدن با نسخه جدیدی از آن سطر, ختم خواهد شد.

مثال
حال که تقریبا همه موارد را بصورت تئوری بررسی کردیم بیاید با در نظر گرفتن چند سناریوی ساده حالات مختلف Isolation Level رو با ذکر چند مثال بررسی کنیم و چند نکته کاربردی و مهم رو یاد بگیریم.
برای شروع یک جدول ساده به نام Sales Order ایجاد میکنیم. برای پر کردن دیتای این جدول می‌توانید از دیتابیس AdventureWorks کمک بگیرید.

USE SSIS_IsolationLevels
GO
CREATE TABLE dbo.SalesOrder
(
  SalesOrderID int IDENTITY(1,1) NOT NULL,
  OrderDate datetime NOT NULL,
  [Status] tinyint NOT NULL,
  CustomerID int NOT NULL,
  SalesPersonID int NULL,
  TaxAmt money NOT NULL,
  Freight money NOT NULL,
  TotalDue money NOT NULL
)
CREATE UNIQUE CLUSTERED	INDEX Ix_SalesOrderID ON dbo.SalesOrder
(SalesOrderID)
CREATE	NONCLUSTERED	INDEX	Ix_OrderDate	ON	dbo.SalesOrder
(
  OrderDate
)

سپس برای آنکه Isolation Levelهای مختلف را بررسی کنیم در هر مرحله از یک پکیج جهت بررسی تاثیر Isolation Level در SSIS و همچنین از یک اسکریپت جهت ایجاد یک تراکنش همزمان استفاده می‌کنیم

Read UnCommitted

• اسکریپت

  فرض کنید که یک مشتری قصد کنسل کردن سفارش خرید خود را دارد و به همین دلیل کدی مشابه زیر توسط یک تراکنش می‌بایست اجرا شود اما بدون دستور Rollback

BEGIN	TRAN
  DELETE	dbo.SalesOrder
    WHERE	SalesOrderID = 71950
-- Rollback

• پکیج

  همزمان نیز پکیجی در حال اجرا است که در مرحله‌ای از آن لیست سفارشات یک روز خاص که شامل سفارش مشتری مذکور در مرحله قبل نیز می‌باشد, ایجاد می‌شود. حال بیاید بررسی کنیم اگر این لیست داخل یک تراکنش به صورت ReadUncommitted ایجاد شود چه اتفاقی خواهد افتاد. جهت ساده سازی پکیج مذکور, تنها مرحله دریافت اطلاعات آن نشان داده شده است.

• طراحی پکیج:

  ۱. ابتدا یک Sequence Container ایجاد کرده و نام آن را به “Sequence Container_Read Uncommitted” تغییر می‌دهیم.

سپس جهت ایجاد تراکنش, ویژگی TransactionOption این Container را به مقدار Required تغییر داده و ویژگی Isolation Level آن را نیز مطابق با تصویر زیر بر روی ReadUnCommitted تنظیم می کنیم۲. حال از یک Execute SQL Task جهت بدست آوردن لیست سفارشاتِ روز مورد نظر استفاده کرده و نام آن را به “Get List Of Orders From SalesOrder” تغییر می دهیم و تنظیمات مربوط به ویژگی های TransactionOption و IsolationLevel آن را با همان مقادیر پیش فرض باقی می‌گذاریم.سوال: با توجه به اینکه مقدار ویژگی‌های Isolation Level تعیین شده برای Sequence Container و Execute SQL Task متفاوت می‌باشد, کدام یک اعمال خواهد شد؟
نکته: مقدار ویژگی Isolation Levelِ تنظیم شده برای یک Container (شامل Package , Sequence Container, Taskها و…) تنها زمانی اعمال می‌شود که مقدار ویژگی TransactionOption آن Required باشد. علاوه بر این خود آن کانتینر باید ایجاد کنندۀ تراکنش باشد و والد آن نباید از قبل تراکنشی را ایجاد کرده باشد.
نکته:در خصوص کانتینرهای فرزند (Child Container), در صورتی که ویژگی TransactionOption آنها برابر با مقدار Supported باشد به تراکنش کانتینر والد (Parent Container) ملحق شده و مقدار ویژگی Isolation Level آنها نادیده گرفته خواهد شد.
سپس از کوئری زیر برای بدست آوردن لیست سفارشات استفاده می‌شود

SELECT
    SalesOrderID
    ,OrderDate
    ,TaxAmt
,TotalDue
  FROM	dbo.SalesOrder
           WHERE  OrderDate = '2020-05-20'

حال برای آنکه بتوانیم تاثیر Isolation Level انتخاب شده را مشاهده کنیم از یک متغیر از نوع Object به نام ObjResult برای ذخیره کردن رکوردهای برگشتی استفاده می‌کنیم۳. در مرحله آخر از یک Script Task برای نمایش نتایج مرحله قبل استفاده می‌کنیم و متغیر ObjResult را به عنوان ورودی به آن پاس می‌دهیم.کد استفاده شده جهت نمایش رکوردها نیز در ادامه آورده شده است

OleDbDataAdapter A = new OleDbDataAdapter();
System.Data.DataTable dt = new System.Data.DataTable();
A.Fill(dt, Dts.Variables["User::ObjResult"].Value);
string message = "";
foreach (DataRow row in dt.Rows)
{
object[] array = row.ItemArray;
message += "SalesOrderID : " + array[0].ToString() + " ; " +
"OrderDate : " + array[1].ToString() + " ; " +
"TaxAmt : " + array[2].ToString() + " ; " +
"TotalDue : " + array[3].ToString() + "rn";
}
MessageBox.Show(message);

• اجرا
  ۱. پیش از شروع به اجرا ابتدا کوئری زیر را اجرا کرده و مشاهده می‌کنیم که در خروجی سه رکورد بازگردانده می‌شود

۲. در مرحله بعد اسکریپت ذکر شده در قسمت “اسکریپت” را بدون Commit و یا Rollback کردن اجرا می نماییم.شما می توانید قفل های بدست آمده و آزاد شده توسط این session را توسط Extended-Event و eventهای lock_acquired و lock_released مشاهده کنید۳. حال پکیجی که در مرحله قبل ایجاد کردیم را اجرا می‌کنیم.مشاهده می‌کنید بدون آنکه پکیج مورد نظر بلاک شود نتیجه برگردانده می‌شود. این در حالی است که رکورد ۷۱۹۵۰ نمایش داده نشده است.
حال اگر به هر دلیلی دستور حذف رکورد ۷۱۹۵۰ با مشکل مواجه شود و تراکنش Rollback شود, این رکورد در نتیجۀ Select بازگردانده نشده است.

انواع isolation level، بیایید بدون آنکه تغییری در اسکریپت و طراحی پکیج مثال قبل ایجاد کنیم تنها نوع Isolation Level مورد استفاده آن را به ReadCommitted تغییر دهیم و تاثیر آن را در روال اجرای پکیج بررسی کنیم. در حال حاضر پس از تغییر, ویژگی‌های کامپوننت Sequence Container بصورت زیر می‌باشد.

انواع isolation level

حال اگر مجددا روال اجرا را همانند مثال قبل طی کنیم مشاهده می‌کنیم که با اجرا شدن پکیج, در مرحلۀ “Get List Of Orders From SalesOrder” بلاک خواهیم شدمی توان این موضوع را با استفاده از Extended-Event و استفاده از Eventای به نام Blocked_Process_Report ثابت کرد.حال اگر اسکریپت حذف رکورد ۷۱۹۵۰, Rollback شودبلاکنیگ برطرف شده و نتیجه به صورت صحیح , شامل رکورد ۷۱۹۵۰ , برگردانده خواهد شد

مفهوم RepeatableRead

بیایید این بار با تغییری کوچک در اسکریپت و پکیج و همچنین ترتیب اجرای آنها, Isolation Level از نوع RepeatableRead را بررسی کنیم. فرض کنید که شما در حال تهیه لیست سفارشات یک روز خاص با استفاده از پکیج هستید. در حین اجرای پکیج شما دستوری مبنی بر کاهش میزان مالیات یکی از سفارشاتی که اتفاقا در لیست گزارش شما نیز وجود دارد صادر می شود. اگر تراکنش مورد استفاده در پکیج از نوع RepeatableRead باشد چه اتفاقی خواهد افتاد؟

پکیج

تنها تغییر مورد نیاز این است که در پکیج مثال قبل مقدار ویژگی Isolation Level کامپوننت Sequence Container را به “RepeatableRead” تغییر دهید.

اسکریپت

کوئری نیز مشابه با اسکریپت زیر جهت بروزرسانی مقدار مالیاتِ سفارش مورد نظر وجود دارد.

UPDATE	dbo.SalesOrder
	SET	TaxAmt = TaxAmt - 10
WHERE  SalesOrderID = 71950

اجرا

۱- این بار , ابتدا پکیج را اجرا می‌نماییم. اجازه دهید که پیغام نمایش داده شده روی صفحه باقی بماند, این کار باعث می‌شود که تراکنش همچنان باز مانده و شما فرصت خواهید داشت تا اسکریپت را اجرا کنید.

۲- حالا اسکریپت مربوط به بروزرسانی را اجرا می‌کنیم. با اجرا شدن کوئری مشاهده می‌کنید که کوئری مربوطه توسط پکیج بلاک خواهد شد.می‌توان این موضوع را با استفاده از Extended Event و Event مربوطه (Blocked_Process_Event) اثبات کرد. برای جزئیات بیشتر می توانید گراف مربوط به بلاکینگ را مشاهده نمایید.زمانی که تراکنش پایان یابد (در این مثال با OK کردن Message Box) , اسکریپت مذکور از حالت بلاک خارج شده و اجرا خواهد شد.

مفهوم Serializable

این بار می‌خواهیم با تغییر کوچکی در مثال قبل (RepeatableRead) تاثیر Isolation Level از نوع Serializable را بر تراکنش‌های همزمان نشان دهیم.
تصور کنید که شما در حال تهیه لیست سفارشات یک روز خاص با استفاده از پکیج هستید. در حین اجرای پکیجِ شما, کاربری به صورت همزمان قصد دارد سفارش جدیدی از یک مشتری برای همان روز خاص ثبت نماید. حال اگر شما برای تهیه لیست سفارشات از Serializable Isolation Level استفاده کرده باشید چه اتفاقی خواهد افتاد؟

پکیج

تنها تغییر مورد نیاز این است که در پکیج مثال قبل مقدار ویژگی Isolation Level کامپوننت Sequence Container را به “Serializable” تغییر دهید.

اسکریپت

کوئری نیز مشابه با اسکریپت زیر جهت ثبت سفارشی جدید وجود دارد. توجه داشته باشید که تاریخ سفارش جدید با تاریخ سفارشات ذکر شده در پکیج یکسان باشد.

INSERT	INTO	dbo.SalesOrder
	)	OrderDate
		,Status
		,CustomerID
		,SalesPersonID
		,TaxAmt
		,Freight
		,TotalDue
	(
	VALUES ('2020-05-20',5,29551,279,1000,100,1100)

اجرا

۱- این بار نیز ابتدا پکیج را اجرا کرده و جهت باز ماندن تراکنش اجازه خواهیم داد تا پیغام نمایش داده شده , روی صفحه باقی بماندمی‌توانید قفل‌های بدست آمده توسط پکیج را با استفاده از DMV به نام sys.dm_tran_locks مشاهده نمایید

SELECT
		DB_NAME(L.resource_database_id) databaseName
		,resource_type
		,L.resource_description
		,L.request_mode
		,resource_associated_entity_id
		,L.request_type
		,L.request_status
	FROM	sys.dm_tran_locks	L
       WHERE	resource_database_id = DB_ID()

با استفاده از اسکریپت زیر می‌توان نشان داد که تمامی Range Lock های ایجاد شده , روی ایندکس NonClustered بدست آمده‌اند.
سطر هایلایت شده را در نظر داشته باشید.

SELECT	object_name(P.object_id) ObjectName
		,partition_id
		,P.index_id
		,I.name
		,I.type_desc
	FROM	sys.partitions  P
			inner join sys.indexes I
			on	I.object_id = P.object_id
				and I.index_id = P.index_id
    WHERE	P.object_id = OBJECT_ID (<ObjectName>)
              And hobt_id in (<resource_associated_entity_id>)

۲- در این مرحله اسکریپت مربوط به ثبت سفارش را اجرا می‌کنیم. با اجرا شدن اسکریپت مشاهده خواهید کرد که این Session توسط پکیج بلاک خواهد شداین موضوع توسط Extended Event قابل اثبات است با مشاهده گزارش بلاکینگ متوجه خواهید شد که وجود Range Lock (Key = ffffffffffff) مانع از درج رکورد جدید با مقدارِ کلیدِ بزرگتر یا مساوی (۲۰۲۰-۰۵-۲۰) شده است.زمانی که با بستن Message Box تراکنش خاتمه یابد, دستور Insert نیز اجرا خواهد شد.

بررسی SnapShot

حالا نوبت بررسی Isolation Level خوشبینانۀ Snapshot است. فرض کنید که شما این یژگی را روی دیتابیس فعال کرده‌اید و پکیجی همانند مثال‌های قبل طراحی کرده و قصد دارید ویژگی Isolation Level آن را به Snapshot تنظیم کنید. به محض آنکه مقدار ویژگی Isolation Level را به Snapshot تغییر دهید با پیغام خطای زیر مواجه خواهید شد (نسخه SSDT مورد استفاده ۲۰۱۹ می‌باشد)

نکته: متاسفانه مقدار Snapshot از ویژگی Isolation Level با تراکنش‌های پکیج ناسازگار است. بنابراین شما نمی‌توانید Isolation Level تراکنش‌های پکیج را به این مقدار تنظیم کنید. راه حل جایگزین استفاده از کد TSQL, به طور مثال در Execute SQL Task و OLEDB Sourceها جهت تنظیم Isolation Level می‌باشد, همانند کد زیر.

SET	TRANSACTION	iSOLATION	lEVEL	SNAPSHOT

 Chaos

در این نوع Isolation Level تغییرات به محض اجرای دستورات, در دیتابیس Commit می‌شوند و دیگر قابل Rollback شدن نیستند. از ویژگی‌های آن می‌توان به نقض ویژگی‌های Serializable و Repeatable Read اشاره کرد. این نوع Isolation توسط SQL Server پشتیبانی نمی‌شود. به عنوان نکته آخر می‌توان اضافه کرد که نوع Isolation Level باید بسته به نیاز ما و با حداقل میزان Data Consistency قابل قبول تنظیم گردد.

سخن پایانی

در این مقاله با Isolation Level و انواع آن آشنا شدیم به معرفی Isolation Levelهای مثبت و منفی پرداختیم و با مثال به صورت عملی شرح دادیم که هر کدام به چند دسته تقسیم می‌شوند در مقاله بعدی به موارد باقی مانده از Isolation Levelهای مثبت با ذکر مثال خواهیم پرداخت. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.