سطوح ایزوله‌سازی تراکنش (Isolation Levels) در SQL Server

حرف «I» در ACID مخفف Isolation (ایزوله‌سازی) بود، اما نگفتیم دقیقاً «ایزوله بودن» چقدر باید باشد. جواب این است: قابل تنظیم است. SQL Server چند سطح ایزوله‌سازی ارائه می‌دهد که هرکدام، تعادل متفاوتی بین «صحت داده» و «سرعت/همزمانی» برقرار می‌کنند. سطح بالاتر یعنی ایمن‌تر اما کندتر (قفل بیشتر)؛ سطح پایین‌تر یعنی سریع‌تر اما در معرض خطر دیدن داده‌ی نادرست.

سه پدیده‌ی ناخواسته که باید بشناسیم

پدیده	توضیح
Dirty Read	خواندن داده‌ای که تراکنش دیگر هنوز COMMIT نکرده — ممکن است لحظه‌ای بعد ROLLBACK شود و داده‌ی خوانده‌شده اصلاً واقعی نبوده باشد.
Non-Repeatable Read	یک تراکنش، یک ردیف را دوبار می‌خواند و بین این دو بار، مقدار آن توسط تراکنش دیگری تغییر و COMMIT شده — نتیجه‌ی دو خواندن فرق می‌کند.
Phantom Read	یک تراکنش، یک شرط را دوبار با `SELECT ... WHERE` اجرا می‌کند و بین این دو بار، تراکنش دیگری ردیف جدیدی اضافه/حذف کرده که در نتیجه‌ی دوم ظاهر/ناپدید می‌شود.

سطوح ایزوله‌سازی و پدیده‌هایی که اجازه می‌دهند

سطح	Dirty Read	Non-Repeatable Read	Phantom Read	قفل‌گیری
`READ UNCOMMITTED`	ممکن است رخ دهد	ممکن است رخ دهد	ممکن است رخ دهد	کمترین
`READ COMMITTED` (پیش‌فرض SQL Server)	جلوگیری می‌شود	ممکن است رخ دهد	ممکن است رخ دهد	متوسط
`REPEATABLE READ`	جلوگیری می‌شود	جلوگیری می‌شود	ممکن است رخ دهد	بیشتر
`SERIALIZABLE`	جلوگیری می‌شود	جلوگیری می‌شود	جلوگیری می‌شود	بیشترین
`SNAPSHOT`	جلوگیری می‌شود	جلوگیری می‌شود	جلوگیری می‌شود	بدون قفل خواندن (از Row Versioning استفاده می‌کند)

در T-SQL، سطح ایزوله‌سازی یک Session با دستور SET TRANSACTION ISOLATION LEVEL READ COMMITTED; (یا هر سطح دیگر) قبل از BEGIN TRANSACTION تنظیم می‌شود.

مثال بصری: چرا READ COMMITTED برای Non-Repeatable Read کافی نیست

Session A (سطح READ COMMITTED)

BEGIN TRANSACTION;

SELECT balance FROM accounts WHERE id=1; → 500000

-- کمی صبر می‌کند...

SELECT balance FROM accounts WHERE id=1; → 350000 !

COMMIT;

Session B

UPDATE accounts SET balance=350000 WHERE id=1;

COMMIT;

-- Session B زودتر کارش را تمام کرد

چون Session B بین دو SELECT در Session A مقدار را تغییر داده و COMMIT کرده، Session A دو نتیجه‌ی متفاوت برای یک ردیف واحد می‌بیند — این همان Non-Repeatable Read است. اگر Session A از سطح REPEATABLE READ یا بالاتر استفاده می‌کرد، ردیف id=1 تا پایان تراکنش A قفل می‌ماند و Session B مجبور به انتظار می‌شد.

SNAPSHOT: راه‌حل مدرن‌تر

سطح SNAPSHOT (و نسخه‌ی سبک‌تر آن READ COMMITTED SNAPSHOT) به‌جای قفل‌گذاری روی داده‌های در حال خواندن، از تکنیک Row Versioning استفاده می‌کند: هر تراکنش، یک «عکس لحظه‌ای» (Snapshot) از داده در لحظه‌ی شروع تراکنش می‌بیند، بدون این‌که مانع نوشتن دیگران شود و بدون این‌که منتظر نوشتن دیگران بماند. این سطح، Dirty/Non-Repeatable/Phantom Read را بدون قفل سنگین حذف می‌کند — قیمتش، مصرف بیشتر tempdb برای نگه‌داشتن نسخه‌های قدیمی ردیف‌هاست.

SQLite (که در Playground این سایت اجرا می‌شود) هم مدلی شبیه به Snapshot Isolation در حالت WAL دارد، اما چون همه‌چیز در همین صفحه و در یک اتصال واحد اجرا می‌شود، تفاوت سطوح ایزوله‌سازی را نمی‌توان این‌جا به‌صورت زنده نشان داد — دقیقاً به همین دلیل، این درس و دو درس قبلی (قفل‌گذاری و Blocking) به‌صورت مفهومی و با نمودار روایت شدند، نه با جعبه‌ی اجرای زنده.

جمع‌بندی فصل ۹

سطح ایزوله‌سازی، تعادل بین صحت داده و سرعت/همزمانی را کنترل می‌کند.
READ COMMITTED پیش‌فرض SQL Server است و فقط جلوی Dirty Read را می‌گیرد.
REPEATABLE READ و SERIALIZABLE ایمن‌ترند اما قفل بیشتری می‌گیرند و شانس Blocking را بالا می‌برند.
SNAPSHOT با Row Versioning، ایمنی بالا را بدون قفل سنگین خواندن فراهم می‌کند.
در این فصل با تراکنش، ACID، قفل‌گذاری، Blocking و سطوح ایزوله‌سازی آشنا شدیم — پایه‌ای که هر برنامه‌نویس SQL حرفه‌ای باید آن را بشناسد، حتی اگر مستقیماً کدنویسی نکند.

فصل بعدی سراغ ایندکس‌گذاری و کارایی (Indexing & Performance) می‌رود — جایی که یاد می‌گیریم موتور پایگاه‌داده چطور پرس‌وجوها را واقعاً اجرا می‌کند و چطور آن را سریع‌تر کنیم.

سطوح ایزوله‌سازی تراکنش (Isolation Levels)

سه پدیده‌ی ناخواسته که باید بشناسیم

سطوح ایزوله‌سازی و پدیده‌هایی که اجازه می‌دهند

مثال بصری: چرا READ COMMITTED برای Non-Repeatable Read کافی نیست

SNAPSHOT: راه‌حل مدرن‌تر

جمع‌بندی فصل ۹

تمرین‌های خودآزمایی