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

ReadCommitted

بیایید بدون آنکه تغییری در اسکریپت و طراحی پکیج مثال قبل ایجاد کنیم تنها نوع Isolation Level مورد استفاده آن را به ReadCommitted تغییر دهیم و تاثیر آن را در روال اجرای پکیج بررسی کنیم.
در حال حاضر پس از تغییر, ویژگی‌های کامپوننت Sequence Container بصورت زیر می‌باشد.حال اگر مجددا روال اجرا را همانند مثال قبل طی کنیم مشاهده می‌کنیم که با اجرا شدن پکیج, در مرحلۀ “Get List Of Orders From SalesOrder” بلاک خواهیم شدمی توان این موضوع را با استفاده از Extended-Event و استفاده از Eventای به نام Blocked_Process_Report ثابت کرد.حال اگر اسکریپت حذف رکورد 71950, Rollback شودبلاکنیگ برطرف شده و نتیجه به صورت صحیح , شامل رکورد 71950 , برگردانده خواهد شد

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) مانع از درج رکورد جدید با مقدارِ کلیدِ بزرگتر یا مساوی (2020-05-20) شده است.زمانی که با بستن Message Box تراکنش خاتمه یابد, دستور Insert نیز اجرا خواهد شد.

SnapShot

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

علت؟

نکته: متاسفانه مقدار 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 قابل قبول تنظیم گردد