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

مقدمه

در یکی از مقاله‌های سایت توضیحاتی در مورد تراکنش‌ها (مدیریت تراکنش ها) ارائه شده است. در این مقاله در مورد 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
10 ثانیه منتظر بمانید و دوباره دستورات دوم را اجرا کنید. این بار خروجی متفاوت خواهد بود. مانند شکل زیر
همان طور که می بینید در اولین اجرای دستورات دوم، اطلاعاتی را به دست آوردیم که تغییر یافته بود ولی هنوز نهایی نشده بود. در واقع 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 در دستورات اول دقیقا مشابه هم می‎باشد. مانند شکل زیر