خانه SQL Server انواع Isolation Level در SSIS [بخش اول] SQL Server نوشته شده توسط: زهرا فرهنگی تاریخ انتشار: ۱۱ مرداد ۱۳۹۹ آخرین بروزرسانی: ۲۲ شهریور ۱۴۰۲ زمان مطالعه: 20 دقیقه ۴.۸ (۴) مقدمه همانطور که اطلاع دارید از اصول اولیه هر 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های مثبت با ذکر مثال خواهیم پرداخت. چه رتبه ای میدهید؟ میانگین ۴.۸ / ۵. از مجموع ۴ اولین نفر باش دانلود مقاله انواع Isolation Level در SSIS [بخش اول] فرمت PDF 16 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده مقالات 51 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده زهرا فرهنگی کارشناس پایگاه داده، در حال کسب تجربه در زمینههای تحلیل انباره داده، BI، بهینه سازی پایگاههای داده معرفی محصول مسعود طاهری آموزش ETL در هوش تجاری 3.590.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ محمد کمائی ۱۱ / ۰۵ / ۹۹ - ۰۸:۱۱ با سلام و تشکر از سایت خوبتون ، اگر بخوایم یک کوئری در ef بدون هیچ لاکی اجرا بشه تا کوئری سریع اجرا بشه مثل وقتی که در کد sql میزنیم (nolock) ، چه کار باید کرد؟ پاسخ به دیدگاه