MySQL 8.4 InnoDB internals: commit, recovery, replication, and online DDL

MySQL Server parses and plans SQL while InnoDB owns transactional rows, indexes, MVCC, locks, undo, redo, and the buffer pool. The binary log is a server-level replication/change stream, not a substitute for InnoDB redo. Crash safety coordinates these logs around commit.

Current MySQL 8.4 defaults matter: row-based binary logging is the default; replicas are multithreaded by default with four worker threads and commit-order preservation enabled. Parallelism and online DDL remain operation- and workload-dependent rather than universal guarantees.

Модель и предпосылки

• A committed InnoDB change can reside in memory data pages because durable redo permits roll-forward after crash.
• Undo supports rollback and older row versions; purge removes versions only when they are no longer needed.
• A replica receiver stores events in a relay log and applier workers execute eligible transactions; lag and apply order require monitoring.

Проверяемые утверждения

• C1. The InnoDB buffer pool caches both table and index pages; a cache miss reads pages, while dirty pages may be flushed after commit because redo carries recovery information.
• C2. Crash recovery applies redo and rolls back incomplete transactions; change-buffer merge and purge can continue after connections are accepted.
• C3. Row-based binary logging is the MySQL 8.4 default; statement and mixed formats have different safety restrictions.
• C4. MySQL 8.4 replicas use a multithreaded applier by default (replica_parallel_workers=4); more workers are not a monotonic throughput guarantee.
• C5. Online DDL support is operation-specific across INSTANT, INPLACE, and COPY behavior; some operations permit concurrent DML and others rebuild or lock.
• C6. InnoDB isolation levels have distinct locking and snapshot behavior; deadlocks can occur at any isolation level and require complete transaction retry.

Исполняемые сценарии

Commit and crash-recovery contract. Redo durability can precede data-page flush; recovery rolls committed changes forward and incomplete work back.

Index read through the buffer pool. Logical B-tree steps do not translate to a fixed number of disk I/O operations.

Row binlog and parallel replica apply. Current defaults allow several applier workers, while dependencies and locks bound useful parallelism.

Operation-specific online DDL. DDL is admitted only after checking supported algorithm, locks, rebuild work, and resource headroom.

Deadlock and whole-transaction retry. InnoDB rolls back a victim; the application starts the complete transaction again.

Ошибки проектирования

• Не путайте redo log с binary log: первый восстанавливает InnoDB, второй служит server-level replication/CDC.
• Не обещайте, что secondary-index lookup всегда делает ровно два физических I/O: buffer pool и covering index меняют путь.
• Не объявляйте любой ALTER TABLE nonblocking; проверяйте algorithm, lock, rebuild, disk headroom и long transactions.
• Deadlock возможен даже для коротких операций; приложение обязано повторить всю транзакцию.

Границы гарантии

• Настройки durability (innodb_flush_log_at_trx_commit, sync binlog и storage behavior) меняют failure contract; их нельзя скрывать за словом ACID.
• Replica lag, failover и read-after-write не решаются самим наличием binlog.
• Значения defaults относятся к MySQL 8.4 и должны перепроверяться при смене версии.

Связанные материалы

Официальные и первичные источники

1. MySQL 8.4: InnoDB Buffer Pool Optimization
2. MySQL 8.4: InnoDB Recovery
3. MySQL 8.4: Replication Formats
4. MySQL 8.4: Replica Server Options and Variables
5. MySQL 8.4: Online DDL Operations
6. MySQL 8.4: InnoDB Transaction Isolation Levels
7. MySQL 8.4: InnoDB Deadlocks