خانه SQL Server آشنایی با Isolation Level در SQL Server SQL Server افزایش سرعت SQL Server نوشته شده توسط: مسعود طاهری تاریخ انتشار: ۲۷ آبان ۱۳۹۳ آخرین بروزرسانی: ۲۴ آبان ۱۴۰۲ زمان مطالعه: 12 دقیقه ۴.۱ (۲۵) مقدمه در یکی از مقالههای سایت توضیحاتی در مورد تراکنشها (مدیریت تراکنش ها) ارائه شده است. در این مقاله در مورد Isolation Levelهای تراکنشها صحبت خواهیم کرد. در ابتدا تعریفی از Isolation Level را میآوریم. Isolation Levelها در SQL Server روشهای قفل گذاری در میان چند تراکنش را مشخص میکند. به بیان ساده تر اگر ما یک عمل به روز رسانی را داخل یک تراکنش انجام دهیم و از طرف دیگر با استفاده از دستور SELECT داده های مربوط به همان به روز رسانی را بخواهیم استخراج کنیم، اینجا Isolation Level نحوه دستیابی ما به اطلاعات را مشخص خواهد کرد. در ادامه این مقاله هر مورد از انواع Isolation Level همراه با یک مثال توضیح داده شده است. قبل از شروع بحث لازم است تا یک دیتابیس جدید و جدولی را به عنوان نمونه ایجاد نمایید. CREATE DATABASE IsolationLevelTest GO CREATE TABLE TestTable ( ID int IDENTITY, Field1 INT NULL, Field2 INT NULL, Field3 INT NULL ) GO INSERT INTO TestTable (Field1,Field2,Field3) VALUES (1,2,3) INSERT INTO TestTable (Field1,Field2,Field3) VALUES (1,2,3) INSERT INTO TestTable (Field1,Field2,Field3) VALUES (1,2,3) INSERT INTO TestTable (Field1,Field2,Field3) VALUES (1,2,3) همچنین در ادامه بحث لازم است تا با اصطلاحات زیر آشنا شوید ۱- Dirty Reads فرض کنید تراکنشی باعث تغییر اطلاعات در بدنه خود شده است ولی هنوز آن تراکنش Commit نشده است. اگر شرایط به گونهای باشد که با استفاده از دستور Select دادههای تغییر یافته معلق را که هنوز تایید نشده است را استخراج نماییم و آنها را مشاهده کنیم اصطلاحا به آن Dirty Read گفته میشود. ۲- Phantom Reads اگر حین انجام Select داخل یک تراکنش، اطلاعات جدیدی در دیتابیس درج شود و این درج اطلاعات روی نتیجه Select ما تاثیر بگذارد (سطرهای جدید به نتیجه Select اضافه شود) اصطلاحا گفته میشود که Phantom Read اتفاق افتاده است. در ادامه مقاله این موضوع به صورت عینی توضیح داده شده است. انواعIsolation Level Read UnCommitted Read Committed Repeatable Read Serializable Snapshot ۱- Read UnCommitted این سطح ارزیابی به نوعی پایین ترین سطح ارزیابی میباشد به طوری که هیچ قفلی بر روی رکوردها اعمال نمیشود. مثلا اگر اطلاعاتی را داخل یک تراکنش به روزرسانی مینمایید یک تراکنش دیگر میتواند این اطلاعات را تغییر دهد. یا اگر در حال اجرای تراکنش، یک دستور Select بنویسید اطلاعاتی را دریافت کنید که هنوز تایید و نهایی نشده است. با این اوصاف میتوان گفت اطلاعاتی که در این Selectها به دست میآید به احتمال زیاد نامعتبر خواهد بود. همان Dirty Read در اینجا اتفاق خواهد افتاد. یک مثال: یک صفحه New Query باز کنید و دستور زیر را در آن کپی نمایید BEGIN TRAN UPDATE TestTable SET Field1 = 2 WAITFOR DELAY '00:00:10' ROLLBACK یک صفحه New Query دیگر باز کنید و دستور زیر را در آن کپی نمایید SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM TestTable دستورات اول را اجرا کرده و سریع دستورات دوم را اجرا نمایید. خروجی مانند زیر خواهد بود آشنایی با Isolation Level در SQL Server ۱۰ ثانیه منتظر بمانید و دوباره دستورات دوم را اجرا کنید. این بار خروجی متفاوت خواهد بود. مانند شکل زیر همان طور که می بینید در اولین اجرای دستورات دوم، اطلاعاتی را به دست آوردیم که تغییر یافته بود ولی هنوز نهایی نشده بود. در واقع Dirty Read اتفاق افتاد. ۲- Read Committed این سطح ارزیابی سطح پیش فرض SQL Server میباشد. در این حالت اطلاعاتی که داخل یک تراکنش باشد تا لحظه اتمام آن تراکنش به حالت قفل در خواهد آمد. یعنی نه میتوان آن ها را Select کرد و نه میتوان آن ها را تغییر داد یا حذف کرد. چنانچه دستور SELECT شما همزمان با اجرای تراکنش باشد اجرای دستور SELECT منتظر اتمام تراکنش خواهد ماند. با یک مثال بهتر متوجه خواهید شد. یک صفحه New Query باز کنید و دستورات زیر را در آن کپی نمایید BEGIN TRAN UPDATE TestTable SET Field1 = 2 WAITFOR DELAY '00:00:10' ROLLBACK یک صفحه New Query دیگر باز کنید و دستورات زیر را در آن کپی نمایید SELECT * FROM TestTable همانند مثال قبل ابتدا دستورات اول را اجرا کنید و سپس سریع دستورات دوم را اجرا نمایید. همان طور که میبینید نتیجه خروجی دستور دوم با تاخیر نمایش داده خواهد شد. و این به خاطر تاخیر در اتمام تراکنش در دستورات اول میباشد. ۳- Reapeatable Read این سطح ارزیابی شبیه Read Committed می باشد ولی با این تفاوت که Reapeatable Read این تضمین را به ما میدهد که تمامی دستورات تغییر اطلاعاتی، که شما آن ها را Select کردهاید منتظر خواهد ماند تا Select شما به اتمام برسد. با یک مثال بهتر متوجه خواهید شد: یک صفحه New Query باز کنید و دستورات زیر را در آن کپی نمایید SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM TestTable WAITFOR DELAY '00:00:10' SELECT * FROM TestTable ROLLBACK یک صفحه New Query دیگر باز کنید و دستورات زیر را در آن کپی نمایید : UPDATE TestTable SET Field1 = 7 همانند مثال های قبل ابتدا دستورات اول را اجرا نموده و سپس سریع دستورات دوم را اجرا نمایید. خروجی دستورات اول به شکل زیر خواهد بود. آشنایی با Isolation Level در SQL Server همان طور که مشاهده می کنید دستور Update که اجرا شده است تاثیری در خروجی Selectهای ما نداشته و خروجی هر دو Select شبیه هم میباشد. اگر در دستورات اول Isolation Level را به Read Committed تغییر دهید خروجی Selectها شبیه هم نخواهد بود. به عبارتی دستور Update منتظر اتمام دستورات اول نخواهد ماند. نکته : اگر در حین تراکنش با سطح ارزیابی Reapeatable Read دستور Insert در جدول مربوطه اجرا شود و دادههای جدید اضافه شود، Phantom Read اتفاق خواهد افتاد. یعنی در خروجی آخرین Select سطرهای اضافه شده نیز نمایش داده خواهد شد. ۴- Serializable این سطح ارزیابی شبیه Reapeatable Read می باشد منتها Serializable یک تضمین اضافی را نیز به ما میدهد که اجازه درج رکورد جدید در اطلاعات مربوطه را تا اتمام تراکنش نخواهد داد. بنابر این جلوی Phantom Read نیز گرفته خواهد شد. با یک مثال بهتر متوجه موضوع خواهید شد. یک صفحه New Query باز کنید و دستورات زیر را در آن کپی نمایید SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM TestTable WAITFOR DELAY '00:00:10' SELECT * FROM TestTable ROLLBACK یک صفحه New Query دیگر باز کنید و دستورات زیر را در آن کپی نمایید INSERT INTO TestTable(Field1,Field2,Field3) VALUES (100,100,100) مانند مثالهای قبلی ابتدا دستورات اول را اجرا کرده و سپس سریع دستورات دوم را اجرا نمایید. خروجی مانند زیر خواهد بود آشنایی با Isolation Level در SQL Server همان طور که مشاهده می کنید خروجی هر دو Select شبیه هم میباشد. بنابر این دستور Insert که در دستورات دوم نوشته شده است منتظر اتمام تراکنش در دستورات اول مانده است. Snapshot : این سطح ارزیابی دقیقا مشابه Serializable میباشد با این تفاوت که Snapshot عملیات Update و Insert را قفل نخوهد کرد. شاید این سوال به ذهنتان برسد که تغییر اطلاعات چگونه مدیریت میشود؟ پاسخ این است که اگر عملیاتی بعد از Snapshot بر روی دادهها تغییراتی را ایجاد کنند یا داده های جدیدی در دیتابیس ثبت شوند این تغییرات و دادههای جدید در tempdb ذخیره خواهد شد. ضمنا یک Row Version هم به این اطلاعات اضافه میشود. بعد از اتمام تراکنش Snapshot، آخرین اطلاعات از tempdb به دیتابیس شما منتقل خواهد شد. برای فعال سازی این Isolation Level بایستی دستور زیر را اجرا نمایید. ALTER DATABASE IsolationLevelTest SET ALLOW_SNAPSHOT_ISOLATION ON یک صفحه New Query باز کنید و دستورات زیر را در آن کپی نمایید SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT * FROM TestTable WAITFOR DELAY '00:00:10' SELECT * FROM TestTable ROLLBACK یک صفحه New Query دیگر باز کنید و دستورات زیر را در آن کپی نمایید INSERT INTO TestTable(Field1,Field2,Field3) VALUES (200,200,200) همانند مثالهای قبلی ابتدا دستورات اول را اجرا کرده و سپس سریع دستورات دوم را اجرا نمایید. همان طور که میبینید اجرای دستورات دوم منتظر اتمام تراکنش دستورات اول نخواهد ماند ولی جالب اینجاست که خروجی هر دو Select در دستورات اول دقیقا مشابه هم میباشد. مانند شکل زیر چه رتبه ای میدهید؟ میانگین ۴.۱ / ۵. از مجموع ۲۵ اولین نفر باش دانلود مقاله آشنایی با Isolation Level در SQL Server فرمت PDF 9 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده مقالات 24 مقاله توسط این نویسنده محصولات 65 دوره توسط این نویسنده مسعود طاهری مسعود طاهری مدرس و مشاور ارشد SQL Server & BI ، مدیر فنی پروژههای هوش تجاری (بیمه سامان، اوقاف، جین وست، هلدینگ ماهان و...) ، مدرس دورههــای SQL Server و هوشتجاری در شرکت نیکآموز و نویسنده کتاب PolyBase در SQL Server معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ حمید ۲۳ / ۱۰ / ۰۲ - ۰۲:۰۹ طبق گفته مایکروسافت، ایزولیشن لول رو روی کانکشن، روی دیتابیس و روی state میشه تنظیم کرد سوالم اینه که چطوری روی دیتابیسم لول رو serializable کنم ؟ می خوام کل تراکنش های دیتابیسم serializable بشن پاسخ به دیدگاه ایرج ۰۳ / ۱۰ / ۰۱ - ۰۱:۵۳ توضیحات کامل و قابل درک به سادگی پاسخ به دیدگاه حسین قربانی ۱۱ / ۰۷ / ۰۱ - ۱۰:۱۱ خیلی عالی و جامع بود. پاسخ به دیدگاه سارا ۲۲ / ۰۱ / ۰۱ - ۰۱:۲۲ سلام ممنون بابت مطلب مفید برای جلوگیری از Dirty Write کدوم Isolation Level مناسب هست؟ پاسخ به دیدگاه آرزو محمدزاده ۲۳ / ۰۱ / ۰۱ - ۰۲:۲۴ درود بر شما برای دریافت پاسخ دیدگاهتون بر روی لینک زیر کلیک نمایید. https://b2n.ir/z20035 همچنین از لینک زیر هم می توانید اطلاعات مفیدی کسب نمایید. https://www.mssqltips.com/sqlservertip/4468/compare-sql-server-nolock-and-readpast-table-hints/ پاسخ به دیدگاه سارا ۲۳ / ۰۱ / ۰۱ - ۰۲:۵۴ ممنون بابت راهنمایی پاسخ به دیدگاه احسان ۲۸ / ۰۲ / ۹۹ - ۱۰:۳۸ البته دوستان بخاطر اشتباهی تاپی عذرخواهی می کنم ای دی پاسخ به دیدگاه جواد اسماعیلی ۱۶ / ۰۶ / ۰۰ - ۰۶:۱۰ داشتن همراهانی مثل شما برای ما افتخاره پاسخ به دیدگاه احسان ۲۸ / ۰۲ / ۹۹ - ۱۰:۰۲ سلام از مهندس گرامی ودوستان خواهش دارم سوال بنده جواب بدهند. فرض مثال بنده یک جدول درخواست شفارش دارم که باید افراد مختلف تایید کنند اطلاعات جدول شامل ۱-ادی جدول ۲-ادی ثبت کننده ۳الی ۸-سایر مشخصات ۹-ادی تایید کننده اول ۱۰ادی تایید کننده دوم بنده می خواهم از جدول یوزر ادی ها تایید کننده ها را داشته باشم موضوع این که مشکل در relatioدارم که نمی شود از یک جدول شفارش به جدول یوزر هم برای درخواست کننده وهم برای تایید کنندهها ارتباط برقرار کنم البته بنده دوست ندارم از جداول میانی استفاده کنم دوستان ممنون میشم کمک کنند پاسخ به دیدگاه احسان ۲۸ / ۰۲ / ۹۹ - ۱۰:۰۲ سلام از مهندس گرامی ودوستان خواهش دارم سوال بنده جواب بدهند. فرض مثال بنده یک جدول درخواست شفارش دارم که باید افراد مختلف تایید کنند اطلاعات جدول شامل ۱-ادی جدول ۲-ادی ثبت کننده ۳الی ۸-سایر مشخصات ۹-ادی تایید کننده اول ۱۰ادی تایید کننده دوم بنده می خواهم از جدول یوزر ادی ها تایید کننده ها را داشته باشم موضوع این که مشکل در relatioدارم که نمی شود از یک جدول شفارش به جدول یوزر هم برای درخواست کننده وهم برای تایید کنندهها ارتباط برقرار کنم البته بنده دوست ندارم از جداول میانی استفاده کنم دوستان ممنون میشم کمک کنند پاسخ به دیدگاه جواد اسماعیلی ۱۶ / ۰۶ / ۰۰ - ۰۶:۳۳ سلام نیک آموزی عزیز باتوجه به طراحی شما ناچار هستید که دوتا Relation با جدول یوزرهاتون برقرا کنید. حال روال و شرایط موجود در سفارش رو باید در کد برنامه پیاده سازی کنید پاسخ به دیدگاه محمدحسین فخرآوری ۰۹ / ۱۲ / ۹۷ - ۰۲:۴۹ درود عالی پاسخ به دیدگاه محمدحسین فخرآوری ۰۹ / ۱۲ / ۹۷ - ۰۲:۴۹ درود عالی پاسخ به دیدگاه علی ۱۰ / ۰۳ / ۹۶ - ۰۱:۵۰ سلام مطالب سایتتون رو دیدم. فوق العاده س توی سایتای فارسی. واقعا تبریک میگم که همچین سایت تخصصی و موفقی دارید. و یک سوال هم از خدمتتون دارم : دو تا جدول داریم مثلا فاکتور و ریزخرید(اینطور فرض کنید چون توضیح جداول اصلی خیلی زمان بره). با صدور فاکتور، به تعداد مشخص و با آیدی های متوالی، ریزخرید توی جدول خودش ایجاد میشه. میخوایم طوری باشه که این توالی حفظ بشه! فاکتور حاوی “تعداد، ابتدا و انتها” واسه ریزخریدهاش هست. واسه همین باید این توالی حفظ بشه. سوالی که از خدمتتون دارم اینه که چطور باید اینکار رو کرد؟ بیشتر دغدغه م روی همزمانی درخواستها به دیتابیس هست.(موقع اینزرت و البته احتمالا حذف. البته ریزخریدها ثابت هستن و حذف و اضافه شدنشون تابع خود فاکتور هست! یعنی تقریبا میشه گفت ثابت هستن به ازای یک فاکتور) از اونجایی که تخصص روی SQL ندارم، روش ابتدایی که به ذهنم رسیده بود، یک جدول اضافی با یک فیلد بیتی برای لاک کردن!!! بود که امیدوارم زیاد نخندید بهش! 🙂 پیشاپیش متشکر از راهنماییتون… پاسخ به دیدگاه فرید طاهری ۱۳ / ۰۳ / ۹۶ - ۰۷:۰۶ با سلام و تشکر از نظر شما. برای طراحی جدول فاکتور شما باید به روش زیر اقدام کنید: ۱. یک جدول به عنوان Master باید داشته باشید که اطلاعات (ID مشتری، ID فاکتور، تاریخ فاکتور، مبلغ کل فاکتور، شماره فاکتور) داشته باشد. ۲. یک جدول به عنوان Detail باید داشته باشید که اطلاعات ( ID , Detail ID فاکتور، ID کالا، تعداد کالا، مبلغ کالا، مبلغ کل هر سطر (تعداد * مبلغ کالا)) را داشته باشید. کلید اصلی در جدول Master عبارت است از ID فاکتور و کلید خارجی در جدول Detail عبارت است از ID فاکتور سعی کنید از TVP استفاده کنید تا مشکلات همزمانی و Lock را نداشته باشید. موفق باشید. پاسخ به دیدگاه 1 2