Isolation Level در SQL

مقدمه

با گسترش سامانه‌های نرم‌افزاری مبتنی بر پایگاه داده و افزایش همزمانی در دسترسی کاربران و سرویس‌ها به داده‌ها، مسئله‌ی مدیریت صحیح همزمانی به یکی از چالش‌های بنیادین در طراحی سیستم‌های اطلاعاتی تبدیل شده است. در چنین محیط‌هایی، عملیات مختلفی به‌طور همزمان اقدام به خواندن و تغییر داده‌های مشترک می‌کنند و در صورت نبود سازوکارهای کنترلی مناسب، احتمال بروز ناسازگاری، نتایج غیرقابل پیش‌بینی و نقض یکپارچگی داده‌ها به‌طور قابل توجهی افزایش می‌یابد.

برای پاسخ به این چالش، پایگاه‌های داده رابطه‌ای از مفهوم تراکنش (Transaction) استفاده می‌کنند. تراکنش مجموعه‌ای از عملیات پایگاه داده است که باید به‌صورت یک واحد منطقی و غیرقابل تجزیه اجرا شود و ویژگی‌های شناخته‌شده‌ی ACID—شامل اتمی بودن (Atomicity)، سازگاری (Consistency)، جداسازی (Isolation) و ماندگاری (Durability)—را رعایت کند. در میان این ویژگی‌ها، «جداسازی» نقشی کلیدی در تعیین نحوه‌ی تعامل تراکنش‌های همزمان با یکدیگر ایفا می‌کند.

Isolation Level یا سطح جداسازی، مشخص می‌کند که یک تراکنش تا چه حد از تغییرات ایجادشده توسط سایر تراکنش‌های همزمان آگاه باشد و چه نوع ناهنجاری‌هایی در خواندن داده‌ها مجاز یا غیرمجاز تلقی شوند. استاندارد SQL با هدف ایجاد تعادل میان صحت داده‌ها و کارایی سیستم، چهار سطح اصلی برای جداسازی تراکنش‌ها تعریف کرده است. هر یک از این سطوح، درجه‌ای متفاوت از محدودیت را بر دسترسی همزمان به داده‌ها اعمال می‌کند و در نتیجه، رفتار سیستم را در شرایط بار کاری مختلف تغییر می‌دهد.

اهمیت Isolation Level تنها به مباحث نظری محدود نمی‌شود، بلکه تأثیر آن به‌طور مستقیم در طراحی سیستم‌های واقعی، به‌ویژه سامانه‌های مالی، بانکی، تحلیلی و توزیع‌شده قابل مشاهده است. انتخاب نادرست سطح جداسازی می‌تواند منجر به بروز خطاهایی شود که شناسایی و بازتولید آن‌ها دشوار است، در حالی که انتخاب بیش از حد محافظه‌کارانه نیز ممکن است کارایی و مقیاس‌پذیری سیستم را به‌شدت کاهش دهد. از این رو، درک دقیق سطوح مختلف Isolation Level و پیامدهای عملی هر یک، برای طراحان و توسعه‌دهندگان سیستم‌های مبتنی بر پایگاه داده امری ضروری محسوب می‌شود.

در این مقاله سعی میکنم با رویکردی تحلیلی، سطوح مختلف Isolation Level در SQL را معرفی کرده و با ارائه‌ی مثال‌های عملی و کوئری‌های واقعی، تأثیر هر سطح را بر رفتار تراکنش‌های همزمان بررسی کنیم. هدف اصلی، ایجاد درکی عمیق و کاربردی از این مفهوم به‌منظور اتخاذ تصمیم‌های آگاهانه در طراحی و پیاده‌سازی سیستم‌های پایگاه داده است.

سطوح Isolation Level در SQL

استاندارد SQL برای مدیریت همزمانی تراکنش‌ها، چهار سطح اصلی Isolation Level را تعریف می‌کند که هر یک میزان مشخصی از جداسازی را میان تراکنش‌های همزمان اعمال می‌کنند. هدف از این سطوح، ایجاد تعادل میان صحت داده‌ها و کارایی سیستم است؛ به این معنا که هرچه محدودیت‌ها سخت‌گیرانه‌تر باشند، احتمال بروز ناهنجاری‌های همزمانی کاهش می‌یابد، اما در مقابل، سربار پردازشی و کاهش همزمانی افزایش پیدا می‌کند. علاوه بر این سطوح استاندارد، برخی سامانه‌های مدیریت پایگاه داده مفهومی به نام Snapshot Isolation را نیز ارائه می‌دهند که اگرچه جزو سطوح رسمی استاندارد SQL نیست، اما در عمل نقش مهمی در معماری سیستم‌های مدرن ایفا می‌کند.

سطح Read Uncommitted

پایین‌ترین سطح جداسازی، Read Uncommitted است. در این سطح، یک تراکنش می‌تواند تغییرات اعمال‌شده توسط سایر تراکنش‌ها را حتی پیش از commit شدن مشاهده کند. این رفتار منجر به پدیده Dirty Read می‌شود؛ حالتی که در آن داده‌ی خوانده‌شده ممکن است در ادامه rollback شود و هرگز به وضعیت پایدار نرسد. Read Uncommitted کمترین سطح تضمین سازگاری را فراهم می‌کند و معمولاً تنها در سناریوهایی با حساسیت پایین و تمرکز بر بیشینه‌سازی کارایی مورد استفاده قرار می‌گیرد.

ابتدا Transaction A را در نظر بگیرید:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;

UPDATE accounts
SET balance = 2000
WHERE id = 1;

در این لحظه، مقدار balance به 2000 تغییر کرده اما هنوز commit نشده است. حال Transaction B به‌صورت همزمان اجرا می‌شود:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;

SELECT balance
FROM accounts
WHERE id = 1;

خروجی این کوئری مقدار 2000 خواهد بود، در حالی که این مقدار هنوز نهایی نشده است. حال اگر Transaction A دچار خطا شود و rollback گردد:

ROLLBACK;

داده به مقدار قبلی یعنی 1000 بازمی‌گردد، اما Transaction B عددی را مشاهده کرده که در نهایت هرگز وجود نداشته است. این مثال به‌صورت عملی نشان می‌دهد چرا Dirty Read می‌تواند منجر به تحلیل‌های نادرست شود.

سطح Read Committed

سطح Read Committed تضمین می‌کند که هر تراکنش فقط داده‌هایی را مشاهده کند که commit شده‌اند. در این سطح، Dirty Read حذف می‌شود و هر خواندن داده مبتنی بر وضعیت پایدار پایگاه داده است. با این حال، Read Committed همچنان اجازه‌ی بروز Non-Repeatable Read را می‌دهد؛ به این معنا که یک تراکنش ممکن است در خواندن‌های مکرر از یک ردیف مشخص، مقادیر متفاوتی دریافت کند. این رفتار نتیجه‌ی commit شدن تغییرات توسط تراکنش‌های دیگر در فاصله‌ی بین دو عملیات خواندن است.

Transaction A را به شکل زیر در نظر بگیرید:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

SELECT balance
FROM accounts
WHERE id = 1;

خروجی در این مرحله مقدار 1000 است. حال Transaction B اجرا می‌شود:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

UPDATE accounts
SET balance = 1500
WHERE id = 1;

COMMIT;

پس از commit شدن Transaction B، Transaction A دوباره همان داده را می‌خواند:

SELECT balance
FROM accounts
WHERE id = 1;

این بار خروجی 1500 خواهد بود. در نتیجه، یک تراکنش واحد در دو بار خواندن یک ردیف، دو مقدار متفاوت دریافت کرده است. این دقیقاً مصداق Non-Repeatable Read است و می‌تواند در محاسبات تجمعی یا گزارش‌گیری مشکل‌ساز شود.

سطح Repeatable Read

در سطح Repeatable Read، پایگاه داده تضمین می‌کند که ردیف‌هایی که یک تراکنش آن‌ها را خوانده است، تا پایان عمر آن تراکنش تغییر نخواهند کرد. به این ترتیب، Non-Repeatable Read حذف می‌شود و ثبات داده‌های خوانده‌شده افزایش می‌یابد. با وجود این، Repeatable Read لزوماً مانع بروز Phantom Read نمی‌شود. در چنین شرایطی، ممکن است یک تراکنش در اجرای مجدد یک کوئری شرطی، ردیف‌های جدیدی را مشاهده کند که در اجرای اولیه وجود نداشته‌اند.

Transaction A به‌صورت زیر آغاز می‌شود:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;

SELECT balance
FROM accounts
WHERE id = 1;

خروجی مقدار 1500 است. حال Transaction B تلاش می‌کند مقدار را تغییر دهد:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;

UPDATE accounts
SET balance = 3000
WHERE id = 1;

COMMIT;

Transaction A دوباره داده را می‌خواند:

SELECT balance
FROM accounts
WHERE id = 1;

خروجی همچنان 1500 خواهد بود و تغییر انجام‌شده توسط Transaction B تا پایان Transaction A قابل مشاهده نیست. این رفتار نشان می‌دهد که Non-Repeatable Read در این سطح حذف شده است.

اما حال سناریوی Phantom Read را بررسی می‌کنیم. فرض کنید Transaction A کوئری زیر را اجرا می‌کند:

SELECT *
FROM accounts
WHERE balance > 1000;

در همین زمان، Transaction B یک ردیف جدید درج می‌کند:

INSERT INTO accounts (id, balance)
VALUES (2, 5000);

COMMIT;

اگر Transaction A مجدداً همان کوئری را اجرا کند، ممکن است ردیف جدید را مشاهده کند. این ردیف در اجرای اول وجود نداشته و اکنون ظاهر شده است؛ این پدیده Phantom Read نام دارد.

سطح Serializable

بالاترین سطح استاندارد جداسازی، Serializable است. در این سطح، پایگاه داده تضمین می‌کند که نتیجه‌ی اجرای تراکنش‌های همزمان معادل اجرای کاملاً ترتیبی آن‌ها باشد. در نتیجه، تمامی ناهنجاری‌های شناخته‌شده از جمله Dirty Read، Non-Repeatable Read و Phantom Read حذف می‌شوند. این سطح بالاترین میزان سازگاری را فراهم می‌کند، اما هزینه‌ی آن کاهش همزمانی، افزایش قفل‌گذاری و احتمال افت محسوس کارایی سیستم است. به همین دلیل، Serializable معمولاً برای عملیات‌های حیاتی و بسیار حساس مورد استفاده قرار می‌گیرد.

Transaction A به شکل زیر شروع می‌شود:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

SELECT *
FROM accounts
WHERE balance > 1000;

در این حالت، پایگاه داده معمولاً قفل‌هایی اعمال می‌کند که مانع از درج یا تغییر داده‌های مؤثر بر این نتیجه می‌شود. اگر Transaction B تلاش کند رکورد جدیدی درج کند:

INSERT INTO accounts (id, balance)
VALUES (3, 7000);

این دستور یا تا پایان Transaction A بلاک می‌شود یا با خطا مواجه خواهد شد (بسته به DBMS). در نتیجه، نه Dirty Read، نه Non-Repeatable Read و نه Phantom Read رخ نخواهد داد. این رفتار بالاترین سطح سازگاری داده را تضمین می‌کند، هرچند به قیمت کاهش همزمانی.

موضوع Snapshot Isolation

در کنار این سطوح، Snapshot Isolation رویکردی متفاوت برای مدیریت همزمانی ارائه می‌دهد. در این مدل، هر تراکنش هنگام شروع، یک «تصویر ثابت» یا Snapshot از وضعیت پایگاه داده دریافت می‌کند و تمامی عملیات خواندن خود را بر اساس همان تصویر انجام می‌دهد. به این ترتیب، داده‌هایی که یک تراکنش می‌خواند در طول عمر آن ثابت باقی می‌مانند، بدون آنکه نیاز به قفل‌گذاری گسترده برای عملیات خواندن وجود داشته باشد. Snapshot Isolation معمولاً مبتنی بر مکانیزم کنترل همزمانی چندنسخه‌ای (MVCC) است و باعث می‌شود خواندن‌ها مسدودکننده‌ی نوشتن‌ها نباشند و بالعکس. در مورد مکانیزم کنترل همزمانی چندنسخه‌ای (MVCC) بیشتر صحبت خواهیم کرد.

Snapshot Isolation از بروز Dirty Read و Non-Repeatable Read جلوگیری می‌کند و در عمل Phantom Read را نیز در بسیاری از سناریوها حذف می‌نماید. با این حال، این سطح به‌طور کامل معادل Serializable نیست و ممکن است ناهنجاری‌هایی مانند Write Skew در آن رخ دهد؛ حالتی که در آن دو تراکنش بر اساس یک Snapshot مشترک تصمیم‌گیری می‌کنند و در نهایت، نتیجه‌ای ناسازگار با منطق کسب‌وکار ایجاد می‌شود. به همین دلیل، Snapshot Isolation را می‌توان سطحی میانی میان Repeatable Read و Serializable دانست که با هدف افزایش کارایی و کاهش قفل‌گذاری طراحی شده است.

در مجموع، Isolation Level‌ها و Snapshot Isolation چارچوب‌های متفاوتی برای مدیریت همزمانی در پایگاه‌های داده فراهم می‌کنند. انتخاب میان این گزینه‌ها باید بر اساس نیازمندی‌های سازگاری، الگوی دسترسی به داده و الزامات کارایی سیستم انجام شود. درک دقیق تفاوت‌های مفهومی و عملی این سطوح، پیش‌نیاز طراحی سیستم‌های پایگاه داده‌ی قابل اعتماد و مقیاس‌پذیر است.