Reference: Xiaolin Coding

Transaction Properties

Atomicity: Implemented via undo log.

Consistency: Achieved through the other three properties. Before and after a transaction, the database transitions from one consistent state to another.

Isolation: Implemented via MVCC/locking. The execution of one transaction does not affect the execution of others.

Durability: Implemented via redo log. Once a transaction is committed, its changes to the database are permanent, even in the event of a system failure or crash.

Starting a Transaction

BEGIN or START TRANSACTION marks the beginning of a transaction. The transaction truly starts only after this command is executed and the first SELECT statement is run. START TRANSACTION WITH CONSISTENT SNAPSHOT immediately starts the transaction.

Transaction Isolation Levels

Potential issues: Dirty Read, Non-Repeatable Read, Phantom Read.

Dirty Read: Transaction A reads data that Transaction B has not yet committed. If Transaction B rolls back, Transaction A will have read incorrect data.

Non-Repeatable Read: Within a single transaction, reading the same data twice produces different values. For example, Transaction A reads a as 1, then Transaction B modifies a to 2 and commits the change. When Transaction A reads a again, it finds the value is now 2.

Phantom Read: Within a single transaction, querying the number of records that meet a certain condition twice yields different results. This can severely degrade database performance under concurrency.

Read Uncommitted: Highest performance, worst consistency. Almost never used in production. No locks or MVCC needed; directly reads the latest data.

Read Committed: Uses row-level locks/MVCC.

Repeatable Read: The default isolation level in InnoDB.

Serializable: Read operations place shared locks on the data being read, and write operations place exclusive locks on the data being modified.

MVCC

Reference Video

The undo log has two hidden columns: trx_id (transaction ID) and roll_pointer (points to the previous version of the record).

m_ids is a list that records all currently active transaction IDs (started but not yet committed). min_trx_id is the smallest active transaction ID. max_trx_id is the largest active transaction ID (current transaction) + 1. creator_trx_id is the transaction ID that created this read view.

When performing a SELECT, for any given row (any transaction chain), only one version can be selected.

In the Read Committed isolation level, a snapshot is generated every time the transaction reads data. In the Repeatable Read isolation level, a snapshot is generated at the beginning of the transaction.

In the transaction chain, read their transaction IDs from top to bottom:

1. Compare it with creator_trx_id. If they are equal, it means the current transaction is the creator, and it can access its own version. If not, proceed to the next step.
2. Compare it with min_trx_id. If this transaction ID is less than the smallest active transaction ID, it means it was committed before this snapshot was created, so this version of the transaction can be selected. If not, proceed to the next step.
3. Compare it with max_trx_id. If it is greater, it means this transaction was started after the snapshot was created, so it cannot be selected. Skip to the next transaction. If not, proceed to the next step.
4. If it is between min_trx_id and max_trx_id and is not in the list of active transactions, it can be selected. If not, skip to the next transaction.

Solving Phantom Reads

InnoDB’s Repeatable Read isolation level largely solves the problem of phantom reads.

For snapshot reads, the snapshot is generated when the transaction starts. Therefore, if new data is added after the transaction has started, it will not be visible in queries.

Transaction Propagation

If method A starts a transaction, and method A calls method B, the call to method B also requires a transaction.

PROPAGATION_REQUIRED: Method B can join method A’s transaction. If method A has not started a transaction, method B creates its own.

PROPAGATION_REQUIRES_NEW: A new transaction is created regardless of whether an existing transaction is present.