When discussing database transactions, ACID properties (Atomicity, Consistency, Isolation, Durability) are fundamental. This exploration focuses on the 'I' – Isolation.

Concurrent transaction execution can lead to issues like dirty reads, non-repeatable reads, and phantom reads. Transaction isolation levels are designed to mitigate these problems. A crucial trade-off exists: higher isolation levels generally decrease performance. Therefore, finding a balance is often necessary.

The SQL standard defines four transaction isolation levels:

• Read Uncommitted: Changes made by a transaction are visible to other transactions before the changes are committed.
• Read Committed: Changes made by a transaction are not visible to other transactions until the transaction is committed.
• Repeatable Read: Within a single transaction, all reads will consistently return the same data. Changes made by the transaction are not visible to other transactions until committed.
• Serializable: This level uses locking for both reads and writes. Write operations acquire exclusive locks, and read operations acquire shared locks. If lock conflicts arise, subsequent transactions must wait for the preceding transaction to complete and release its locks.

Let's illustrate these levels with an example. Consider a table 'T' with a single integer column. Initially, it contains a row with the value 1. The following demonstrates the sequence of operations for two transactions:

CREATE TABLE T(c INT) ENGINE = InnoDB;
INSERT INTO T(c) VALUES(1);
 

The outcome for Transaction A (values of V1, V2, and V3) varies based on the isolation level:

• Read Uncommitted: Transaction B's update is visible to A even before commit. V1, V2, and V3 will all be 2.
• Read Committed: B's update is visible only after commit. V1 will be 1; V2 and V3 will be 2.
• Repeatable Read: A sees consistent data throughout its execution. V1 and V2 will be 1; V3 will be 2.
• Serializable: Transaction B's update is blocked by a lock until Transaction A commits. V1 and V2 will be 1; V3 will be 2.

In implementation, databases often use a "view" of the data. For 'Repeatable Read', this view is created when the transaction starts and persists. For 'Read Committed', a new view is generated for each SQL statement. 'Read Uncommitted' directly accesses latest values without a view concept. 'Serializable' relies on explicit locking.

Implementing Transaction Isolation

Let's delve deeper into the implementation, focusing on 'Repeatable Read'.

MySQL's InnoDB engine records rollback information for each data update. This allows retrieving previous states of a value. If a value changes sequentially from 1 to 2, then 3, and finally 4, the rollback logs would store the operations to revert these changes.

The current value might be 4, but different transactions, based on their start times and respective "read-views," might see the value as 1, 2, or 4. This capability of a record having multiple versions accessible at different points in time is known as Multi-Version Concurrency Control (MVCC).

To reconstruct the value 1 for a specific read-view, InnoDB would apply the necessary rollback operations from the current state. Crucially, these operations do not conflict with other ongoing transactions (e.g., one changing 4 to 5), as they operate on different versions.

Rollback logs are retained as long as there's an active read-view that might need them. Once no read-view references older versions, the logs are purged.

Snapshot Implementation in MVCC

Under the 'Repeatable Read' isolation level, a transaction effectively takes a "snapshot" of the entire database upon its initiation. This might seem resource-intensive for large databases, but it's efficiently managed.

InnoDB assigns a unique, monotonically increasing transaction id (trx_id) to each transaction upon its start. Each row also maintains versions, each tagged with the trx_id of the transaction that created it (row_trx_id). Older versions are preserved, and the current version contains pointers to facilitate accessing them.

Consider a row updated multiple times. Each update creates a new version, tagged with the respective trx_id. For instance, V4 might be the latest version, created by transaction 25 (row_trx_id = 25). Versions V1, V2, and V3 are not physically separate copies but are derived by applying rollback operations from V4, as needed.

The "snapshot" for a transaction's consistency is defined by its "read-view." This read-view consists of:

• An array of transaction ids for all currently active (started but not committed) transactions at the moment the transaction began.
• A minimum transaction id (low watermark).
• A maximum transaction id plus one (high watermark) that represents the next available transaction id.

A data version's visibility to the current transaction is determined by its row_trx_id relative to the read-view:

• Green Area: If row_trx_id is less than or equal to the low watermark, or if it's the transaction's own ID, the version is visible (committed or created by the current transaction).
• Red Area: If row_trx_id is greater than the high watermark, the version was created by a future transaction and is invisible.
• Yellow Area: This covers cases where row_trx_id falls between the low and high watermarks.
  • If the row_trx_id is present in the active transactions array, the version is from an uncommitted transaction and is invisible.
  • If the row_trx_id is NOT in the active transactions array, it means the transaction committed before the read-view was established, and the version is visible.

Case Studies

Case 1: Consistent Read Verification

Assumptions:

1. Before Transaction A starts, only active transaction with ID 99 exists.
2. Transactions A, B, C have IDs 100, 101, 102 respectively. These are the only active transactions.
3. Before these transactions, a row (1, 1) has a row_trx_id of 90.

Transaction A's read-view array is [99, 100]. B's is [99, 100, 101]. C's is [99, 100, 101, 102].

Let's trace the operations relevant to Transaction A's query:

• Transaction C updates the row from (1, 1) to (1, 2). The new version has row_trx_id = 102. The version with row_trx_id = 90 becomes historical.
• Transaction B updates the row from (1, 2) to (1, 3). The new version has row_trx_id = 101. The version with row_trx_id = 102 becomes historical.

When Transaction A queries:

• It encounters the version with row_trx_id = 101. This ID is greater than A's high watermark (102), so it's in the red zone and invisible.
• It checks the next historical version, row_trx_id = 102. This is also greater than the high watermark and invisible.
• It finds the version with row_trx_id = 90. This ID is less than A's low watermark (99), placing it in the green zone and making it visible.

Thus, Transaction A consistently reads the value 1, demonstrating a consistent read.

Case 2: Current Read vs. Snapshot Read

Consider a table 'T' initialized with two rows:

CREATE TABLE `T` (
 `id` INT(11) NOT NULL,
 `k` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE = InnoDB;
INSERT INTO T(id, k) VALUES(1, 1), (2, 2);
 

Key points:

• begin/start transaction does not mark the transaction start; the first DML statement does. start transaction with consistent snapshot explicitly sets the start time.
• Transaction C's UPDATE is auto-committed.
• Transaction B updates and then queries; Transaction A queries in a read-only transaction after B's query.

If Transaction B reads 3 and Transaction A reads 1, it might seem confusing. Transaction B's read-view is established before C commits. However, when B performs its UPDATE, it cannot operate on a historical version to avoid losing C's update. This is where "current read" (current read) comes into play.

For an update, InnoDB first performs a "current read" to get the latest committed value. In this case, it reads (1, 2) (from C's update). Then, it applies the increment, creating a new version (1, 3) with row_trx_id = 101 (B's ID). When B queries immediately after its update, it sees its own transaction's most recent version (k=3).

Transaction A, having started with a consistent snapshot before C's commit, will eventually see the state *before* C's update, resulting in k=1 for its consistent read.

Case 3: Lock Wait with Uncommitted Changes

Consider a modification where Transaction C' doesn't commit immediately after its update:

Here, Transaction C' has updated the row to (1, 2) but hasn't committed. Its write lock on this version is still held. Transaction B attempts its "current read" for an update. Since C' holds a lock on the latest version, B's update operation (which involves a read) will be blocked until C' releases its lock.

This scenario illustrates the interaction between consistent reads, current reads, and row locks.

Case 4: Read Committed Isolation Level

How would Transaction A and Transaction B see the values under the 'Read Committed' isolation level? The key difference is when the read-view is created.

In 'Read Committed', Transaction A's read-view is generated *at the moment its SELECT statement executes*. At this point, both (1, 2) (from C) and (1, 3) (from B) have been generated. However:

• (1, 3) is part of an uncommitted transaction (B), so it's invisible to A's read-view.
• (1, 2) was committed by C before A's SELECT.

Therefore, Transaction A's query returns k=2. Transaction B's query returns k=3 (as it's a current read within its own transaction).

Summary

InnoDB's MVCC allows multiple versions of data rows, each with a row_trx_id. Transactions operate with consistency views. Consistent reads use these views to determine data version visibility:

• Repeatable Read: Considers data committed before the transaction started.
• Read Committed: Considers data committed before the statement started.

Current reads always fetch the latest committed version. Updates involve a current read and may acquire row locks if conflicts arise.

The primary distinction between 'Repeatable Read' and 'Read Committed' lies in read-view generation:

• Repeatable Read: A single read-view is generated at the transaction's start for all subsequent queries within that transaction.
• Read Committed: A new read-view is generated before each statement execution.