Питання на співбесіді: InnoDB і транзакції

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