Why MySQL Updates Sometimes Disappear: InnoDB Read View Explained

Problem Background

During a night‑time operation a detection order got stuck: after updating a row inside a transaction, a subsequent read returned the old value.

Possible Scenarios

Master‑slave replication delay.

Transaction not taking effect due to concurrent modifications.

Snapshot (repeatable‑read) read returning an older version.

Replication delay was ruled out (same connection pool) and all operations run inside a single Spring @Transactional context.

Business Logic Flow

Select detection order IDs with initial status 10.

If no exception records exist for those IDs, update status to 20.

Further processing branches on the final status.

All steps execute within one transaction.

InnoDB Read‑View Mechanics (MySQL 5.7.44, REPEATABLE‑READ)

When a transaction starts, InnoDB creates a ReadView containing: m_ids: list of active transaction IDs at creation time. min_trx_id: smallest active transaction ID. max_trx_id: next transaction ID to be assigned. creator_trx_id: ID of the transaction that created the view.

Visibility rules for a row version ( DB_TRX_ID) are:

If DB_TRX_ID == creator_trx_id, the version is visible (written by the current transaction).

If DB_TRX_ID < min_trx_id, the version is visible (committed before the view).

If DB_TRX_ID >= max_trx_id or DB_TRX_ID is in m_ids, the version is invisible.

Root Cause Analysis

MySQL’s compare_records() function skips the storage‑engine update when the new row values are identical to the old ones. In that case the row’s DB_TRX_ID is not changed.

When a second transaction updates the same row to the same final value before the first transaction’s UPDATE runs, the first transaction’s update is skipped. Because the row’s DB_TRX_ID remains the earlier transaction’s ID, the snapshot created by the first transaction cannot see the newer version, leading to the “update disappears” phenomenon.

Reproduction Scenario

# T0 – Transaction A starts and creates a ReadView
BEGIN;
SELECT id, exception_type, qc_order_id FROM qc_order_exception WHERE qc_order_id IN (1001,1002);

# T1 – Transaction B updates id=1001 and commits
BEGIN;
UPDATE qc_order SET status = 20 WHERE id = 1001;
COMMIT;

# T2 – Transaction A updates both ids
UPDATE qc_order SET status = 20 WHERE id IN (1001,1002);

# T3 – Transaction A reads the rows
SELECT id, status FROM qc_order WHERE id IN (1001,1002);

Result:

Transaction B reports Rows matched: 1, Changed: 1.

Transaction A reports Rows matched: 2, Changed: 1 – only id=1002 was actually updated.

When Transaction A reads the rows, id=1001 still shows status = 10.

Using the undrop‑for‑innodb tool to inspect DB_TRX_ID:

Record id=1001 has DB_TRX_ID = 4364 (Transaction B).

Record id=1002 has DB_TRX_ID = 4366 (Transaction A).

Step‑by‑Step Reproduction

Create tables qc_order_exception and qc_order and insert two rows with status = 10.

Begin Transaction A, select exception records (none), note its transaction ID.

In a separate session, begin Transaction B, update id=1001 to 20, commit.

Back in Transaction A, run UPDATE qc_order SET status = 20 WHERE id IN (1001,1002); and then SELECT id, status FROM qc_order WHERE id IN (1001,1002);.

Observe that id=1001 still reports status = 10.

Optionally use undrop‑for‑innodb to dump DB_TRX_ID from the .ibd file and verify which transaction wrote each row.

Recommendations

Lock rows before updating, e.g., SELECT ... FOR UPDATE, to prevent concurrent updates that could cause a no‑op.

Include the updated values directly in subsequent reads instead of issuing a separate SELECT.

Apply optimistic locking with a version column that increments on each update.

References

MySQL InnoDB Consistent Read Documentation: https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html

undrop‑for‑innodb GitHub Repository: https://github.com/twindb/undrop-for-innodb