Что такое свойства ACID и как InnoDB их реализует?

Уровни изоляции и их эффекты

-- Посмотреть текущий уровень
SHOW VARIABLES LIKE 'transaction_isolation';

-- Изменить для сессии
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- READ UNCOMMITTED — грязное чтение (dirty read)
-- READ COMMITTED   — нет грязного чтения, возможен non-repeatable read
-- REPEATABLE READ  — default InnoDB; нет non-repeatable read; phantom read
--                    закрыт next-key locks
-- SERIALIZABLE     — полная блокировка; SELECT превращается в SELECT ... FOR SHARE

Что такое ACID

ACID — четыре свойства, которые гарантирует транзакционная СУБД. В MySQL эти свойства реализует движок InnoDB; MyISAM их не поддерживает.

• Atomicity (атомарность) — транзакция либо применяется целиком, либо откатывается. InnoDB использует undo log: до изменения строки старая версия записывается в undo-сегмент табличного пространства. При ROLLBACK или сбое движок читает undo log и восстанавливает прежние данные.
• Consistency (согласованность) — данные всегда удовлетворяют ограничениям: NOT NULL, UNIQUE, FOREIGN KEY, CHECK (MySQL 8.0.16+). InnoDB проверяет FK до фиксации; нарушение вызывает ошибку и автоматический откат.
• Isolation (изоляция) — параллельные транзакции не видят промежуточных состояний друг друга. InnoDB реализует MVCC (Multi-Version Concurrency Control): каждая строка хранит поле DB_TRX_ID и указатель на undo log. Уровень изоляции задаётся переменной transaction_isolation.
• Durability (долговечность) — зафиксированные данные не теряются при сбое. InnoDB записывает изменения в redo log (файлы #ib_redo* в MySQL 8.0+, ib_logfile* в 5.7) до записи страниц в data file. Переменная innodb_flush_log_at_trx_commit=1 обеспечивает fsync после каждого COMMIT.

Ключевые механизмы InnoDB

• Redo log — журнал для восстановления после сбоя (crash recovery). InnoDB применяет его при старте, если страницы не были сброшены на диск.
• Undo log — хранит старые версии строк для ROLLBACK и для consistent read в MVCC.
• Doublewrite buffer — защита от частичной записи страницы (torn page): InnoDB сначала записывает страницу в doublewrite-область, затем в настоящее место. При сбое восстанавливает из doublewrite.
• MVCC и consistent read — SELECT без FOR UPDATE читает «снимок» данных на момент начала транзакции (REPEATABLE READ) или на момент каждого запроса (READ COMMITTED), не блокируя пишущие транзакции.

Проверка состояния InnoDB

-- Общий статус движка: транзакции, блокировки, redo log, buffer pool
SHOW ENGINE INNODB STATUS\G

-- Активные транзакции
SELECT trx_id, trx_state, trx_started, trx_query
FROM information_schema.INNODB_TRX;

-- Ожидания блокировок (MySQL 8.0)
SELECT * FROM performance_schema.data_lock_waits;

-- Настройки durability
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'innodb_doublewrite';

Пример транзакции с демонстрацией атомарности

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Если второй UPDATE упал — ROLLBACK вернёт оба изменения
COMMIT;

Подводные камни

• innodb_flush_log_at_trx_commit=2 — ускоряет запись, но при сбое ОС можно потерять до 1 секунды транзакций. Значение 0 ещё опаснее — сбой mysqld тоже даёт потерю данных.
• DDL вызывает implicit commit — если внутри транзакции выполнить CREATE TABLE, ALTER TABLE, TRUNCATE, DROP — MySQL незаметно фиксирует всё, что было до этого, и начинает новую транзакцию.
• autocommit=1 по умолчанию — каждый DML-запрос вне явного BEGIN является отдельной транзакцией. Забытый START TRANSACTION означает, что откат невозможен.
• MyISAM не поддерживает ACID — таблицы MyISAM не имеют redo/undo log и транзакций. Смешение InnoDB и MyISAM в одной бизнес-операции разрушает атомарность.
• Next-key locks и phantom read — InnoDB в REPEATABLE READ блокирует не только конкретные строки, но и «щели» (gaps) между ними. Это предотвращает phantom read, но увеличивает вероятность дедлока при параллельных INSERT.
• Долгие транзакции раздувают undo log — чем дольше открыта транзакция, тем больше старых версий строк хранит undo tablespace. Это замедляет purge thread и увеличивает MVCC-overhead для всех читателей.
• Consistent read vs locking read — SELECT без FOR UPDATE использует MVCC-снимок и не блокирует; SELECT ... FOR UPDATE берёт row-level lock. Смешение этих режимов в одной транзакции — частая причина race condition.
• Crash recovery зависит от redo log size — слишком маленький innodb_log_file_size (MySQL 5.7) ускоряет checkpoint, но замедляет восстановление при большом объёме грязных страниц.

• Не уточнять storage engine и говорить о acid в innodb так, будто MyISAM и InnoDB ведут себя одинаково.
• Игнорировать implicit commit, autocommit, isolation level или binlog там, где они меняют ответ.
• Описывать PostgreSQL-поведение как MySQL-поведение.
• Забывать проверить план через EXPLAIN и фактическую версию MySQL.

• Кандидат объясняет acid в innodb через реальный механизм MySQL, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.