MySQL Bugs: #120610: REPEATABLE READ: no-change UPDATE causes MVCC Rule 1 violation

Bug #120610	REPEATABLE READ: no-change UPDATE causes MVCC Rule 1 violation — transaction cannot see own modification
Submitted:	3 Jun 12:00	Modified:	3 Jun 12:21
Reporter:	Hongqiang Zhang	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In REPEATABLE READ isolation level, InnoDB's MVCC Rule 1 states that a
transaction should always see its own modifications. However, when a
transaction performs an UPDATE that does not change any column values
("no-change UPDATE"), the subsequent consistent read (SELECT) within
the same transaction fails to see its own modification and instead
returns the original (pre-update) version of the row.

This bug is triggered when:
1. Transaction T1 updates a row and commits
2. Transaction T2 (REPEATABLE READ) does a SELECT, then an UPDATE
   that writes the SAME values as what T1 wrote (no-change UPDATE)
3. T2's subsequent SELECT returns the ORIGINAL values instead of
   T1's committed values or T2's own modification

How to repeat:
-- Setup
CREATE TABLE test_mvcc (
  id INT PRIMARY KEY,
  gmt_modified DATETIME,
  state VARCHAR(20)
) ENGINE=InnoDB;
INSERT INTO test_mvcc VALUES (1, '2020-01-01', 'NORMAL');

-- T1: Update row
BEGIN;
UPDATE test_mvcc SET gmt_modified='2025-06-01', state='CEASE' WHERE id=1;
COMMIT;

-- T2: No-change UPDATE (writes same values as T1)
BEGIN;
-- First SELECT creates read view
SELECT * FROM test_mvcc WHERE id=1;  -- Returns (1, 2025-06-01, CEASE) ✓

-- UPDATE with same values (no-change UPDATE)
UPDATE test_mvcc SET gmt_modified='2025-06-01', state='CEASE' WHERE id=1;

-- Second SELECT — BUG: returns original version!
SELECT * FROM test_mvcc WHERE id=1;  -- Returns (1, 2020-01-01, NORMAL) ✗
-- Expected: (1, 2025-06-01, CEASE) per MVCC Rule 1
COMMIT;

-- After T2 commits, the DB has the correct value:
SELECT * FROM test_mvcc WHERE id=1;  -- Returns (1, 2025-06-01, CEASE)

-- This proves: UPDATE wrote correctly, but in-transaction SELECT read incorrectly.

Suggested fix:
When InnoDB processes a no-change UPDATE (n_fields == 0 in update vector),
it should still ensure that DB_TRX_ID is properly updated so that MVCC
Rule 1 (changes_visible: id == creator_trx_id) works correctly.

Currently, Bug #29157 addressed the "affected rows" count issue by
returning HA_ERR_RECORD_IS_THE_SAME, but this fix did not consider
the MVCC visibility side effect. The InnoDB source code shows that
btr_cur_update_in_place calls row_upd_rec_sys_fields which should
update DB_TRX_ID, but experimental data (trx_rows_modified=0 for
no-change UPDATE) suggests the transaction's modification is not
properly registered in the MVCC version chain.

Related: Bug #29157 (HA_ERR_RECORD_IS_THE_SAME for no-change UPDATE)

Correction to "How to repeat": The bug ONLY triggers when T2's UPDATE
experiences a lock wait (blocked by T1's X lock). Without concurrent
lock wait, MVCC Rule 1 works correctly. The submitted steps are
incorrect because T1 commits before T2 starts — there is no lock wait
and the bug cannot be reproduced that way.

Correct reproduction requires TWO CONCURRENT sessions where T1 holds
an X lock while T2 attempts UPDATE:

-- Setup (any session)
CREATE TABLE test_mvcc (
  id INT PRIMARY KEY,
  gmt_modified DATETIME,
  state VARCHAR(20)
) ENGINE=InnoDB;
INSERT INTO test_mvcc VALUES (1, '2020-01-01', 'NORMAL');

-- SESSION 1 (T1): Update row and HOLD X lock (do NOT commit yet)
BEGIN;
UPDATE test_mvcc SET gmt_modified='2025-06-01', state='CEASE' WHERE id=1;
-- T1 now holds X lock. Stay in this session, do NOT commit.

-- SESSION 2 (T2): Start transaction
BEGIN;
-- First SELECT creates stale read view (T1 is still active, so T2
-- sees only the original version, not T1's uncommitted changes)
SELECT * FROM test_mvcc WHERE id=1;
-- Returns: (1, 2020-01-01, NORMAL)

-- UPDATE with same values T1 wrote — BLOCKED by T1's X lock!
UPDATE test_mvcc SET gmt_modified='2025-06-01', state='CEASE' WHERE id=1;
-- T2 waits for T1's X lock release...

-- SESSION 1 (T1): NOW commit, releasing the X lock
COMMIT;
-- T2's UPDATE resumes. Current read sees T1's committed version
-- (2025-06-01, CEASE). T2 writes same values = no-change UPDATE.

-- SESSION 2 (T2): SELECT after UPDATE — BUG!
SELECT * FROM test_mvcc WHERE id=1;
-- Returns: (1, 2020-01-01, NORMAL)  ← WRONG!
-- Expected: (1, 2025-06-01, CEASE) per MVCC Rule 1
COMMIT;

-- Verify DB has correct value after T2 commits:
SELECT * FROM test_mvcc WHERE id=1;
-- Returns: (1, 2025-06-01, CEASE) ← UPDATE wrote correctly

Summary of essential preconditions:
1. T1 holds X lock (not yet committed) when T2 starts SELECT
2. T2 SELECT creates stale read view that skips T1's uncommitted version
3. T2 UPDATE is blocked by T1's X lock (lock wait occurs)
4. T1 commits → T2 UPDATE resumes → does current read → sees T1 version
5. T2 writes SAME values as T1 → no-change UPDATE
6. T2 subsequent SELECT returns ORIGINAL version instead of own modification

Without lock wait (if T1 commits before T2 starts), this bug does NOT
trigger. Lock wait is an essential precondition, confirmed by 16
controlled experiments (tests without concurrency all PASS, tests with
concurrent lock wait all FAIL).