‏Repeatable Read واقعاً Repeatable Read نیست

سؤالی که نویسنده تو این یادداشت مطرح می‌کنه ساده‌ست: وقتی تو MySQL سطح ایزوله‌سازی Repeatable Read رو انتخاب می‌کنی، واقعاً همون رو می‌گیری یا در عمل Snapshot Isolation تحویلت می‌دن؟ و اصلاً چرا این فرق مهمه؟

استاندارد SQL-92 چهار سطح تعریف کرده و ملاکش هم محافظت در برابر سه آنومالیه: dirty read (تراکنش اول داده‌ای رو می‌بینه که تراکنش دوم هنوز commit نکرده)، inconsistent read (سطر رو دو بار می‌خونی و مقدار عوض شده) و phantom (دفعهٔ دوم که با همون شرط SELECT می‌زنی، مجموعهٔ سطرها فرق کرده). در تئوری هم هر سطح با ترکیبی از قفل‌های کوتاه و بلند روی read set و write set و قفل‌های ضدفانتوم پیاده می‌شد.

اما به گفتهٔ نویسنده، دیتابیس‌های امروزی نه دقیقاً این معناشناسی رو می‌دن و نه با همون قفل‌های ساده پیاده‌ش کردن. سیستم‌های MVCC که می‌تونن روی یه timestamp مشخص بخونن، ابزار راحت‌تری‌ان: با قفل کمتر، کارایی و همزمانی بیشتر. PostgreSQL حتی تو سطح پیش‌فرض Read Committed هم داخلاً از snapshot استفاده می‌کنه، و MySQL هم تو Repeatable Read یه اسنپ‌شات از اولین SELECT تراکنش می‌گیره و بقیهٔ SELECTها رو با همون می‌خونه.

نویسنده به دو مقالهٔ کلاسیک ارجاع می‌ده: نقد Berenson و Bernstein و Gray بر سطوح ایزوله‌سازی ANSI (SIGMOD 1995) که نشون می‌ده استاندارد آنومالی write skew رو اصلاً پوشش نمی‌ده، و مقالهٔ Adya و Liskov (ICDE 2000) که تعریف‌های عمومی‌تر و انعطاف‌پذیرتری ارائه می‌کنه. جمع‌بندی‌اش از ادبیات موضوع اینه: SI و RR هر دو قوی‌تر از Read Committed و ضعیف‌تر از Serializable‌ان، ولی SI اجازهٔ write skew می‌ده و جلوی فانتوم رو می‌گیره، در حالی که RR برعکسه.

آزمایش اصلی مقاله همون مثال کلاسیک دو پزشک on-call‌ه: جدولی با Andy و Brad که هر دو on-call هستن، و هر کدوم تو تراکنش خودش چک می‌کنه که «یکی دیگه هست؟» و بعد خودش رو از شیفت درمی‌آره.

-- T1 (Andy)
BEGIN;
SELECT name FROM doctor WHERE oncall=TRUE AND name<>'Andy';  -- Brad
-- T2 (Brad)
BEGIN;
SELECT name FROM doctor WHERE oncall=TRUE AND name<>'Brad';  -- Andy
UPDATE doctor SET oncall=FALSE WHERE name='Brad'; COMMIT;
-- back in T1
UPDATE doctor SET oncall=FALSE WHERE name='Andy'; COMMIT;

هر دو تراکنش از روی یه read view اسنپ‌شات‌شده کار می‌کنن و نوشتهٔ هرکدوم به‌تنهایی معتبره، ولی همپوشانی‌شون غیرسریالایزبله و نتیجهٔ نهایی فاجعه‌ست: هیچ‌کس on-call نیست. این دقیقاً write skew‌ه، تو هر دوی MySQL و PostgreSQL زیر RR اتفاق می‌افته، و محافظت از فانتوم هم سرجاش هست — یعنی رفتار عملاً SI‌ه نه RR. نویسنده حدس می‌زنه توسعه‌دهنده‌ها دلشون نمی‌خواسته اسماً از استاندارد ANSI فاصله بگیرن.

پیچ داستان اما اینه که RR در MySQL خالص SI هم نیست: آنومالی lost update که نباید نه تو RR و نه تو SI رخ بده، اونجا رخ می‌ده. همون تست تو PostgreSQL زیر RR سالمه (و فقط تو RC اتفاق می‌افته) که با ادبیات موضوع سازگارتره.

-- T1: SELECT cash FROM account WHERE id=1;  -- 100
-- T2: SELECT cash FROM account WHERE id=1;  -- 100
UPDATE account SET cash=130 WHERE id=1; COMMIT;  -- T2
UPDATE account SET cash=120 WHERE id=1; COMMIT;  -- T1 → واریز ۳۰ گم شد

توصیهٔ عملی نویسنده اینه که هرچند Serializable جلوی این مشکل رو می‌گرفت، اگه چنین الگوی تراکنشی دارید بهتره نیت خودتون رو به دیتابیس اعلام کنید و از SELECT ... FOR UPDATE استفاده کنید. نتیجه‌گیری کلی‌اش هم اینه که آدم‌ها اصطلاح‌ها رو یکسان تعریف نمی‌کنن و عمل معمولاً از تئوری فاصله داره — به‌خصوص تو دنیای سطوح ایزوله‌سازی.

نکات کلیدی:

• تفاوت کلیدی: SI اجازهٔ write skew می‌ده ولی فانتوم رو می‌بنده؛ RR برعکسه
• در MySQL و PostgreSQL، سطح Repeatable Read در عمل write skew رو اجازه می‌ده و بیشتر شبیه SI رفتار می‌کنه
• مثال دو پزشک on-call نشون می‌ده دو نوشتهٔ جداگانهٔ معتبر می‌تونن ترکیب فاجعه‌بار بسازن
• در MySQL آنومالی lost update حتی زیر RR رخ می‌ده؛ در PostgreSQL فقط زیر Read Committed
• راه‌حل عملی برای این الگو: SELECT ... FOR UPDATE یا سطح Serializable
• نسخه‌های جدید PostgreSQL نسخهٔ بهبودیافته‌ای به اسم Serializable Snapshot Isolation (SSI) دارن