Transaction Execution Flow

InnoDB transactions operate through four core components: redo logs, undo logs, locking mechanisms, and MVCC (Multi-Version Concurrency Control). The transaction lifecycle consists of initiation, execution, and commit/rollback phases.

ACID Properties Implementation

• Atomicity: Achieved through undo logs that record reverse operations
• Consistency: Ensured through combined atomicity, isolation, and durability
• Isolation: Maintained via locking and MVCC mechanisms
• Durability: Guaranteed through redo log persistence

Explicit Transaction Flow

Transaction Initiation

When executing BEGIN or START TRANSACTION, InnoDB assigns a unique transaction identifier and initializes transaction context, including redo/undo log positions and MVCC read view preparation.

Statement Execution

• Read operations: Depending on isolation level, uses either locked reads or MVCC snapshot reads
• Write operations:
  • Acquire appropriate row locks
  • Record previous state in undo log
  • Modify buffer pool data pages
  • Log changes to redo log buffer

Transaction Commit

Commit processing ensures durability through:

1. Forcing redo log buffer to disk
2. Releasing acquired locks
3. Coordinating with binary logging via two-phase commit
4. Asynchronous data page flushing

Transaction Rollback

Rollback utilizes undo logs to restore previous data state and releases all held locks.

Core Components Summary

Transaction Isolation Levels

MySQL implements four isolation levels per SQL92 standard, with InnoDB enhancing Repeatable Read to address phantom reads.

Level Definitions

Read Uncommitted (RU)

• Permits reading uncommitted changes from other transactions
• Lowest consistency, highest concurrency
• Generally avoided in production

Read Committed (RC)

• Only returns committed data from other transactions
• Uses MVCC with per-statement read views
• Oracle and SQL Server default level

Repeatable Read (RR)

• Maintains consistent read results throughout transaction
• Uses MVCC with transaction-scoped read views
• InnoDB default with phantom read prevention

Serializable (SR)

• Full serial execution through comprehensive locking
• Highest consistency, lowest concurrency
• Reserved for critical consistency requirements

Isolation Level Comparison

*InnoDB's RR implementation prevents phantom reads through next-key locking

Phentom Read Prevention

InnoDB addresses phantom reads through:

1. MVCC Snapshot Reads: Consistent view of data at transaction start
2. Next-Key Locking: Combination of record locks and gap locks for current reads

Concurrency Anomalies

Dirty Reads

Occur when a transaction reads uncommitted changes that are subsequently rolled back.

-- Transaction A reads uncommitted data from Transaction B
SESSION A: BEGIN;
SESSION B: BEGIN;
SESSION B: UPDATE accounts SET balance = 1500 WHERE id = 101;
SESSION A: SELECT balance FROM accounts WHERE id = 101; -- Returns 1500 (dirty read)
SESSION B: ROLLBACK;
SESSION A: COMMIT; -- Based on invalid data

Resolution: Use Read Committed or higher isolation level.

Non-Repeatable Reads

Occur when a transaction reads the same row multiple times but gets different results due to intermediate commits.

-- Transaction A observes changed data between reads
SESSION A: BEGIN;
SESSION A: SELECT balance FROM accounts WHERE id = 101; -- Returns 1000
SESSION B: BEGIN;
SESSION B: UPDATE accounts SET balance = 1200 WHERE id = 101;
SESSION B: COMMIT;
SESSION A: SELECT balance FROM accounts WHERE id = 101; -- Returns 1200 (changed)
SESSION A: COMMIT;

Resolution: Use Repeatable Read or higher isolation level.

Phantom Reads

Occur when a transaction executes the same range query multiple times but gets different numbers of rows due to intermediate inserts/deletes.

-- Transaction A sees changing row counts in range queries
SESSION A: BEGIN;
SESSION A: SELECT COUNT(*) FROM accounts WHERE branch_id = 5; -- Returns 3
SESSION B: BEGIN;
SESSION B: INSERT INTO accounts VALUES (204, 5, 500);
SESSION B: COMMIT;
SESSION A: SELECT COUNT(*) FROM accounts WHERE branch_id = 5; -- Returns 4 (phantom)
SESSION A: COMMIT;

Resolution: Use Repeatable Read (InnoDB) or Serializable isolation level.

Key Differentiators

• Dirty vs Non-repeatable Reads: Uncommitted vs committed changes
• Non-repeatable vs Phantom Reads: Row content changes vs row count changes

Key Implemantation Details

• Read View Creation: RC creates views per statement, RR per transaction
• Next-Key Locking: Combines record locks with gap locks for range protection
• Two-Phase Commit: Coordinates redo log and binary log persistence
• Undo Log Dual Purpose: Supports both rollback operations and MVCC versioning