Why Does Transaction A See 1 While Transaction B Sees 3? Understanding MySQL Repeatable Read

Example

We have a table:

Now three transactions A, B, C run concurrently:

What are the results of the SELECT statements in transactions A and B?

Using start transaction with consistent snapshot; starts the transaction immediately; the begin/start transaction command itself is not the start point— the transaction begins with the first InnoDB statement after it.

First we create the table and insert data:

mysql> 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);

Then we open three MySQL client terminals representing A, B, C and execute:

# client A
start transaction with consistent snapshot;

# client B
start transaction with consistent snapshot;

# client C
update t set k=k+1 where id=1;

# client B
update t set k=k+1 where id=1;
select k from t where id=1; # 3

# client A
select k from t where id=1; # 1
commit;

# client B
commit;

Result:

Transaction B's SELECT returns 3 .

Transaction A's SELECT returns 1 .

If you guessed correctly, you know why; otherwise, let’s analyze.

Analysis

1. Background

MySQL’s default isolation level is repeatable read , which takes a snapshot of the whole database when the transaction starts.

If the database is 100 GB, the snapshot is not a full copy; it is based on the version log.

In InnoDB each transaction has a unique transaction ID (transaction id) assigned at start, strictly increasing.

Each row can have multiple versions; each update creates a new version with its own row trx_id .

The dashed lines show four versions of the same row; the latest version V4 has k = 22, updated by transaction ID 25. Versions V1‑V3 are not physically stored but can be reconstructed from the undo log.

According to the definition of repeatable read, a transaction sees all results committed before it starts; updates from other transactions after it starts are invisible.

Thus a transaction declares: “I recognize only data versions committed before my start time; later versions are ignored until I see a version I recognize.”

InnoDB builds an array of all active (started but not yet committed) transaction IDs at the moment a transaction starts.

For example, transaction 15 starts while transactions 9 and 12 are active, so its array is [9,12,15] . From this array we can infer:

Events with IDs < 9 are definitely visible because they have completed.

Events with IDs > 15 are definitely invisible because they occur later.

Changes from other transactions not in the array are invisible, as they were not committed when the transaction started.

Events with IDs > 9 and < 15 that are not in the array are visible because they were committed before the transaction started (e.g., transaction 10).

Therefore InnoDB leverages the fact that all data have multiple versions to achieve instant snapshot capability.

2. Example Analysis

Returning to the earlier example, we examine the execution flow.

Assume:

Before transaction A starts, only active transaction 99 exists.

Transactions A, B, C have version numbers 100, 101, 102 respectively; only these four transactions exist.

Before the three transactions start, the row (1,1) has row trx_id = 90.

The view arrays are:

Transaction A → [99, 100]

Transaction B → [99, 100, 101]

Transaction C → [99, 100, 101, 102]

2.1 Logic of Transaction A

The first effective update is by transaction C, making the latest row trx_id = 102; version 90 becomes a historical version.

The second effective update is by transaction B, changing the latest row trx_id to 101.

When transaction A reads the data, its view array is [99,100]. The read process:

Find the current version: row trx_id = 101, larger than the max in its view array, invisible.

Check the previous version: row trx_id = 102, still invisible.

Check the older version: row trx_id = 90, smaller than the min in its view array, finally visible.

Thus transaction A’s query result is 1 .

2.2 Logic of Transaction B

For transaction B, a question arises: according to consistent read rules, why does B see 3 instead of the original (1,1) value?

This involves the principle that “updates read first then write, reading the current version,” known as “current read.”

Updates must not be applied to historical versions; otherwise transaction C’s update would be lost, so B’s update operates on version (1,2).

When B executes SELECT, it sees the current version 101, which it updated, so the result is 3 .

Conclusion

Reading data is a consistent read.

Updating data is a current read.

If the current row lock is held by another transaction, the operation waits for the lock.

Content compiled from Alibaba technology expert Ding Qi’s column “MySQL in Practice 45 Lectures”.