Коли варто перевести MySQL на READ COMMITTED і що при цьому зміниться?

Багато великих інсталяцій MySQL працюють на READ COMMITTED замість типового REPEATABLE READ. Причина - менше блокувань, а не інша видимість даних.

Що змінюється в блокуваннях:

• немає блокувань проміжків (gap locks) при пошуку й скануванні - лише блокування самих записів. Вставки в діапазони, які читали інші, більше не чекають. Gap locks лишаються тільки для перевірки зовнішніх ключів і дублікатів;
• блокування невідповідних рядків знімаються одразу. UPDATE ... WHERE status = 'new' без індексу на status на REPEATABLE READ тримає блокування на всіх проглянутих рядках до коміту. На READ COMMITTED рядки, що не підійшли під умову, розблоковуються після перевірки;
• напівузгоджене читання для UPDATE: якщо рядок заблоковано, InnoDB читає його останню закомічену версію, щоб перевірити умову WHERE, і чекає лише якщо рядок справді підходить.

Разом це помітно зменшує кількість очікувань і взаємоблокувань при паралельних записах.

Що змінюється в читанні:

• кожен оператор бере свіжий знімок - два однакові SELECT в одній транзакції можуть повернути різне (неповторюване читання й фантоми);
• немає довгоживучих знімків: аналітичні транзакції менше заважають очищенню undo (purge).

Обов'язкова умова - рядковий бінарний журнал. На READ COMMITTED реплікація за операторами (binlog_format=STATEMENT) небезпечна, і MySQL відмовиться писати такі зміни в журнал. З ROW (за замовчуванням) проблем немає.

Як перейти:

SET PERSIST transaction_isolation = 'READ-COMMITTED';

Або точково - для підключення в config/database.php ('isolation_level' => 'READ COMMITTED') чи для окремої транзакції.

Що перевірити в коді перед переходом:

• місця, де логіка покладалася на стабільний знімок у межах транзакції (кілька читань, що мають бути узгоджені між собою, - звіти, перерахунки);
• захист від гонитви має спиратися на FOR UPDATE, унікальні індекси чи атомарні UPDATE ... SET x = x + 1, а не на рівень ізоляції. На обох рівнях шаблон «прочитати без блокування, перевірити, записати» - гонитва.

Додатковий аргумент: PostgreSQL за замовчуванням працює саме на READ COMMITTED, тож застосунок, що підтримує обидві бази, поводитиметься однаково.

Докладніше в документації: Рівні ізоляції транзакцій InnoDB