Що таке узгоджене читання (consistent read) в InnoDB і коли фіксується знімок?

Узгоджене читання - це спосіб, яким InnoDB виконує звичайні SELECT: вони читають знімок бази на певний момент і не ставлять жодних блокувань. Якщо рядок змінила інша транзакція, InnoDB відновлює його попередню версію з undo-журналу.

Тому читачі не чекають на записувачів, а записувачі - на читачів.

Коли фіксується знімок:

• REPEATABLE READ (за замовчуванням): під час першого читання в транзакції, а не на START TRANSACTION. Усі наступні звичайні SELECT транзакції бачать той самий знімок.
• READ COMMITTED: кожен оператор бере новий знімок.

START TRANSACTION;          -- знімка ще немає
-- тут інша сесія комітить зміну
SELECT * FROM t;            -- знімок фіксується зараз: зміну видно

START TRANSACTION WITH CONSISTENT SNAPSHOT;  -- знімок фіксується одразу

WITH CONSISTENT SNAPSHOT використовує, наприклад, mysqldump --single-transaction: дамп усіх таблиць узгоджений на одну мить.

Знімок не діє на зміни даних. Це найчастіша пастка:

START TRANSACTION;
SELECT COUNT(*) FROM jobs WHERE status = 'new';     -- 0 (знімок)
-- інша сесія вставляє і комітить 5 рядків 'new'
UPDATE jobs SET status = 'taken' WHERE status = 'new';  -- оновить 5 рядків!
SELECT COUNT(*) FROM jobs WHERE status = 'taken';   -- 5: свої зміни видно
COMMIT;

UPDATE і DELETE працюють з останніми закоміченими версіями. Після цього транзакція бачить рядки, які вона змінила, - навіть якщо «за знімком» їх не існувало.

Блокувальні читання (SELECT ... FOR UPDATE / FOR SHARE) теж читають останню версію, а не знімок, і ставлять блокування.

Практичні наслідки:

• шаблон «прочитати, перевірити в PHP, записати» без FOR UPDATE дає гонитву: рішення приймається на основі знімка, а запис - поверх свіжих даних;
• довга транзакція тримає старий знімок, і InnoDB не може очистити старі версії рядків (див. purge), тож база росте й сповільнюється;
• DDL на кшталт ALTER TABLE інколи не узгоджується зі знімком: якщо таблицю перестворено після фіксації знімка, транзакція отримає помилку «Table definition has changed».

Докладніше в документації: Узгоджене неблокувальне читання