خانه SQL Server آشنایی با Isolation Level در 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 در دستورات اول دقیقا مشابه هم میباشد. مانند شکل زیر چه رتبه ای میدهید؟ میانگین ۴ / ۵. از مجموع ۲۲ اولین نفر باش برچسب ها # SQL Server# آشنایی با Isolation Level# آموزش SQL Server# تراکنش در SQL Server دانلود مقاله آشنایی با Isolation Level در SQL Server فرمت PDF 9 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده مقالات 28 مقاله توسط این نویسنده محصولات 61 دوره توسط این نویسنده مسعود طاهری مسعود طاهری مدرس و مشاور ارشد SQL Server & BI ، مدیر فنی پروژههای هوش تجاری (بیمه سامان، اوقاف، جین وست، هلدینگ ماهان و...) ، مدرس دورههــای SQL Server و هوشتجاری در شرکت نیکآموز و نویسنده کتاب PolyBase در SQL Server پروفایل نویسنده معرفی محصول احسان حسین پور In-Memory OLTP و Columnstore در SQL Server 1.890.000 تومان مقالات مرتبط ۲۶ فروردین SQL Server دستور SELECT TOP در SQL Server تیم فنی نیک آموز ۱۹ فروردین SQL Server راهنمای پیکربندی Authentication و Authorization در SQL Server به بهترین روش تیم فنی نیک آموز ۱۴ فروردین SQL Server آموزش رمزگذاری اطلاعات در SQL Server تیم فنی نیک آموز ۰۵ فروردین SQL Server عملگر LIKE در SQL Server – نحوه استفاده از عملگر LIKE چگونه است؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ مسعود طاهری ۲۳ / ۰۵ / ۹۴ - ۰۸:۱۶ بله استفاده مناسب از Isolation Level می تواند جلوی Deadlock را بگیرد. در ضمن یادتون باشه از Isolation Level نوع Uncommitted استفاده کنید تمامی مشکلات همزمانی را خواهید داشت. فیلم مسابقه و جواب آن را نگاه کنید. در کوئری مربوط به آن از With (NoLock) استفاده کرده ایم عملکرد آن دقیقا مثل Isolation Level نوع Uncommitted می باشد. بهترین حالت برای پروژه ها ۱- ReadCommitted ۲-SnapShot (با توجه به اینکه از TempDB زیاد استفاده می شود. بهتر است که تنظیمات اصولی برای TempDB انجام شود.) به زودی یک دوره ۴ جلسه ای در زمینه افزایش سرعت کوئری ها برگزار خواهیم کرد. در این دوره این موارد را به خوبی بررسی خواهیم کرد. پاسخ به دیدگاه 1 2