Middle: питання на співбесіді з теми «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».

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

Блокувальні читання читають останню закомічену версію рядка (а не знімок) і ставлять на нього блокування до кінця транзакції. Поза транзакцією (в автокоміті) вони майже марні: блокування знімається одразу.

FOR SHARE (раніше LOCK IN SHARE MODE) - спільне блокування. Інші теж можуть читати з FOR SHARE, але змінити рядок чи взяти FOR UPDATE не можуть до коміту.

Типове застосування - перевірити, що батьківський рядок існує й не зникне, поки вставляємо дочірній:

START TRANSACTION;
SELECT id FROM projects WHERE id = 5 FOR SHARE;
INSERT INTO tasks (project_id, title) VALUES (5, 'Нове завдання');
COMMIT;

FOR UPDATE - виключне блокування: так, ніби рядок уже оновлюють. Інші FOR UPDATE, FOR SHARE і зміни чекають. Звичайні SELECT без блокувань читають далі зі знімка.

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- перевірка в застосунку
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

У Laravel: ->sharedLock() і ->lockForUpdate() всередині DB::transaction().

NOWAIT - не чекати: якщо рядок заблоковано, одразу помилка (ER_LOCK_NOWAIT). Зручно для інтерфейсу «запис редагує інший користувач».

SKIP LOCKED - пропустити заблоковані рядки. Класична черга завдань, де кілька воркерів не беруть одне й те саме:

START TRANSACTION;
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- позначити взяті й закомітити

Саме так драйвер черг database у Laravel вибирає завдання на MySQL 8+.

Нюанси InnoDB:

• блокується не «рядок за умовою», а записи індексу, які пройшов пошук. Без індексу на колонці з умови FOR UPDATE заблокує всі прочитані рядки таблиці;
• на REPEATABLE READ додаються блокування проміжків (gap locks), тож може блокуватися й вставка в діапазон;
• SKIP LOCKED повертає неузгоджену картину даних - для черг це нормально, для звітів ні;
• FOR UPDATE OF orders у запиті з JOIN блокує лише рядки вказаної таблиці.

Докладніше в документації: Блокувальні читання

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

Види блокувань:

• record lock - блокування одного запису індексу;
• gap lock - блокування проміжку між записами (без самих записів). Забороняє лише вставку в цей проміжок;
• next-key lock - запис плюс проміжок перед ним. Саме так InnoDB блокує при пошуку й скануванні індексу на REPEATABLE READ.

Приклад, що дивує:

-- у таблиці є рядки з age = 20 і age = 30, індекс на age
-- сесія A
START TRANSACTION;
SELECT * FROM users WHERE age BETWEEN 21 AND 29 FOR UPDATE;  -- 0 рядків

-- сесія B
INSERT INTO users (age) VALUES (25);    -- чекає!
INSERT INTO users (age) VALUES (35);    -- може теж чекати: next-key lock
                                        -- на запис 30 разом із проміжком

Сесія A не знайшла жодного рядка, але заблокувала проміжок, щоб повторний запит дав той самий результат.

Коли блокування ширше, ніж очікуєш:

• немає індексу на колонці з умови - сканується вся таблиця, і блокуються всі записи з проміжками. UPDATE ... WHERE email = ? без індексу на email фактично блокує таблицю для вставок;
• неунікальний індекс - блокуються проміжки з обох боків знайдених записів;
• пошук за унікальним індексом з рівністю блокує лише сам запис, без проміжку.

Gap locks не конфліктують між собою - дві транзакції можуть тримати блокування одного проміжку. Звідси класичне взаємоблокування: обидві роблять SELECT ... FOR UPDATE за неіснуючим ключем, обидві отримують gap lock, обидві намагаються вставити - deadlock.

READ COMMITTED вимикає gap locks для пошуку й сканування (лишаються лише для перевірки зовнішніх ключів і дублікатів). Блокувань і взаємоблокувань менше, але фантоми можливі.

Як побачити блокування:

SELECT engine_transaction_id, index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks;

lock_mode вигляду X,GAP, X,REC_NOT_GAP чи просто X (next-key) показує, що саме тримає транзакція.

Докладніше в документації: Блокування next-key і фантомні рядки

Взаємоблокування (deadlock) - дві транзакції чекають одна на одну: A тримає рядок 1 і хоче рядок 2, B тримає рядок 2 і хоче рядок 1. InnoDB помічає цикл і відкочує одну з транзакцій (зазвичай ту, що змінила менше рядків). Вона отримує помилку 1213, друга продовжує роботу.

Перше правило: deadlock - не аварія. У системі з паралельними записами вони трапляються. Застосунок має бути готовим повторити транзакцію повністю:

DB::transaction(function () use ($from, $to, $amount) {
    // ...
}, attempts: 3);

Laravel повторює замикання при взаємоблокуванні. Важливо: повторюється вся транзакція, тож побічні ефекти (листи, HTTP-запити) - лише після коміту (DB::afterCommit() чи afterCommit у подіях і джобах).

Друге - з'ясувати причину:

SHOW ENGINE INNODB STATUS\G
-- розділ LATEST DETECTED DEADLOCK: обидві транзакції,
-- їхні запити, які блокування тримають і чого чекають

Там видно лише останній deadlock. Щоб писати всі в журнал помилок:

SET PERSIST innodb_print_all_deadlocks = ON;

Типові причини й ліки:

• Різний порядок захоплення рядків. Переказ з A на B і з B на A одночасно. Ліки - завжди блокувати в одному порядку (наприклад, за зростанням id):

$accounts = Account::whereIn('id', [$from, $to])->orderBy('id')->lockForUpdate()->get();

• Відсутній індекс. UPDATE ... WHERE без індексу блокує все проскановане - ймовірність перетину різко зростає.
• Блокування проміжків. Два SELECT ... FOR UPDATE за неіснуючим ключем, а потім два INSERT. Ліки - INSERT ... ON DUPLICATE KEY UPDATE замість «перевірити, потім вставити», або READ COMMITTED.
• Довгі транзакції. Чим довше тримаються блокування, тим більше перетинів. Важкі обчислення й зовнішні виклики - поза транзакцією.
• Масові оновлення. Великий UPDATE по частинах з однаковим порядком.

Що не допомагає: збільшувати innodb_lock_wait_timeout - він стосується звичайного очікування, а не циклів, які InnoDB розриває одразу.

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

Буферний пул (buffer pool) - головна область пам'яті InnoDB. Там кешуються сторінки таблиць і індексів (по 16 КБ). Запит, якому вистачило даних у пам'яті, не йде на диск. Зміни теж спершу пишуться в сторінки буферного пулу («брудні» сторінки), а на диск скидаються пізніше у фоні.

Розмір - найважливіше налаштування MySQL. Параметр innodb_buffer_pool_size, за замовчуванням лише 128 МБ - для продакшену майже завжди замало.

Як вибирати:

• на виділеному під MySQL сервері - зазвичай 50-75% оперативної пам'яті. Решта потрібна ОС, з'єднанням (буфери сортування, з'єднань на кожну сесію), тимчасовим таблицям;
• якщо на тому ж сервері працюють PHP, Redis, черги - менше, залежно від їхніх потреб;
• ідеал - щоб робочий набір (часто читані таблиці й індекси) поміщався в пул повністю. Вся база в пам'ять не обов'язкова.

Для виділеного сервера є ще варіант innodb_dedicated_server = ON: InnoDB сам визначає розмір пулу й журналу повтору з обсягу пам'яті.

Як перевірити, чи вистачає:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests - логічні читання
-- Innodb_buffer_pool_reads         - довелося йти на диск

SELECT * FROM sys.innodb_buffer_stats_by_table LIMIT 10;  -- хто займає пул

Частка reads / read_requests має бути дуже малою (соті частки відсотка). Помітне зростання - робочий набір не вміщується.

Як влаштоване витіснення. Пул використовує LRU з «точкою вставки посередині»: нові сторінки потрапляють не на початок списку, а в «старшу» частину (останні 3/8). Так одноразове повне сканування великої таблиці (наприклад, дамп) не витісняє з пам'яті гарячі дані.

Корисне:

• розмір можна змінювати на льоту (SET PERSIST innodb_buffer_pool_size = ...), зміна відбувається поступово блоками;
• під час зупинки MySQL зберігає список гарячих сторінок, а під час старту завантажує їх (innodb_buffer_pool_dump_at_shutdown / load_at_startup, увімкнено за замовчуванням), тож після перезапуску кеш «прогрівається» швидше;
• на великих пулах він ділиться на кілька екземплярів (innodb_buffer_pool_instances), щоб зменшити конкуренцію.

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

Блокування метаданих (metadata lock, MDL) захищає структуру таблиці: поки хтось працює з таблицею в транзакції, змінити її схему не можна. Будь-який запит до таблиці, навіть звичайний SELECT, бере спільне MDL і тримає його до кінця транзакції. ALTER TABLE потребує виключного MDL.

Класичний сценарій аварії:

-- сесія 1: хтось у консолі
START TRANSACTION;
SELECT * FROM orders LIMIT 1;   -- MDL взято, транзакція не закрита
-- ... пішов на обід

-- сесія 2: деплой
ALTER TABLE orders ADD COLUMN note TEXT;   -- чекає на MDL

-- сесії 3..N: звичайний трафік
SELECT * FROM orders WHERE id = 5;         -- теж чекають!

Найнеприємніше - останній крок: ALTER стоїть у черзі за виключним блокуванням, і всі нові запити до таблиці стають у чергу за ним. Таблиця фактично недоступна, хоча сам ALTER навіть не почався. Пул з'єднань застосунку швидко вичерпується.

Звідки беруться довгі транзакції:

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

Як знайти винуватця:

SELECT * FROM sys.schema_table_lock_waits\G
-- хто чекає, хто блокує, і готова команда KILL для блокувальника

SELECT trx_mysql_thread_id, trx_started, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;

Як захистити деплой:

• задати короткий тайм-аут очікування для сесії міграцій. За замовчуванням lock_wait_timeout - рік:

SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD COLUMN note TEXT;   -- не дочекався за 5 с - помилка, а не простій

• повторювати міграцію кілька разів з паузою;
• перед міграцією перевіряти innodb_trx на довгі транзакції;
• обмежувати довжину транзакцій у застосунку й тайм-аут неактивних сесій (wait_timeout).

Онлайн DDL не рятує: навіть ALGORITHM=INSTANT бере виключне MDL на коротку мить на початку й наприкінці - і так само може застрягти за довгою транзакцією.

Докладніше в документації: Блокування метаданих

За замовчуванням (innodb_file_per_table = ON) кожна таблиця InnoDB живе у власному файлі .ibd. Коли рядки видаляють, InnoDB позначає місце вільним усередині файлу й використовує його для нових рядків цієї ж таблиці. Операційній системі місце не повертається.

-- таблиця логів займає 40 ГБ
DELETE FROM activity_log WHERE created_at < NOW() - INTERVAL 90 DAY;
-- файл activity_log.ibd усе ще 40 ГБ, але всередині багато вільних сторінок

Як оцінити вільне місце:

SELECT table_name,
       ROUND(data_length / 1024 / 1024) AS data_mb,
       ROUND(index_length / 1024 / 1024) AS index_mb,
       ROUND(data_free / 1024 / 1024) AS free_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_free DESC;

Цифри приблизні (оцінка за статистикою), але порядок величин видно.

Як повернути місце ОС:

• OPTIMIZE TABLE - для InnoDB це перебудова таблиці (ALTER TABLE ... FORCE): створюється нова компактна копія, стара видаляється. Виконується онлайн (паралельні зміни дозволені), але потребує вільного диска на розмір таблиці й створює навантаження;
• TRUNCATE TABLE - якщо треба очистити все: файл перестворюється порожнім, миттєво;
• секціонування за датою й ALTER TABLE ... DROP PARTITION - найкращий варіант для логів: стара частина видаляється разом із файлом, без DELETE мільйонів рядків.

Чи потрібно повертати місце взагалі? Часто ні. Якщо таблиця знову виросте до того ж розміру, вільне місце просто використається. Перебудова має сенс, коли таблиця назавжди зменшилася в рази або диск закінчується.

Системний табличний простір ibdata1 не зменшується ніколи. Якщо таблиці колись створили з innodb_file_per_table = OFF, їхні дані лежать там, і звільнити місце можна лише перенесенням даних у новий інстанс. Тому вимикати file-per-table немає причин.

Масове видалення варто робити частинами (DELETE ... LIMIT 10000 у циклі): один гігантський DELETE - це довга транзакція, величезний undo-журнал і затримка реплік.

Докладніше в документації: Табличні простори file-per-table

Формат рядка визначає, як InnoDB розкладає колонки рядка по сторінках. Є чотири: REDUNDANT, COMPACT, DYNAMIC і COMPRESSED. За замовчуванням - DYNAMIC, і його варто лишати.

Головна відмінність - довгі колонки (TEXT, BLOB, JSON, довгі VARCHAR):

• DYNAMIC: якщо значення не вміщується в сторінку, воно цілком переноситься на окремі сторінки переповнення, а в рядку лишається 20-байтовий вказівник;
• COMPACT: у рядку лишаються перші 768 байтів значення, решта - на сторінках переповнення. Широка таблиця з багатьма TEXT швидко впирається в ліміт.

Звідки «Row size too large» - насправді два різні ліміти:

1. 65 535 байтів на рядок - ліміт MySQL для визначення таблиці. Рахується максимальний розмір усіх колонок. VARCHAR(255) в utf8mb4 важить до 1020 байтів, тож двісті таких колонок уже не вмістяться. TEXT/BLOB додають лише 9-12 байтів, бо зберігаються окремо.
2. Майже половина сторінки (~8 КБ при 16-КБ сторінках) - ліміт InnoDB для частини рядка, що лишається в сторінці. Помилка може з'явитися не при створенні таблиці, а при вставці, коли реальні дані не вмістилися.

-- рішення для 1-го ліміту: довгі VARCHAR перевести в TEXT
ALTER TABLE products MODIFY description TEXT;

-- перевірити формат
SELECT name, row_format FROM information_schema.innodb_tables WHERE name LIKE 'app/%';

Другий наслідок формату - довжина ключа індексу:

• DYNAMIC/COMPRESSED: до 3072 байтів на індекс;
• COMPACT/REDUNDANT: лише 767 байтів.

Звідси історичний Schema::defaultStringLength(191) у Laravel: 191 × 4 байти (utf8mb4) = 764 < 767. На MySQL 5.7.7+ і 8.x з DYNAMIC це обмеження вже не потрібне - VARCHAR(255) під унікальним індексом важить 1020 байтів, що вміщується в 3072.

COMPRESSED стискає сторінки, економлячи диск ціною процесора й складнішої роботи буферного пулу. Сьогодні частіше вибирають стиснення на рівні файлової системи чи сховища.

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

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