Senior: питання на співбесіді з теми «InnoDB і транзакції»

InnoDB змінює сторінки даних у буферному пулі, а на диск скидає їх пізніше. Щоб закомічені зміни не пропали при збої, працює журнал повтору (redo log): перед комітом опис змін послідовно записується в журнал. Послідовний запис невеликих записів значно дешевший за запис випадкових сторінок по 16 КБ.

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

innodb_flush_log_at_trx_commit визначає, що відбувається під час COMMIT:

fsync - найдорожча частина коміту, тож 2 і 0 дають помітний виграш на дрібних транзакціях. Але це свідома відмова від стійкості. Прийнятно для реплік, що легко перестворити, чи для тимчасового масового імпорту - не для основної бази з грошима.

Друга половина стійкості - sync_binlog (за замовчуванням 1): fsync бінарного журналу на кожен коміт. Без нього після збою репліки можуть отримати транзакції, яких немає на джерелі, чи навпаки.

Подвійний запис (doublewrite buffer) захищає від «розірваних» сторінок: якщо живлення зникло посеред запису 16-КБ сторінки, на диску лишиться напівзаписана сторінка, яку журнал повтору не виправить. Тому InnoDB спершу пише сторінки в окрему область doublewrite, а потім на місце. Після збою пошкоджена сторінка відновлюється з копії.

Розмір журналу повтору (innodb_redo_log_capacity з MySQL 8.0.30, за замовчуванням 100 МБ). Замалий журнал змушує часто робити контрольні точки й агресивно скидати сторінки - запис стає ривками. Завеликий подовжує відновлення після збою. Орієнтир - щоб журнал вміщав щонайменше годину запису під піковим навантаженням.

Що варто знати: групування комітів (group commit) дає змогу кільком транзакціям поділити один fsync, тож налаштування 1 під паралельним навантаженням коштує менше, ніж здається на синтетичному тесті з одним клієнтом.

Докладніше в документації: Журнал повтору (redo log)

Undo-журнал зберігає попередні версії рядків. Коли транзакція змінює рядок, стара версія йде в undo. Це потрібно для двох речей: відкоту транзакції і узгодженого читання - інші транзакції відновлюють зі undo версію, яку вони мають бачити за своїм знімком.

Purge - фонова очистка: коли жодна активна транзакція вже не може побачити стару версію, purge-потоки видаляють її з undo і остаточно прибирають рядки, позначені як видалені (DELETE в InnoDB лише позначає рядок).

Довжина історії (history list length) - кількість ще не очищених записів undo:

SHOW ENGINE INNODB STATUS\G
-- TRANSACTIONS: History list length 1234567

SELECT count FROM information_schema.innodb_metrics
WHERE name = 'trx_rseg_history_len';

Як одна транзакція шкодить усім. Purge не може видалити жодну версію, новішу за найстаріший відкритий знімок. Якщо транзакцію відкрито годину тому (навіть тільки для читання на REPEATABLE READ), за годину нагромаджуються всі старі версії всіх змінених рядків:

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

Типові винуватці:

• забутий START TRANSACTION у консолі чи GUI;
• mysqldump --single-transaction великої бази на основному сервері;
• довгий звіт у транзакції;
• воркер, що відкрив транзакцію й чекає на зовнішній сервіс.

Як знайти:

SELECT trx_id, trx_mysql_thread_id, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;

Що робити:

• моніторити довжину історії й вік найстарішої транзакції, ставити сповіщення;
• великі звіти й дампи виконувати на репліці;
• для аналітичних читань, яким не потрібен один знімок на кілька запитів, - READ COMMITTED: знімок береться на кожен оператор і не тримається довго;
• max_execution_time для SELECT і тайм-аут неактивних сесій.

Розмір undo на диску: undo-табличні простори автоматично усікаються (innodb_undo_log_truncate = ON), коли перевищують innodb_max_undo_log_size (1 ГБ) і purge їх звільнив. Але доки довга транзакція жива, усікання неможливе.

Докладніше в документації: Налаштування purge

ALTER TABLE у InnoDB може виконуватися трьома алгоритмами, і від алгоритму залежить, чи буде простій.

ALGORITHM=INSTANT - змінюються лише метадані, дані не чіпаються. Мілісекунди незалежно від розміру таблиці:

• додавання колонки (з MySQL 8.0.29 - у будь-яку позицію, раніше - лише в кінець);
• видалення колонки (8.0.29+);
• зміна значення за замовчуванням, перейменування колонки;
• розширення списку ENUM/SET у кінці.

ALGORITHM=INPLACE - таблицю перебудовують «на місці», без копіювання через рівень SQL. З LOCK=NONE паралельні читання й записи дозволені, зміни за час операції накопичуються в журналі й застосовуються наприкінці:

• створення вторинного індексу (без перебудови таблиці);
• OPTIMIZE TABLE, зміна ROW_FORMAT;
• додавання NOT NULL до колонки (з перебудовою).

ALGORITHM=COPY - нова таблиця, копіювання всіх рядків, перемикання. Записи заблоковано на весь час копіювання. Сюди потрапляє, зокрема, зміна типу колонки (INT → BIGINT, зміна кодування).

Головне правило - вказувати алгоритм явно:

ALTER TABLE orders ADD COLUMN note TEXT, ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX orders_status_idx (status), ALGORITHM=INPLACE, LOCK=NONE;

Якщо операцію не можна виконати так, MySQL поверне помилку замість того, щоб тихо перейти на COPY і заблокувати таблицю на годину. У Laravel-міграції таке пишуть через DB::statement().

Підводні камені навіть онлайн-операцій:

• блокування метаданих - на початку й наприкінці будь-який ALTER бере виключне MDL і може застрягти за довгою транзакцією, зупинивши трафік до таблиці. Короткий lock_wait_timeout у сесії міграції обов'язковий;
• журнал змін при INPLACE обмежений innodb_online_alter_log_max_size; при інтенсивному записі операція може впасти наприкінці;
• репліки виконують той самий ALTER після джерела і (за однопотокового застосування) відстають на весь його час;
• INSTANT має ліміт: кожне миттєве додавання чи видалення колонки створює нову версію рядка, а в MySQL 8.4 їх допускається 64. Далі - помилка, і таблицю доведеться перебудувати через INPLACE чи COPY.

Для найважчих випадків - зовнішні інструменти gh-ost чи pt-online-schema-change: вони створюють тіньову копію таблиці, поступово копіюють дані, наздоганяють зміни (через бінарний журнал чи тригери) і атомарно підміняють таблицю. Копіювання можна пригальмувати під навантаженням.

Докладніше в документації: Онлайн-операції DDL

Коли транзакція стає в чергу за блокуванням, InnoDB будує граф очікування (хто кого чекає) і перевіряє, чи не утворився цикл. Знайшовши цикл, він обирає «жертву» - транзакцію, відкіт якої найдешевший (за кількістю змінених і заблокованих рядків), - і відкочує її з помилкою 1213. Решта продовжує.

Обмеження перевірки. Якщо граф завеликий - понад 200 транзакцій у списку очікування чи понад мільйон блокувань для перевірки, - InnoDB не шукає далі й вважає ситуацію взаємоблокуванням, відкочуючи транзакцію. Тобто під дуже високою конкуренцією можна отримати deadlock-помилки, яких «насправді» не було.

Ціна виявлення. Перевірка виконується при кожному очікуванні блокування й захищена спільним м'ютексом. Коли сотні потоків чекають на один і той самий гарячий рядок (лічильник переглядів, баланс популярного рахунку, рядок налаштувань), обхід графа на кожне очікування починає їсти процесор, і пропускна здатність падає.

Вимкнення:

SET PERSIST innodb_deadlock_detect = OFF;
SET PERSIST innodb_lock_wait_timeout = 3;   -- за замовчуванням 50 с

Без виявлення справжні взаємоблокування розриваються лише тайм-аутом очікування (помилка 1205). Тому тайм-аут треба різко зменшити, інакше учасники циклу висітимуть по 50 секунд.

Важлива різниця між помилками:

• 1213 (deadlock) - транзакцію відкочено повністю;
• 1205 (lock wait timeout) - за замовчуванням відкочується лише останній оператор, транзакція лишається відкритою (innodb_rollback_on_timeout = OFF). Застосунок має сам зробити ROLLBACK і повторити, інакше закомітить частину змін.

DB::transaction(..., attempts: 3) у Laravel повторює транзакцію в обох випадках і на будь-якому винятку сам робить ROLLBACK. Ризик закомітити частину змін лишається при ручному керуванні транзакціями (beginTransaction() / commit()) з перехопленням винятків.

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

Краще лікування - прибрати гарячий рядок:

• лічильники - розбити на кілька рядків-«слотів» і сумувати при читанні, або накопичувати в Redis і скидати пакетами;
• коротші транзакції, що тримають гарячий рядок мілісекунди;
• черга, яка серіалізує оновлення одного ресурсу.

Докладніше в документації: Виявлення взаємоблокувань

Багато великих інсталяцій 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

До MySQL 8.0 метадані таблиць зберігалися у файлах (.frm) окремо від даних InnoDB. Якщо сервер падав посеред DROP TABLE чи ALTER TABLE, файли й внутрішній словник InnoDB могли розійтися: таблиця «є, але не відкривається», «осиротілі» файли, розбіжність між джерелом і реплікою.

MySQL 8 переніс словник даних у транзакційні таблиці InnoDB і зробив DDL-оператори атомарними: зміни словника, файлів і запис у бінарний журнал або застосовуються повністю, або відкочуються - навіть якщо сервер вимкнувся посеред операції.

Що це дає на практиці:

DROP TABLE t1, t2;

Якщо t2 не існує, раніше t1 встигала видалитися. Тепер оператор падає з помилкою, і жодна таблиця не видаляється. Те саме для RENAME TABLE a TO b, c TO d - або всі перейменування, або жодного.

Чого атомарний DDL не дає - транзакційності:

Atomic DDL is not transactional DDL.

Кожен DDL-оператор, як і раніше, неявно завершує поточну транзакцію (COMMIT) перед виконанням. Тому неможливо:

START TRANSACTION;
ALTER TABLE orders ADD COLUMN source VARCHAR(20);
UPDATE orders SET source = 'web';
ALTER TABLE orders ADD INDEX idx_source (source);   -- якщо впаде, перший ALTER уже застосовано
ROLLBACK;                                            -- нічого не відкотить

Наслідки для міграцій Laravel:

• міграція з кількома змінами схеми не відкочується цілком при помилці посередині: частина колонок уже створена, а запис у таблиці migrations - ні. Повторний запуск падає на «колонка вже існує»;
• Schema:: у PostgreSQL виконується в транзакції й відкочується повністю, а в MySQL - ні. Код міграції, що «працює на PostgreSQL», на MySQL може залишити базу в проміжному стані;
• правило: одна міграція - одна логічна зміна схеми; дані й схему не змішувати в одній міграції; для відкату писати down(), а не розраховувати на транзакцію.

Що ще варто знати:

• атомарність працює для InnoDB; оператори над таблицями інших рушіїв можуть бути неатомарними;
• словник даних тепер у mysql-схемі, а INFORMATION_SCHEMA - представлення над ним, тому запити до неї значно швидші, ніж у 5.7, але статистика таблиць кешується (information_schema_stats_expiry);
• онлайн-зміни великих таблиць - окреме питання: атомарність не скасовує блокувань метаданих і копіювання таблиці при ALGORITHM=COPY.

Докладніше в документації: MySQL: атомарний DDL