Why MySQL Returns Old Values After Update: Inside InnoDB Snapshot Reads

1. Problem Background

During a night, a business reported that a detection order process was stuck. Investigation revealed a strange phenomenon: within a transaction, after updating a row, reading the same row returned the old value.

The business logic proceeds in three steps:

User selects a batch of detection order IDs whose initial status is 10.

The backend service fetches exception information and updates the status to 20 for orders without exceptions.

Further business logic is executed based on the order status.

All steps run inside a single transaction.

Example SQL sequence (T0‑T3) demonstrates the update and subsequent select:

# T0 select exception records
select id, exception_type, qc_order_id
from qc_order_exception
where qc_order_id in (1001, 1002);

# T1 update status to 20
update qc_order
set status = 20
where id in (1001, 1002);

# T2 read the rows after update
select id, status
from qc_order
where id in (1001, 1002);

# T3 business logic based on status
if (qcOrder.getStatus() == 20) {
    log.info("Logic 1");
} else {
    log.info("Logic 2");
}

After adding an alert on the "Logic 2" branch, the system showed that orders whose status was correctly updated to 20 still sometimes read back as 10 during the same transaction.

2. Possible Scenarios

2.1 Master‑Slave Replication Lag?

If the read at T2 is performed on a replica, replication lag could return stale data. However, debugging showed that both T1 and T2 use the same data source (Druid connection pool), so replication lag is ruled out.

2.2 Transaction Not Effective?

If a transaction were not effective, concurrent transactions could overwrite each other. Debugging confirmed that all operations run inside a Spring @Transactional method, and the thread stack contains TransactionInterceptor.invoke, proving a single transaction context.

In the test environment, uncommitted changes are invisible to other transactions, matching the observed behavior.

2.3 Snapshot Read?

ReadView

With isolation level REPEATABLE‑READ, the first SELECT creates a ReadView. The ReadView contains:

m_ids – list of active transaction IDs at creation time.

min_trx_id – smallest active transaction ID.

max_trx_id – next global transaction ID (max active ID + 1).

creator_trx_id – ID of the transaction that created the ReadView.

Snapshot Read Process

InnoDB locates the latest version of the row.

It traverses the Undo Log chain to find a version visible to the current ReadView, applying the following rules:

If DB_TRX_ID == creator_trx_id, the version is visible.

If DB_TRX_ID < min_trx_id, the version is visible.

If DB_TRX_ID >= max_trx_id, the version is invisible.

If DB_TRX_ID is in m_ids, the version is invisible.

If a visible version is found, its data is returned; otherwise no data is returned.

When an UPDATE is executed, the row’s DB_TRX_ID should be set to the current transaction ID, making the row visible to the same transaction. In the incident, the row was sometimes read from an older version, indicating that DB_TRX_ID was not updated.

3. Update Execution Flow

3.1 Source Code Analysis

Simplified flow extracted from mysql_update()

⚠️ MySQL checks whether the row changes before performing the actual update via compare_records(). If no change is detected, the update is skipped.

How compare_records() determines changes

If the storage engine supports partial column reads, only the columns to be updated are compared, with special handling for NULL.

For engines that require full row reads:

Fixed‑length columns are compared with memcmp.

NULL columns are compared; any difference returns “not equal”.

All columns to be updated are compared one by one.

3.2 Insights

If a row’s values do not change after the UPDATE, MySQL skips the storage‑engine update, leaving DB_TRX_ID unchanged.

Concurrent processes may update the same row to the same value, causing MySQL to consider the row unchanged and skip the update.

4. Reproducing the Scenario

4.1 Table Structure and Data

create table qc_order_exception (
    id int not null primary key auto_increment comment 'id',
    qc_order_id int not null comment '检测单id',
    exception_type tinyint comment '异常类型',
    create_time datetime not null default current_timestamp comment '创建时间',
    update_time datetime not null default current_timestamp on update current_timestamp comment '更新时间'
) engine=InnoDB comment '检测单异常记录';

create table qc_order (
    id int not null primary key auto_increment comment 'id',
    status int not null comment '状态',
    create_time datetime not null default current_timestamp comment '创建时间',
    update_time datetime not null default current_timestamp on update current_timestamp comment '更新时间'
) engine=InnoDB comment '检测单表';

insert into qc_order(id, status) values (1001, 10), (1002, 10);

4.2 Steps

Record initial state

select id, status, create_time, update_time from qc_order;

T0 (Transaction A) – Begin and create ReadView

begin;
select id, exception_type, qc_order_id
from qc_order_exception
where qc_order_id in (1001, 1002);
-- returns empty
select trx_id, trx_state, trx_mysql_thread_id, trx_query
from information_schema.INNODB_TRX;

Result shows Transaction A with thread ID 7 and temporary trx_id 421164174274160.

T1 (Transaction B) – Update id=1001 and commit

begin;
update qc_order set status = 20 where id = 1001;
select trx_id, trx_state, trx_mysql_thread_id, trx_query from information_schema.INNODB_TRX;
commit;

Result: Update matched 1 row, affected 1 row. Transaction B has trx_id 4364.

T2 (Transaction A) – Update ids 1001,1002

update qc_order set status = 20 where id in (1001, 1002);
select trx_id, trx_state, trx_mysql_thread_id, trx_query from information_schema.INNODB_TRX;

Result: Update matched 2 rows but only 1 row was changed; trx_id for Transaction A changed to 4366.

T3 (Transaction A) – Query ids 1001,1002

select id, status, create_time, update_time from qc_order where id in (1001, 1002);

Result shows id=1001 with status = 10 (old value).

Using the undrop-for-innodb tool to inspect the DB_TRX_ID of each row confirms that id=1001 was updated by Transaction B (trx_id 4364) and id=1002 by Transaction A (trx_id 4366).

5. Summary

Why can an UPDATE sometimes not be visible to the same transaction? If another concurrent transaction updates the same row to the same value before the original transaction’s ReadView is created, MySQL may skip the update because the data appears unchanged. The row’s DB_TRX_ID remains unchanged, making the row invisible to the original transaction’s snapshot read.

How to avoid this “update disappearance” scenario?

Lock the rows before updating, e.g., SELECT ... FOR UPDATE, to prevent concurrent modifications.

Write the intended values into the row before the subsequent read.

Use optimistic locking by adding a version column and incrementing it on each update.

References:

https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html

https://github.com/twindb/undrop-for-innodb