MySQL InnoDB, in depth: one UPDATE through the clustered index, buffer pool, undo, redo, doublewrite and binlog, plus read views, next-key locking and crash recovery

The table is the clustered index

An InnoDB table is a B+tree ordered by primary key, and the leaf pages hold the full rows. There is no separate heap. Pages are 16 KiB by default. Every secondary index is another B+tree whose leaves hold the indexed columns plus the primary key, so a lookup through a secondary index finds the primary key and then descends the clustered index a second time, unless the index covers every column the query needs.

Each row also carries hidden columns: DB_TRX_ID, the ID of the last transaction that modified it, and DB_ROLL_PTR, a pointer to the undo record holding the previous version. A table without a primary key or a non-null unique key gets a hidden DB_ROW_ID as its clustering key. These two columns are what make MVCC and rollback work. A wide primary key is copied into every secondary index entry; MySQL versus PostgreSQL walks through what a random primary key does to this tree.

One UPDATE, step by step

Take UPDATE accounts SET balance = balance - 100 WHERE id = 42 in an autocommit transaction with the binary log on, as it is by default.

1. Find the row. InnoDB descends the clustered index from the root. Each page it needs must be in the buffer pool; a miss reads it from disk. Hot upper levels of the tree are almost always cached.
2. Lock it. The transaction takes an exclusive lock on the index record for id 42. Because the condition is an equality on a unique key, no gap lock is needed.
3. Write undo. The old balance is written to an undo record in an undo tablespace, and the row's roll pointer is set to it. Undo pages are themselves buffer pool pages protected by redo.
4. Change the page. The new value is written into the page in the buffer pool inside a mini-transaction, which generates redo records into the in-memory log buffer and marks the page dirty. Nothing has reached the data file yet.
5. Commit. With the binary log enabled, MySQL runs a two-phase commit: InnoDB prepares the transaction and makes its redo durable; the server writes the binlog events and fsyncs the binlog, batching many transactions together in group commit; then InnoDB marks the transaction committed and releases its locks. The client gets OK only after this.
6. Later. Page cleaner threads write the dirty page to its data file through the doublewrite buffer, which lets the checkpoint advance. Once no read view can need the old version, purge threads discard the undo record.

The design choice is that commits pay for sequential log writes, not random page writes. The data file can be minutes behind, and the redo log is what fills the gap after a crash.

Redo, checkpoints and the durability knobs

The redo log is a circular sequence of records addressed by log sequence number. Since 8.0.30 its size is one setting, innodb_redo_log_capacity, and the files live in the #innodb_redo directory. The distance between the current LSN and the last checkpoint is the checkpoint age: redo that must be replayed after a crash. When writes outpace flushing and the age approaches capacity, InnoDB must flush dirty pages urgently, and throughput drops. A common sizing rule is to hold at least an hour of peak redo generation; measure the rate by sampling the LSN twice, for example from the Innodb_redo_log_current_lsn status variable available since 8.0.30, or from the LOG section of SHOW ENGINE INNODB STATUS.

Pair it with sync_binlog: 1, the default, fsyncs the binlog at each group commit. Only the combination 1 and 1 guarantees that a committed transaction is both durable and in the binlog that replicas read. Relaxing either trades a bounded loss window for fewer fsyncs, which matters most on storage with slow flushes.

Undo, read views and what a reader sees

A consistent read never takes locks. It reads the newest version of a row and, if that version is not visible to it, follows the roll pointer into undo, rebuilding older versions until it finds one that is. Visibility is decided by a read view, a snapshot of which transactions were active when the view was created:

def visible(row_trx_id, view, my_trx_id):
    # view.up_limit:  smallest transaction ID active when the view was created
    # view.low_limit: next transaction ID to be assigned at that moment
    # view.active:    IDs of transactions active at that moment
    if row_trx_id == my_trx_id:
        return True                      # my own changes
    if row_trx_id < view.up_limit:
        return True                      # committed before anything I could not see
    if row_trx_id >= view.low_limit:
        return False                     # started after my snapshot
    return row_trx_id not in view.active # in between: visible only if it had committed

Under REPEATABLE READ, the default, a transaction creates its read view at its first consistent read, not at BEGIN, unless you use START TRANSACTION WITH CONSISTENT SNAPSHOT, and reuses it until it ends. Under READ COMMITTED each statement gets a fresh view. Locking reads, UPDATE and DELETE do not use the view: they read and lock the latest committed version.

Undo cannot be purged while any read view might need it. The backlog is the history list length, printed in the TRANSACTIONS section of SHOW ENGINE INNODB STATUS. One idle transaction left open for hours, often a forgotten session or a long report, pins every older version in the database: undo grows, reads walk longer version chains, and queries slow down. Find the culprit in information_schema.INNODB_TRX by trx_started.

Buffer pool, doublewrite and features now off by default

The buffer pool caches pages in a least-recently-used list split into a young and an old sublist. A newly read page enters at the head of the old sublist, which is 37 percent of the list by default, and moves to the young end only if touched again after innodb_old_blocks_time, 1,000 ms by default. A single full table scan therefore cannot flush the working set out of memory. Size the pool to hold the hot working set, commonly most of a dedicated server's RAM, and watch the read rate from disk rather than the hit ratio alone.

Pages are 16 KiB but most storage writes atomically in 4 KiB units, so a crash during a page write can leave a torn page that redo cannot repair, because redo records describe changes, not whole pages. The doublewrite buffer writes each batch of pages to a doublewrite file first and then to its real location; on recovery a torn page is restored from its doublewrite copy. It costs extra writes, mostly sequential, and should stay on unless the filesystem guarantees atomic 16 KiB writes.

MySQL 8.4 changed several InnoDB defaults from 8.0. The change buffer, which deferred secondary-index updates for pages not in memory, now defaults to innodb_change_buffering=none, and the adaptive hash index is OFF. Treat both as opt-in: the change buffer helps write-heavy workloads with large non-unique secondary indexes on slow storage, and the adaptive hash index can help read-heavy point lookups but has caused latch contention under mixed loads. Measure before enabling either. 8.4 also raised innodb_io_capacity to 10,000 and the log buffer to 64 MiB, and uses O_DIRECT on Linux where supported; review settings carried over from 8.0 configuration files.

Locking: record, gap and next-key locks

Locking reads (SELECT ... FOR UPDATE and FOR SHARE), UPDATE and DELETE lock index records they scan. Under REPEATABLE READ, InnoDB uses next-key locks: a lock on a record plus the gap before it, so no other transaction can insert into a range you have read and phantoms cannot appear. An equality search on a unique index for a row that exists locks only the record. A search that finds nothing locks the gap where the row would be. Gap locks do not conflict with each other; they only block inserts, which must take an insert intention lock on the gap. Under READ COMMITTED, gap locking is disabled for searches and index scans and is used only for foreign-key and duplicate-key checks. The isolation trade-offs in general are covered in isolation levels.

Worked example: the gap-lock deadlock

A common upsert pattern is "check if it exists, insert if not" in one transaction. Table t has primary keys 10 and 20, and two sessions run it for id 15 at the same time:

-- session A                              -- session B
BEGIN;                                     BEGIN;
SELECT * FROM t WHERE id = 15 FOR UPDATE;
-- empty; A holds a gap lock on (10, 20)
                                           SELECT * FROM t WHERE id = 15 FOR UPDATE;
                                           -- empty; gap locks are compatible, B holds one too
INSERT INTO t (id, v) VALUES (15, 'a');
-- waits: insert intention blocked by B's gap lock
                                           INSERT INTO t (id, v) VALUES (15, 'b');
                                           -- waits on A's gap lock: deadlock
                                           -- ERROR 1213: InnoDB rolls back one session, here B

While A waits, performance_schema.data_locks shows both sessions holding a lock with mode X,GAP on the record with id 20, and A waiting with X,GAP,INSERT_INTENTION; data_lock_waits links the waiter to the blocker. Neither SELECT did anything wrong in isolation. The fixes, in order of preference: replace the pattern with one atomic statement such as INSERT ... ON DUPLICATE KEY UPDATE; or insert first and handle the duplicate-key error; or run this transaction under READ COMMITTED so the empty search takes no gap lock and the unique key arbitrates. Whatever you choose, the application must retry on error 1213, because InnoDB resolves deadlocks by rolling back a victim.

Crash recovery

On restart InnoDB replays redo from the last checkpoint, bringing every page to its state at the moment of the crash, using doublewrite copies for any torn pages. The database now contains the effects of committed transactions and also of transactions that were in progress. Those in progress are rolled back using their undo records, which redo also preserved. Transactions that reached the prepare phase are resolved against the binary log: if the transaction's XID is in the binlog it is committed, otherwise it is rolled back, so InnoDB and the binlog, and therefore replicas, agree. Recovery time is driven by checkpoint age and by the size of uncommitted transactions to roll back, which is one more reason to avoid huge single transactions.

Failure modes and trade-offs

• History list growth: a long-open transaction stops purge, inflates undo and slows every read that walks version chains. Alert on transaction age, not only query time.
• Checkpoint stalls: redo capacity too small for the write rate causes periodic throughput collapses as InnoDB flushes urgently. Resize redo, which 8.0.30 and later allow online.
• Lock waits and deadlocks: REPEATABLE READ gap locks turn check-then-insert code into deadlocks. Use atomic statements and retry on 1213.
• Relaxed durability by accident: innodb_flush_log_at_trx_commit=2 or sync_binlog=0 copied from a benchmark guide can lose committed transactions or leave replicas ahead of a crashed primary.
• Large transactions: deleting millions of rows in one statement holds locks, bloats undo and makes rollback and recovery slow. Delete in primary-key ranges of a few thousand rows.
• The trade-off itself: clustering by primary key makes primary-key range reads and covering lookups fast and secondary lookups and random inserts more expensive; choose the primary key with that in mind.

What to do next

1. Confirm innodb_flush_log_at_trx_commit=1 and sync_binlog=1 on every primary, or document why not.
2. Measure redo generated per hour at peak and size innodb_redo_log_capacity to hold at least that much.
3. Add alerts on history list length and on the oldest transaction start time in INNODB_TRX.
4. Search your code for check-then-insert under REPEATABLE READ and replace it with atomic statements, keeping a retry on error 1213.
5. Run one deadlock through performance_schema.data_locks in a test environment so the output is familiar before an incident.
6. When moving from 8.0 to 8.4, diff your configuration against the changed defaults, especially change buffering, the adaptive hash index and I/O capacity.