آشنایی با Isolation Level در SQL Server

آشنایی با Isolation Level در 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 گفته می‌شود.

دوره In-Memory OLTP و Columnstore در SQL Server

۲- 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آشنایی با Isolation Level در SQL Server
۱۰ ثانیه منتظر بمانید و دوباره دستورات دوم را اجرا کنید. این بار خروجی متفاوت خواهد بود. مانند شکل زیر
آشنایی با 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آشنایی با 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آشنایی با 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

چه رتبه ای می‌دهید؟

میانگین ۴.۲ / ۵. از مجموع ۲۰

اولین نفر باش

title sign
دانلود مقاله
آشنایی با Isolation Level در SQL Server
فرمت PDF
9 صفحه
حجم 1 مگابایت
دانلود مقاله
title sign
معرفی نویسنده
مسعود طاهری
مقالات
28 مقاله توسط این نویسنده
محصولات
59 دوره توسط این نویسنده
مسعود طاهری

مسعود طاهری مدرس و مشاور ارشد SQL Server & BI ،  مدیر فنی پروژه‌های هوش تجاری (بیمه سامان، اوقاف، جین وست، هلدینگ ماهان و...) ، مدرس دوره‌هــای SQL Server و هوش‌تجاری در شرکت نیک‌آموز و نویسنده کتاب PolyBase در SQL Server

پروفایل نویسنده
title sign
دیدگاه کاربران

    • طبق گفته مایکروسافت، ایزولیشن لول رو روی کانکشن، روی دیتابیس و روی state میشه تنظیم کرد
      سوالم اینه که چطوری روی دیتابیسم لول رو serializable کنم ؟
      می خوام کل تراکنش های دیتابیسم serializable بشن

    • توضیحات کامل و قابل درک به سادگی

    • خیلی عالی و جامع بود.

    • سلام ممنون بابت مطلب مفید
      برای جلوگیری از Dirty Write کدوم Isolation Level مناسب هست؟

    • البته دوستان بخاطر اشتباهی تاپی عذرخواهی می کنم
      ای دی

      • داشتن همراهانی مثل شما برای ما افتخاره

    • سلام از مهندس گرامی ودوستان خواهش دارم سوال بنده جواب بدهند.
      فرض مثال
      بنده یک جدول درخواست شفارش دارم که باید افراد مختلف تایید کنند
      اطلاعات جدول شامل
      ۱-ادی جدول
      ۲-ادی ثبت کننده
      ۳الی ۸-سایر مشخصات
      ۹-ادی تایید کننده اول
      ۱۰ادی تایید کننده دوم
      بنده می خواهم از جدول یوزر ادی ها تایید کننده ها را داشته باشم
      موضوع این که مشکل در relatioدارم که نمی شود از یک جدول شفارش به جدول یوزر هم برای درخواست کننده وهم برای تایید کنندهها ارتباط برقرار کنم البته بنده دوست ندارم از جداول میانی استفاده کنم
      دوستان ممنون میشم کمک کنند

    • سلام از مهندس گرامی ودوستان خواهش دارم سوال بنده جواب بدهند.
      فرض مثال
      بنده یک جدول درخواست شفارش دارم که باید افراد مختلف تایید کنند
      اطلاعات جدول شامل
      ۱-ادی جدول
      ۲-ادی ثبت کننده
      ۳الی ۸-سایر مشخصات
      ۹-ادی تایید کننده اول
      ۱۰ادی تایید کننده دوم
      بنده می خواهم از جدول یوزر ادی ها تایید کننده ها را داشته باشم
      موضوع این که مشکل در relatioدارم که نمی شود از یک جدول شفارش به جدول یوزر هم برای درخواست کننده وهم برای تایید کنندهها ارتباط برقرار کنم البته بنده دوست ندارم از جداول میانی استفاده کنم
      دوستان ممنون میشم کمک کنند

      • سلام نیک آموزی عزیز
        باتوجه به طراحی شما ناچار هستید که دوتا Relation با جدول یوزرهاتون برقرا کنید.
        حال روال و شرایط موجود در سفارش رو باید در کد برنامه پیاده سازی کنید

    • سلام مطالب سایتتون رو دیدم. فوق العاده س توی سایتای فارسی. واقعا تبریک میگم که همچین سایت تخصصی و موفقی دارید.
      و یک سوال هم از خدمتتون دارم :
      دو تا جدول داریم مثلا فاکتور و ریزخرید(اینطور فرض کنید چون توضیح جداول اصلی خیلی زمان بره). با صدور فاکتور، به تعداد مشخص و با آیدی های متوالی، ریزخرید توی جدول خودش ایجاد میشه. میخوایم طوری باشه که این توالی حفظ بشه! فاکتور حاوی “تعداد، ابتدا و انتها” واسه ریزخریدهاش هست. واسه همین باید این توالی حفظ بشه.
      سوالی که از خدمتتون دارم اینه که چطور باید اینکار رو کرد؟ بیشتر دغدغه م روی همزمانی درخواستها به دیتابیس هست.(موقع اینزرت و البته احتمالا حذف. البته ریزخریدها ثابت هستن و حذف و اضافه شدنشون تابع خود فاکتور هست! یعنی تقریبا میشه گفت ثابت هستن به ازای یک فاکتور)
      از اونجایی که تخصص روی SQL ندارم، روش ابتدایی که به ذهنم رسیده بود، یک جدول اضافی با یک فیلد بیتی برای لاک کردن!!! بود که امیدوارم زیاد نخندید بهش! 🙂
      پیشاپیش متشکر از راهنماییتون…

      • با سلام و تشکر از نظر شما.

        برای طراحی جدول فاکتور شما باید به روش زیر اقدام کنید:
        ۱. یک جدول به عنوان Master باید داشته باشید که اطلاعات (ID مشتری، ID فاکتور، تاریخ فاکتور، مبلغ کل فاکتور، شماره فاکتور) داشته باشد.
        ۲. یک جدول به عنوان Detail باید داشته باشید که اطلاعات ( ID , Detail ID فاکتور، ID کالا، تعداد کالا، مبلغ کالا، مبلغ کل هر سطر (تعداد * مبلغ کالا)) را داشته باشید.

        کلید اصلی در جدول Master عبارت است از ID فاکتور و کلید خارجی در جدول Detail عبارت است از ID فاکتور

        سعی کنید از TVP استفاده کنید تا مشکلات همزمانی و Lock را نداشته باشید.

        موفق باشید.

  • 1
  • 2