سطوح ایزوله‌سازی، lost update و retry در SERIALIZABLE

دو کاربر، یک موجودی

دو فروشنده هم‌زمان آخرین تخته‌ی فرش ۱۲۰۰ شانه‌ی نقشه‌ی افشان را می‌فروشند. هر دو موجودی را ۱ می‌خوانند، هر دو ۱ کم می‌کنند و ذخیره می‌کنند. نتیجه: موجودی صفر و دو سفارش برای یک فرش. این lost update است و سطح ایزوله‌سازی پیش‌فرض پستگرس جلویش را نمی‌گیرد.

Read Uncommitted در پستگرس وجود ندارد و همان Read Committed رفتار می‌کند؛ dirty read هرگز اتفاق نمی‌افتد.

سه راه درمان lost update

-- ۱) UPDATE اتمی: شرط را در خود UPDATE بگذارید (ساده‌ترین و بهترین)
UPDATE factory.stock
SET qty = qty - 1
WHERE design_code = 'AF-1203' AND reed = 1200 AND qty >= 1
RETURNING qty;
-- اگر صفر ردیف برگشت، موجودی کافی نبوده

-- ۲) قفل بدبینانه: ردیف را برای خواندن-و-نوشتن قفل کنید
BEGIN;
SELECT qty FROM factory.stock
WHERE design_code = 'AF-1203' AND reed = 1200
FOR UPDATE;
-- منطق برنامه ...
UPDATE factory.stock SET qty = qty - 1 WHERE design_code = 'AF-1203' AND reed = 1200;
COMMIT;

-- ۳) SERIALIZABLE و تکرار در صورت شکست
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- خواندن‌ها و نوشتن‌های پیچیده
COMMIT;

retry: بخش اجباری SERIALIZABLE

در Repeatable Read و Serializable، خطای could not serialize access با SQLSTATE 40001 بخشی از کار عادی است، نه باگ. برنامه باید کل تراکنش را از اول تکرار کند، نه فقط دستور آخر را:

import time
import psycopg
from psycopg import errors

def run_serializable(conn, work, attempts=5):
    for i in range(attempts):
        try:
            with conn.transaction():
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                return work(conn)
        except (errors.SerializationFailure, errors.DeadlockDetected):
            time.sleep(0.05 * 2 ** i)   # backoff نمایی
    raise RuntimeError("تراکنش پس از چند تلاش موفق نشد")

تابع work نباید اثر جانبی بیرون از دیتابیس داشته باشد (ارسال پیامک، فراخوانی درگاه پرداخت)؛ چون ممکن است چند بار اجرا شود. اثرهای بیرونی را بعد از COMMIT موفق انجام دهید.

نکته‌هایی که کمتر کسی می‌داند

• در Read Committed، اگر UPDATE به ردیفی برسد که تراکنش دیگری در حال تغییرش است، منتظر می‌ماند و بعد شرط WHERE را روی نسخه‌ی جدید دوباره ارزیابی می‌کند؛ به همین دلیل روش ۱ بدون هیچ قفل صریحی درست کار می‌کند.
• SERIALIZABLE فقط بین تراکنش‌هایی که همه SERIALIZABLE هستند تضمین می‌دهد؛ یک تراکنش Read Committed هم‌زمان می‌تواند ناهنجاری بسازد. یا همه، یا هیچ.
• SET TRANSACTION باید اولین دستور تراکنش باشد؛ بعد از اولین SELECT خطا می‌دهد. برای کل نشست از SET default_transaction_isolation استفاده کنید.
• تراکنش‌های فقط‌خواندنی SERIALIZABLE با READ ONLY DEFERRABLE منتظر یک snapshot امن می‌مانند و هرگز خطای 40001 نمی‌گیرند؛ مناسب گزارش‌های طولانی مالی.
• در Serializable، ایندکس مناسب نرخ خطاهای کاذب را کم می‌کند؛ بدون ایندکس، پستگرس قفل‌های پیش‌بینی (SIRead) را در سطح کل جدول می‌گیرد و تعارض‌های بی‌دلیل زیاد می‌شود.