خانه SQL Server انواع Isolation Level در SSIS [بخش دوم] SQL Server نوشته شده توسط: زهرا فرهنگی ۱۱ شهریور ۱۳۹۹ زمان مطالعه: 22 دقیقه ۵ (۱) ReadCommitted بیایید بدون آنکه تغییری در اسکریپت و طراحی پکیج مثال قبل ایجاد کنیم تنها نوع Isolation Level مورد استفاده آن را به ReadCommitted تغییر دهیم و تاثیر آن را در روال اجرای پکیج بررسی کنیم. در حال حاضر پس از تغییر, ویژگیهای کامپوننت Sequence Container بصورت زیر میباشد.حال اگر مجددا روال اجرا را همانند مثال قبل طی کنیم مشاهده میکنیم که با اجرا شدن پکیج, در مرحلۀ “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 چیست# sql# SQL Server# SSIS# TRANSACTION در SQL# انواع Isolation Level# سطوح isolation level# همزمانی در sql server معرفی نویسنده مقالات 51 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده زهرا فرهنگی کارشناس پایگاه داده، در حال کسب تجربه در زمینههای تحلیل انباره داده، BI، بهینه سازی پایگاههای داده پروفایل نویسنده معرفی محصول احسان حسین پور In-Memory OLTP و Columnstore در SQL Server 1.590.000 تومان مقالات مرتبط ۱۰ اردیبهشت SQL Server استفاده از Credential و Proxy در SQL Server Agent حسن سلیمانی ۰۷ اردیبهشت SQL Server استفاده از Operator ها در SQL Server Agent حسن سلیمانی ۰۵ اردیبهشت SQL Server بررسی نحوه ایجاد Job در SQL Server حسن سلیمانی ۲۹ فروردین SQL Server آشنایی با بخش های مختلف SQL Server Agent حسن سلیمانی دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ hassan.7ny ۱۸ / ۰۶ / ۹۹ - ۰۴:۰۴ با سلام. عالی بود. پاسخ به دیدگاه hassan.7ny ۱۸ / ۰۶ / ۹۹ - ۰۴:۰۴ با سلام. عالی بود. پاسخ به دیدگاه