پیاده‌سازی ایزولیشن در SQL Server

سطوح مختلف ایزولیشن در SQL Server

SQL Server پنج سطح ایزولیشن را تعریف می‌کند که هر کدام به میزان متفاوتی از قرائت‌پذیری داده‌ها و همزمانی تراکنش‌ها را تضمین می‌کنند:

1. READ UNCOMMITTED:

   • READ UNCOMMITTED یکی از سطوح جداسازی (Isolation Level) در SQL Server است. این سطح کمترین میزان جداسازی را ارائه می‌دهد و به عبارت ساده‌تر، به پرس و جو اجازه می‌دهد تا داده‌هایی را بخواند که هنوز توسط سایر تراکنش‌ها به طور رسمی ثبت نشده‌اند (committed).

     چه معنایی دارد؟

     • داده‌های ناکامل: پرس و جوهایی که با سطح جداسازی READ UNCOMMITTED اجرا می‌شوند، ممکن است داده‌هایی را مشاهده کنند که در حال تغییر هستند یا هنوز به طور کامل ثبت نشده‌اند. این به آن معناست که ممکن است داده‌های ناسازگار یا ناقصی را مشاهده کنند.
     • خواندن داده‌های حذف نشده: حتی اگر یک سطر توسط تراکنش دیگری حذف شده باشد، اما هنوز commit نشده باشد، پرس و جوهای READ UNCOMMITTED ممکن است آن سطر را مشاهده کنند.
     • خواندن داده‌های در حال ویرایش: اگر یک تراکنش در حال ویرایش یک سطر باشد، اما هنوز commit نشده باشد، پرس و جوهای READ UNCOMMITTED ممکن است هم مقدار قدیمی و هم مقدار جدید را مشاهده کنند.

     چرا از READ UNCOMMITTED استفاده کنیم؟

     • عملکرد بهتر: معمولاً پرس و جوهایی که با سطح جداسازی READ UNCOMMITTED اجرا می‌شوند، سریع‌تر از پرس و جوهایی هستند که با سطوح جداسازی بالاتر اجرا می‌شوند. زیرا SQL Server مجبور نیست برای اطمینان از سازگاری داده‌ها، قفل‌های زیادی را مدیریت کند.
     • خواندن داده‌های موقت: در برخی موارد، ممکن است بخواهیم داده‌هایی را بخوانیم که هنوز به طور رسمی ثبت نشده‌اند، مانند داده‌هایی که در یک تراکنش موقت ایجاد شده‌اند.

     چه زمانی از READ UNCOMMITTED استفاده نکنیم؟

     • داده‌های حساس: اگر داده‌هایی که می‌خواهید بخوانید حساس هستند و باید دقیق و سازگار باشند، از READ UNCOMMITTED استفاده نکنید. زیرا ممکن است داده‌های نادرستی را مشاهده کنید.
     • تراکنش‌های طولانی مدت: اگر تراکنش‌های طولانی مدتی دارید، استفاده از READ UNCOMMITTED می‌تواند منجر به مشکلات جدی شود. زیرا ممکن است داده‌هایی را مشاهده کنید که در حال تغییر هستند و منجر به نتایج غیرمنتظره شود.

2. READ COMMITTED:

   • READ COMMITTED یکی از سطوح جداسازی (Isolation Level) در SQL Server است که تضمین می‌کند یک پرس و جو فقط داده‌هایی را می‌خواند که توسط سایر تراکنش‌ها به طور رسمی ثبت (committed) شده‌اند. این سطح جداسازی، در مقایسه با READ UNCOMMITTED، پیشرفت قابل توجهی در جلوگیری از خواندن داده‌های ناکامل یا نادرست را ارائه می‌دهد.

     چگونه کار می‌کند؟

     • خواندن داده‌های ثبت شده: وقتی یک پرس و جو با سطح جداسازی READ COMMITTED اجرا می‌شود، فقط داده‌هایی را مشاهده می‌کند که قبلاً توسط سایر تراکنش‌ها به طور کامل ثبت شده‌اند. این به معنای آن است که پرس و جو نمی‌تواند داده‌هایی را بخواند که هنوز در حال تغییر هستند یا هنوز commit نشده‌اند.
     • جلوگیری از خواندن داده‌های ناکامل: با جلوگیری از خواندن داده‌های ناکامل، READ COMMITTED احتمال بروز ناسازگاری داده‌ها را کاهش می‌دهد.
     • جلوگیری از خواندن داده‌های حذف نشده: حتی اگر یک سطر توسط تراکنش دیگری حذف شده باشد، اما هنوز commit نشده باشد، پرس و جوهای READ COMMITTED آن سطر را مشاهده نخواهند کرد.

     چرا از READ COMMITTED استفاده کنیم؟

     • داده‌های سازگار: READ COMMITTED تضمین می‌کند که پرس و جوها فقط داده‌های سازگار را مشاهده می‌کنند. این به معنای آن است که احتمال بروز خطاهای داده‌ای کاهش می‌یابد.
     • جلوگیری از خواندن داده‌های نادرست: با جلوگیری از خواندن داده‌های ناکامل، READ COMMITTED احتمال خواندن داده‌های نادرست را کاهش می‌دهد.
     • عملکرد بهتر از READ UNCOMMITTED: در بسیاری از موارد، READ COMMITTED عملکرد بهتری نسبت به READ UNCOMMITTED دارد، زیرا SQL Server مجبور نیست برای اطمینان از سازگاری داده‌ها، قفل‌های زیادی را مدیریت کند.

     چه زمانی از READ COMMITTED استفاده نکنیم؟

     • عملکرد: اگر عملکرد بسیار مهم است و نیاز به خواندن سریع داده‌ها دارید، ممکن است بخواهید از READ UNCOMMITTED استفاده کنید، زیرا معمولاً سریع‌تر است.
     • تراکنش‌های طولانی مدت: اگر تراکنش‌های طولانی مدتی دارید، استفاده از READ COMMITTED ممکن است منجر به مشکلات قفل‌سازی شود.

3. REPEATABLE READ:

   • REPEATABLE READ یکی از سطوح جداسازی (Isolation Level) در SQL Server است که تضمین می‌کند یک پرس و جو، در طول اجرای خود، همیشه همان داده‌ها را می‌خواند. این سطح جداسازی، در مقایسه با READ COMMITTED، پیشرفت قابل توجهی در جلوگیری از خواندن داده‌های ناکامل یا نادرست را ارائه می‌دهد.

     چگونه کار می‌کند؟

     • خواندن ثابت داده‌ها: وقتی یک پرس و جو با سطح جداسازی REPEATABLE READ اجرا می‌شود، SQL Server اطمینان حاصل می‌کند که پرس و جو همیشه همان داده‌هایی را می‌خواند که در ابتدای اجرای پرس و جو وجود داشتند. این به معنای آن است که اگر تراکنش دیگری در طول اجرای پرس و جو داده‌ها را تغییر دهد، پرس و جو همچنان داده‌های قدیمی را مشاهده خواهد کرد.
     • جلوگیری از خواندن داده‌های حذف شده: حتی اگر یک سطر توسط تراکنش دیگری حذف شده باشد، اما هنوز commit نشده باشد، پرس و جوهای REPEATABLE READ آن سطر را مشاهده نخواهند کرد.

     چرا از REPEATABLE READ استفاده کنیم؟

     • خواندن ثابت داده‌ها: REPEATABLE READ تضمین می‌کند که پرس و جوها همیشه همان داده‌ها را می‌خوانند، حتی اگر تراکنش‌های دیگری در حال تغییر داده‌ها باشند. این به معنای آن است که احتمال بروز نتایج نادرست کاهش می‌یابد.
     • جلوگیری از خواندن داده‌های نادرست: با جلوگیری از خواندن داده‌های ناکامل، REPEATABLE READ احتمال خواندن داده‌های نادرست را کاهش می‌دهد.

     چه زمانی از REPEATABLE READ استفاده نکنیم؟

     • عملکرد: REPEATABLE READ ممکن است عملکرد را کاهش دهد، زیرا SQL Server مجبور است قفل‌های بیشتری را مدیریت کند تا اطمینان حاصل کند که پرس و جوها همیشه همان داده‌ها را می‌خوانند.
     • تراکنش‌های طولانی مدت: اگر تراکنش‌های طولانی مدتی دارید، استفاده از REPEATABLE READ ممکن است منجر به مشکلات قفل‌سازی شود.

4. SERIALIZABLE

   :

   سطح جداسازی SERIALIZABLE بالاترین سطح جداسازی در SQL Server است. این سطح تضمین می‌کند که تراکنش‌ها به صورت سریالی (یکی پس از دیگری) اجرا شوند، انگار که هیچ تراکنش همزمانی وجود ندارد. این باعث می‌شود که نتایج حاصل از هر تراکنش دقیقاً همانند نتایجی باشد که اگر تراکنش‌ها به صورت ترتیبی اجرا می‌شدند، حاصل می‌شد.

   چرا از SERIALIZABLE استفاده کنیم؟

   • دقت بالا: برای تراکنش‌هایی که نیاز به دقت بسیار بالا دارند و نباید هیچگونه ناسازگاری داده‌ای در آن‌ها رخ دهد، SERIALIZABLE بهترین گزینه است.
   • جلوگیری از مشکلات پیچیده همزمانی: در محیط‌هایی با تراکنش‌های پیچیده و همزمان زیاد، SERIALIZABLE می‌تواند به جلوگیری از مشکلات ناشی از همزمانی مانند Deadlock و Phantom Reads کمک کند.

   نحوه پیاده‌سازی

   برای تنظیم سطح جداسازی یک تراکنش به SERIALIZABLE در SQL Server، از دستور زیر استفاده می‌شود:

   SQL

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   

   این دستور باید قبل از شروع تراکنش اجرا شود.

   مثال:

   SQL

   BEGIN TRANSACTION;
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   
   -- دستورات تراکنش شما در اینجا قرار می‌گیرند
   
   COMMIT TRANSACTION;
   

   محدودیت‌ها و ملاحظات

   • کاهش عملکرد: استفاده از SERIALIZABLE می‌تواند به طور قابل توجهی عملکرد پایگاه داده را کاهش دهد، زیرا SQL Server مجبور است قفل‌های بیشتری را مدیریت کند و تراکنش‌ها را به صورت سریالی اجرا کند.
   • قفل‌شدگی (Deadlock): در برخی موارد، استفاده از SERIALIZABLE می‌تواند احتمال وقوع Deadlock را افزایش دهد.
   • هزینه محاسباتی: اجرای تراکنش‌ها به صورت سریالی می‌تواند هزینه محاسباتی بالایی داشته باشد.

   چه زمانی از SERIALIZABLE استفاده کنیم؟

   • تراکنش‌های مالی: در سیستم‌های مالی که دقت بسیار بالا اهمیت دارد، مانند سیستم‌های بانکی.
   • سیستم‌های کنترل موجودی: در سیستم‌هایی که مدیریت موجودی کالاها به صورت دقیق انجام می‌شود.
   • سیستم‌های رزرواسیون: در سیستم‌هایی که مدیریت رزرواسیون‌ها به صورت همزمان انجام می‌شود.

   جایگزین‌های SERIALIZABLE

   اگر به سطح جداسازی بسیار بالا نیاز ندارید، می‌توانید از سطوح جداسازی پایین‌تری مانند REPEATABLE READ یا READ COMMITTED استفاده کنید که عملکرد بهتری دارند.

   نتیجه‌گیری

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

   نکات مهم:

   • تست عملکرد: قبل از استفاده از SERIALIZABLE در محیط تولید، آن را در محیط تست به دقت بررسی کنید تا تأثیر آن بر عملکرد را ارزیابی کنید.
   • بررسی نیازمندی‌ها: اطمینان حاصل کنید که به سطح جداسازی SERIALIZABLE نیاز دارید و سطح جداسازی پایین‌تری نمی‌تواند نیازهای شما را برطرف کند.
   • مدیریت قفل‌ها: اگر با مشکل قفل‌شدگی مواجه شدید، می‌توانید از تکنیک‌های مدیریت قفل برای کاهش این مشکل استفاده کنید.

    

5. SNAPSHOT ISOLATION:

   • • پیاده‌سازی SNAPSHOT ISOLATION در SQL Server

       SNAPSHOT ISOLATION یک سطح جداسازی تراکنش در SQL Server است که به تراکنش‌ها اجازه می‌دهد تا یک نسخه ثابت از داده‌ها را در زمان شروع تراکنش مشاهده کنند. این سطح جداسازی می‌تواند به کاهش مشکلات همزمانی مانند Deadlock و Phantom Reads کمک کند.

       نحوه پیاده‌سازی

       برای تنظیم سطح جداسازی یک تراکنش به SNAPSHOT ISOLATION در SQL Server، از دستور زیر استفاده می‌شود:

       SQL

       SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
       

       این دستور باید قبل از شروع تراکنش اجرا شود.

       مثال:

       SQL

       BEGIN TRANSACTION;
       SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
       
       -- دستورات تراکنش شما در اینجا قرار می‌گیرند
       
       COMMIT TRANSACTION;
       

       مزایای SNAPSHOT ISOLATION

       • کاهش Deadlock: SNAPSHOT ISOLATION می‌تواند به کاهش احتمال وقوع Deadlock کمک کند، زیرا تراکنش‌ها از نسخه‌های جداگانه داده‌ها استفاده می‌کنند و احتمال برخورد قفل‌ها را کاهش می‌دهند.
       • جلوگیری از Phantom Reads: SNAPSHOT ISOLATION از Phantom Reads جلوگیری می‌کند، که زمانی رخ می‌دهد که یک تراکنش سطرهایی را می‌بیند که در طول اجرای تراکنش حذف یا اضافه شده‌اند.
       • عملکرد بهتر: در برخی موارد، SNAPSHOT ISOLATION می‌تواند عملکرد بهتری نسبت به سطوح جداسازی بالاتر مانند SERIALIZABLE داشته باشد، زیرا قفل‌های کمتری را نیاز دارد.

       محدودیت‌ها و ملاحظات

       • هزینه محاسباتی: SNAPSHOT ISOLATION نیاز به نگهداری نسخه‌های جداگانه داده‌ها دارد، که می‌تواند هزینه محاسباتی بالایی داشته باشد.
       • عدم سازگاری با برخی ویژگی‌ها: برخی ویژگی‌های SQL Server، مانند TRIGGERها و CURSORها، ممکن است با SNAPSHOT ISOLATION سازگار نباشند.
       • محدودیت در خواندن داده‌های جدید: تراکنش‌هایی که با SNAPSHOT ISOLATION اجرا می‌شوند، ممکن است نتوانند داده‌های جدیدی را که بعد از شروع تراکنش اضافه شده‌اند، مشاهده کنند.

       چه زمانی از SNAPSHOT ISOLATION استفاده کنیم؟

       • تراکنش‌های طولانی مدت: اگر تراکنش‌های شما طولانی مدت هستند و احتمال وقوع Deadlock یا Phantom Reads بالا است، SNAPSHOT ISOLATION می‌تواند مفید باشد.
       • سیستم‌های با بار کاری بالا: اگر سیستم شما بار کاری بالایی دارد و نیاز به بهبود عملکرد دارید، SNAPSHOT ISOLATION می‌تواند یک گزینه مناسب باشد.

       جایگزین‌های SNAPSHOT ISOLATION

       اگر نیاز به سطح جداسازی بسیار بالا ندارید، می‌توانید از سطوح جداسازی پایین‌تری مانند REPEATABLE READ یا READ COMMITTED استفاده کنید که عملکرد بهتری دارند.

       نتیجه‌گیری

       SNAPSHOT ISOLATION یک سطح جداسازی قوی است که می‌تواند به بهبود عملکرد و کاهش مشکلات همزمانی کمک کند. با این حال، قبل از استفاده از آن، باید مزایا و معایب آن را به دقت در نظر بگیرید و بررسی کنید که آیا برای نیازهای شما مناسب است یا خیر.

       نکات مهم:

       • تست عملکرد: قبل از استفاده از SNAPSHOT ISOLATION در محیط تولید، آن را در محیط تست به دقت بررسی کنید تا تأثیر آن بر عملکرد را ارزیابی کنید.
       • بررسی نیازمندی‌ها: اطمینان حاصل کنید که به سطح جداسازی SNAPSHOT ISOLATION نیاز دارید و سطح جداسازی پایین‌تری نمی‌تواند نیازهای شما را برطرف کند.
       • مدیریت نسخه‌های داده: اگر از SNAPSHOT ISOLATION استفاده می‌کنید، باید مدیریت نسخه‌های داده را به درستی انجام دهید تا از عملکرد بهینه استفاده کنید.

        

انتخاب سطح ایزولیشن مناسب

انتخاب سطح ایزولیشن مناسب به عوامل مختلفی بستگی دارد، از جمله:

• نوع تراکنش‌ها: برای تراکنش‌های کوتاه و ساده، سطح ایزولیشن پایین‌تر مانند READ COMMITTED ممکن است کافی باشد.
• میزان همزمانی: در محیط‌های با همزمانی بالا، سطح ایزولیشن بالاتر ممکن است باعث کاهش کارایی شود.
• نیاز به قرائت‌پذیری داده‌ها: اگر قرائت‌پذیری داده‌ها از اهمیت بالایی برخوردار باشد، باید از سطوح ایزولیشن بالاتر استفاده شود.

راهنمای انتخاب سطح ایزولیشن

تنظیم سطح ایزولیشن در SQL Server

جدول مقایسه سطوح ایزولیشن

نکات مهم

• سطح ایزولیشن در سطح جلسه (session) تنظیم می‌شود.
• انتخاب سطح ایزولیشن نامناسب می‌تواند منجر به مشکلات عملکردی و داده‌ای شود.
• برای سیستم‌های با حجم بالای تراکنش، استفاده از سطح ایزولیشن پایین‌تر مانند READ COMMITTED یا SNAPSHOT ISOLATION می‌تواند کارایی را بهبود بخشد.