Reference: Xiaolin coding

ACID Properties

• Atomicity: implemented via undo log.
• Consistency: ensured by the other three properties. Before and after execution, the database transitions between consistent states.
• Isolation: MVCC/locks. One transaction’s execution should not affect others.
• Durability: redo log. Once committed, changes are permanent even after crash.

Starting a Transaction

• begin/start transaction marks the beginning. The transaction actually starts only when the first SELECT after this statement executes.
• start transaction with consistent snapshot: immediately starts the transaction and takes a snapshot.

Isolation Levels

Possible anomalies: dirty read, non-repeatable read, phantom read.

• Dirty read: A reads B’s uncommitted data; if B rolls back, A read wrong data.

• Non-repeatable read: within the same transaction, reading the same row twice yields different values due to another committed update.

• Phantom read: within the same transaction, the count of rows matching a predicate changes between reads.

• Read Uncommitted: highest performance, weakest consistency; rarely used in production. Directly reads latest data without locks or MVCC.

• Read Committed: uses row locks/MVCC.

• Repeatable Read: InnoDB’s default.

• Serializable: reads take shared locks; writes take exclusive locks.

MVCC

Two hidden columns maintained in undo log: trx_id and roll_pointer.

Read View fields:

• m_ids: list of active transaction IDs at snapshot creation.
• min_trx_id: smallest active transaction ID.
• max_trx_id: largest active transaction ID + 1.
• creator_trx_id: the transaction that created the Read View.

Visibility rules when selecting a row version chain:

1. If version.trx_id == creator_trx_id, visible.
2. Else if version.trx_id < min_trx_id, visible (committed before snapshot).
3. Else if version.trx_id >= max_trx_id, invisible (started after snapshot); check next version.
4. Else (between min and max): visible only if version.trx_id not in m_ids; otherwise check next version.

Snapshot timing:

• Read Committed: a new snapshot is created for each read.
• Repeatable Read: snapshot is created when the transaction starts.

Handling Phantoms

At Repeatable Read, InnoDB largely prevents phantoms:

• For snapshot reads, data inserted after snapshot creation is not visible.
• For locking reads, InnoDB uses next-key/gap locks to prevent phantom inserts.

Transaction Propagation (Spring)

• REQUIRED: Method B joins A’s transaction if present; otherwise creates its own.
• REQUIRES_NEW: Always creates a new transaction regardless of caller.