انواع Isolation Level در SSIS [بخش اول]

مقدمه

همانطور که اطلاع دارید از اصول اولیه هر RDBMS ای حفظ جامعیت داده ای (data integrity) و پایداری داده ها (durability) در هر شرایطی می باشد. SQL Server نیز به عنوان یکی از RDBMSهای معروف از این قاعده مستثنا نمی‌باشد و در این رابطه از مفهوم Transactionها کمک گرفته است. طبق ویژگی Isolation در تراکنش‌ها تمامی تغییرات انجام شده در هر تراکنش تا زمان Commit شدن, از دید سایر تراکنش‌ها مخفی و ایزوله می‌باشد و به تعبیری عامیانه فضای هر تراکنش را می‌توان همانند حریم شخصی آن در نظر گرفت.
بر خلاف یک دیتابیس روی لپ تاپ شخصیتان, متاسفانه و یا خوشبختانه در یک محیط عملیاتی واقعی شما تنها شخصی نخواهید بود که از یک دیتابیس استفاده خواهید کرد و همزمان با شما احتمالا چند صد و یا چند هزار کاربر در حال کار با آن دیتابیس می‌باشند و احیانا درخواست استفاده از منابع مشترکی را خواهند داشت. در چنین شرایطی 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() + "\r\n";
}
MessageBox.Show(message);

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

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