MVCC و سطوح ایزوله‌سازی: چرا REPEATABLE READ پیش‌فرض است

خواننده‌ها منتظر نویسنده‌ها نمی‌مانند

InnoDB از MVCC (کنترل هم‌زمانی چندنسخه‌ای) استفاده می‌کند: وقتی ردیفی تغییر می‌کند، نسخه‌ی قبلی در undo log نگه داشته می‌شود. هر تراکنشی که در حال خواندن است یک «عکس فوری» (snapshot) دارد و نسخه‌ای را می‌بیند که در لحظه‌ی عکس معتبر بوده. در نتیجه SELECT معمولی هیچ قفلی نمی‌گیرد و منتظر UPDATEهای هم‌زمان نمی‌ماند.

چهار سطح ایزوله‌سازی

سطح	Dirty read	Non-repeatable read	Phantom	کاربرد
READ UNCOMMITTED	ممکن	ممکن	ممکن	تقریباً هرگز
READ COMMITTED	خیر	ممکن	ممکن	پیش‌فرض PostgreSQL و جنگو روی MySQL
REPEATABLE READ	خیر	خیر	در خواندن معمولی خیر	پیش‌فرض MySQL
SERIALIZABLE	خیر	خیر	خیر	موارد خاص؛ همه‌ی SELECTها قفل اشتراکی می‌گیرند

در READ COMMITTED هر دستور SELECT عکس فوری تازه می‌گیرد؛ در REPEATABLE READ کل تراکنش با یک عکس کار می‌کند، پس گزارشی که چند SELECT پشت‌سرهم دارد اعداد سازگار می‌بیند.

آزمایش با دو پنجره

-- نشست A                                   -- نشست B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT stock FROM products WHERE id = 7;     -- 5
                                              UPDATE products SET stock = 3 WHERE id = 7;  -- autocommit
SELECT stock FROM products WHERE id = 7;     -- هنوز 5 (همان snapshot)
UPDATE products SET stock = stock - 1 WHERE id = 7;
SELECT stock FROM products WHERE id = 7;     -- 2 !
COMMIT;

نتیجه‌ی آخر غافلگیرکننده است: SELECT معمولی «خواندن سازگار» (consistent read) از snapshot است، اما UPDATE یک «خواندن جاری» (current read) است و همیشه آخرین نسخه‌ی COMMIT‌شده را می‌بیند (3)، یک واحد کم می‌کند و از آن به بعد تراکنش تغییر خودش را می‌بیند. به همین دلیل منطق «اول SELECT کن، در برنامه حساب کن، بعد مقدار ثابت را UPDATE کن» زیر بار هم‌زمان غلط است؛ یا محاسبه را در خود UPDATE انجام دهید (stock = stock - 1) یا با SELECT ... FOR UPDATE ردیف را قفل کنید.

تنظیم سطح

SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- فقط این نشست
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;             -- فقط تراکنش بعدی

[mysqld]
transaction_isolation = READ-COMMITTED

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

snapshot در REPEATABLE READ هنگام START TRANSACTION گرفته نمی‌شود، بلکه هنگام اولین SELECT؛ اگر همان لحظه لازم است: START TRANSACTION WITH CONSISTENT SNAPSHOT. mysqldump با --single-transaction دقیقاً از همین استفاده می‌کند.
تراکنش خواندنی که ساعت‌ها باز بماند، مانع پاک‌سازی (purge) نسخه‌های قدیمی می‌شود؛ مقدار History list length در SHOW ENGINE INNODB STATUS رشد می‌کند و کل سرور کند می‌شود.
متغیر قدیمی tx_isolation در MySQL 8 حذف شده و نامش transaction_isolation است؛ کتابخانه‌ها و ORMهای قدیمی به همین دلیل خطا می‌دهند.
جنگو از نسخه‌ی 2.0 به بعد روی MySQL به‌طور پیش‌فرض READ COMMITTED تنظیم می‌کند، نه REPEATABLE READ پیش‌فرض سرور؛ رفتار برنامه و کلاینت دستی شما ممکن است متفاوت باشد.
READ COMMITTED تعداد gap lockها را به‌شدت کم می‌کند و در سیستم‌های پرنوشتن deadlockها را کاهش می‌دهد؛ اما با binlog مبتنی بر statement سازگار نیست و binlog_format باید ROW باشد (که پیش‌فرض است).

برای ذخیره‌ی پیشرفت و شرکت در آزمون، وارد شوید — رایگان است.