Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

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

Питання з реальних співбесід з відповідями: Laravel і PHP, бази даних, JavaScript і фронтенд, Git, Docker, API, безпека й архітектура. Тими самими темами, що й тести.

19 питань

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