Scenario Setup

Consider the following table and update operation for our analysis:

-- Table definition with clustered and secondary indexes
CREATE TABLE `products` (
  `product_id` int NOT NULL AUTO_INCREMENT,
  `sku` varchar(32) NOT NULL,
  `quantity` int NOT NULL DEFAULT 0,
  `cost` decimal(10,2) NOT NULL,
  PRIMARY KEY (`product_id`),
  UNIQUE KEY `uk_sku` (`sku`)
) ENGINE=InnoDB;

-- Initial data
INSERT INTO `products` (`sku`, `quantity`, `cost`) VALUES ('SKU-9001', 500, 29.99);

-- Target transaction
START TRANSACTION;
UPDATE `products` SET `quantity` = 450 WHERE `sku` = 'SKU-9001';
COMMIT;

Execution Pipeline

The update lifecycle follows the Write-Ahead Logging (WAL) protocol to ensure ACID compliance.

1. Transaction Context Initialization

When START TRANSACTION is executed:

• A unique internal transaction ID (tx_id) is generated.
• A transaction object is created to track undo/redo states.
• The session is switched to manual commit mode.

2. Index Traversal and Row Identification

The engine processes the WHERE sku = 'SKU-9001' predicate:

• The optimizer selects the uk_sku unique secondary index.
• The B+Tree for uk_sku is traversed to find the leaf node containing the matching SKU.
• The leaf node stores the primary key value (product_id). The engine uses this to perform a lookup (or bookmark lookup) into the clustered index (primary key B+Tree) to locate the actual data page in the Buffer Pool.

3. Lock Acquisition

Before modifying data, the engine secures locks on the relevant index entries:

• Secondary Index Lock: A Record Lock is placed on the uk_sku index entry for 'SKU-9001'.
• Clustered Index Lock: A Record Lock is placed on the primary key entry in the clustered index.
• Note: For unique indexes, InnoDB typically downgrades Next-Key Locks to simple record locks during an equality search.
• If another transaction holds a conflicting lock, the current thread enters a wait state until the lock is released or a timeout occurs.

4. Undo Log Generation

To support atomicity and Multi-Version Concurrency Control (MVCC), the engine writes an undo record:

• This is a logical log storing the inverse operation (e.g., how to restore quantity to 500).
• The specific data page in the Buffer Pool is updated with a pointer (roll_ptr) linking to this new undo record, forming a version chain.
• The hidden tx_id column in the row is updated to the current transaction's ID.

5. In-Memory Modification and Redo Logging

The actual data change happens in memory first:

• Buffer Pool Update: The quantity field in the cached data page is changed from 500 to 450. The page is now marked as a Dirty Page.
• Redo Log Buffer: A physical log entry is written to the in-memory redo buffer. This entry describes the specific tablespace, page number, and byte-offset modification.

6. Two-Phase Commit (2PC) on COMMIT

To keep the storage engine (InnoDB redo) and the server layer (binlog) consistent, the commit acts in two phases:

Phase 1: Prepare

• The redo log records in the buffer are flushed to the ib_logfile on disk (triggered by innodb_flush_log_at_trx_commit=1).
• The transaction is marked as PREPARED in the redo log.

Phase 2: Commit

• The logical SQL statement is written to the binary log (binlog) and flushed to disk (sync_binlog=1).
• A COMMIT marker is written to the redo log.
• All locks held by the transaction are released.

7. Background Flushing (Checkpointing)

The transaction is complete once the logs are on disk; the actual data page does not need to be written immediately. Background threads (Page Cleaners) handle this asynchronously:

• Dirty pages are written back to the .ibd tablespace file.
• This occurs when the Buffer Pool is full, the redo log is near capacity, or via a periodic schedule.

Critical Component Comparison

Handling Exceptions

• ROLLBACK: The engine reads the undo log chain to reconstruct the previous values (500), restores the Buffer Pool page, releases locks, and purges the undo entries.
• Crash during Transaction: If the crash happens before the COMMIT, the transaction is rolled backupon restart using the undo log. If it crashes after the PREPARE phase but before the binlog write, the engine checks the binlog upon restart to decide whether to commit or rollback.
• Missing Index: If the WHERE clause cannot use an index, InnoDB performs a full table scan. It locks every row it encounters (effectively a table lock), which blocks all concurrent operations on that table.

Secondary Index Updates

If the update targeted the sku column (a secondary index), InnoDB handles it differently due to B+Tree structure:

1. It performs a logical delete of the old entry in the secondary index.
2. It inserts a new entry with the updated value into the secondary index. This is not an in-place update but a deletion followed by insertion.