مقدمه: مشکلات همزمانی (Concurrency Anomalies)
در اجرای همزمان تراکنش‌ها، بدون مکانیزم ایزوله‌سازی مناسب، رفتارهای غیرمنتظره‌ای رخ می‌دهد. مهم‌ترین آن‌ها:
- Dirty Read: خواندن داده‌ای که هنوز Commit نشده و ممکن است Rollback شود.
- Non-Repeatable Read: قرائت مجددِ یک رکورد در همان تراکنش، نتیجه متفاوتی می‌دهد چون تراکنش دیگری آن را تغییر/حذف کرده است.
- Phantom Read: اجرای مجدد همان Query مجموعه نتایج متفاوتی بازمی‌گرداند، زیرا ردیف‌های جدیدی درج شده‌اند که شرط را ارضا می‌کنند.
- Lost Update: دو تراکنش تغییرات یکدیگر را بدون آگاهی Overwrite می‌کنند و بخشی از به‌روزرسانی از دست می‌رود.
سطوح ایزولیشن در SQL Server
1) Read Uncommitted
- کمترین سطح ایزولیشن؛ داده‌های Commit نشده قابل مشاهده‌اند.
- Dirty Read مجاز است؛ Non-Repeatable Read و Phantom Read محتمل‌اند.
- سربار قفل‌گذاری حداقلی و کارایی بالا؛ دقت داده پایین‌تر.
2) Read Committed (پیش‌فرض)
- فقط داده‌های Commit شده قابل خواندن هستند؛ Dirty Read مسدود می‌شود.
- Shared Lock هنگام خواندن کوتاه‌مدت گرفته و آزاد می‌شود.
- Non-Repeatable Read و Phantom Read همچنان محتمل‌اند.
- نکته: با READ_COMMITTED_SNAPSHOT نسخه‌بندی سطر جایگزین Shared Lock می‌شود.
3) Repeatable Read
- جلوگیری از Dirty Read و Non-Repeatable Read.
- Shared Lock روی رکوردهای خوانده‌شده تا پایان تراکنش نگهداری می‌شود.
- Phantom Read ممکن است رخ دهد (افزایش ردیف‌های جدید در محدوده).
4) Serializable
- سخت‌گیرانه‌ترین سطح سنتی: جلوگیری از Dirty/Non-Repeatable/Phantom Read.
- اعمال Key-Range Locks برای مسدودسازی درج/تغییر در محدوده نتایج.
- هزینه همزمانی بالا، افزایش احتمال بن‌بست (Deadlock) و کاهش Throughput.
5) Snapshot
- مبتنی بر Row Versioning در tempdb؛ قرائت‌ها نسخه سازگار در شروع تراکنش را می‌بینند.
- جلوگیری از Dirty/Non-Repeatable/Phantom Read بدون قفل‌گذاری سنگین.
- نیاز به منابع بیشتر برای نگهداری نسخه‌ها و فعال‌سازی در سطح دیتابیس.
مقایسه سطح‌ها و رفتار همزمانی
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | الگوی قفل/نسخه | کارایی نسبی |
|---|---|---|---|---|---|
| Read Uncommitted | مجاز | محتمل | محتمل | قفل حداقلی | بسیار بالا |
| Read Committed | مسدود | محتمل | محتمل | Shared Lock کوتاه‌مدت (یا Versioning با RCSI) | بالا |
| Repeatable Read | مسدود | مسدود | محتمل | نگه‌داشتن Shared Lock تا پایان تراکنش | متوسط |
| Serializable | مسدود | مسدود | مسدود | Key-Range Lock روی محدوده نتایج | پایین |
| Snapshot | مسدود | مسدود | مسدود | Row Versioning در tempdb | بالا (هزینه فضای نسخه‌ها) |
*RCSI: Read Committed Snapshot Isolation
نتیجه‌گیری
انتخاب Isolation Level مبادله‌ای بین صحت داده و کارایی/همزمانی است. سطوح پایین‌تر سرعت بیشتری دارند اما خطاهای همزمانی را می‌پذیرند؛ سطوح بالاتر صحت قوی‌تری تضمین می‌کنند اما هزینه قفل‌گذاری/نسخه‌برداری را افزایش می‌دهند.
با در نظر داشتن تمامی این موارد، باید برای هر تراکنش و متناسب با نیازمندی‌های بیزینس، الگوی ایزولیشن مناسب را اعمال کرد: تراکنش‌های حساس به ناسازگاری داده می‌توانند از Serializable یا Snapshot بهره ببرند، در حالی‌که عملیات پرتعداد و خواندنی معمولاً با Read Committed (یا RCSI) متوازن‌تر هستند. در نهایت، سیاست ایزولیشن باید بر مبنای SLA، پروفایل بار، و حساسیت داده برای هر مسیر بحرانی تنظیم شود.