Description:
In READ COMMITTED (RC) isolation, when an UPDATE statement is blocked by another transaction, it is required to re-evaluate the target rows once the lock is released. However, a failure in this re-evaluation logic causes the resumed UPDATE to silently skip rows that were modified by the blocking transaction to match the filter criteria. This leads to a Lost Update anomaly.
This issue is a violation of the Read Committed isolation semantics during row re-evaluation.
When Transaction s2 (RC) is blocked by Transaction s1, InnoDB is supposed to re-scan the record once the lock is released to check if it still matches the WHERE clause based on the newly committed version (Semi-Consistent Read / Row Re-evaluation).
In this trace:
s1 modified Row 1 so that c3 = ''.
s2 was searching for c3 = '' and got blocked by s1's lock on Row 1.
After s1 committed, s2 resumed.
The Bug: s2 failed to "see" that Row 1 now matches its criteria and silently skipped the update for this row.
This "silent skip" results in an inconsistent database state. The presence of the prefix index c0(5) and the concurrent update to other columns in the same index may be interfering with the cursor's ability to correctly re-evaluate the row after the lock is released.
How to repeat:
/* --- 1. Initialization --- */
DROP TABLE IF EXISTS mtest;
CREATE TABLE mtest(c0 VARCHAR(7) DEFAULT NULL, c1 FLOAT DEFAULT NULL, c2 INT, c3 TEXT);
INSERT INTO mtest(c1) VALUES (0.3830294021405778);
INSERT INTO mtest(c1, c2) VALUES (0.10366036771819787, 49);
INSERT IGNORE INTO mtest(c3, c0, c1, c2) VALUES ("恈F}", "ss", 0.9312915170983301, 54);
INSERT INTO mtest(c3) VALUES ("");
CREATE INDEX i0 ON mtest (c0(5), c1, c2);
/* s1 */ BEGIN;
/* s2 */ BEGIN;
/* s1 */ SELECT c3, c0, c1, c2 FROM mtest WHERE c2 = 49 LIMIT 2 LOCK IN SHARE MODE;
/* s1 */ -- Update Row 1: Sets c3 to "", which matches s2's future criteria
/* s1 */ UPDATE mtest SET c3 = "", c1 = 0.704839902710349, c2 = 37 WHERE (ABS(c1 - 0.383029) < 0.000010);
/* s2 */ SELECT c3 FROM mtest WHERE c2 = 54 LIMIT 3;
/* s2 */ -- Attempts to update rows where c3 is empty.
/* s2 */ -- This statement will BLOCK because Row 1 (modified by s1) now has c3 = ""
/* s2 */ UPDATE mtest SET c3 = "", c2 = 66 WHERE c3 = ''; -- (BLOCKED)
/* s1 */ SELECT c3, c0, c1, c2 FROM mtest WHERE (c2)IS NULL LOCK IN SHARE MODE;
/* s1 */ UPDATE mtest SET c3 = "/P8vV㕶濲L]", c0 = "-sD{BG?", c1 = 0.19454098257880525, c2 = 65 WHERE c0 = 'ss';
/* s1 */ COMMIT;
/* s2 */ -- s2 RESUMES here after s1 releases the lock on Row 1.
/* s2 */ COMMIT;
Actual Final State (Observed):
-- Row 1: [null, 0.70484, 37, ""]
-- Note: Row 1's c2 is 37. It was NOT updated by s2.
FinalState: [null, 0.70484, 37, , null, 0.10366, 49, null, -sD{BG?, 0.194541, 65, /P8vV㕶濲L], null, null, 66, ]
Expected Final State (Inferred):
-- Row 1: [null, 0.70484, 66, ""]
-- Note: Row 1's c2 should have been updated to 66 by s2 after s1 committed.
FinalState: [null, 0.70484, 66, , null, 0.10366, 49, null, -sD{BG?, 0.194541, 65, /P8vV㕶濲L], null, null, 66, ]
© 2026, Oracle Corporation and/or its affiliates
Description: In READ COMMITTED (RC) isolation, when an UPDATE statement is blocked by another transaction, it is required to re-evaluate the target rows once the lock is released. However, a failure in this re-evaluation logic causes the resumed UPDATE to silently skip rows that were modified by the blocking transaction to match the filter criteria. This leads to a Lost Update anomaly. This issue is a violation of the Read Committed isolation semantics during row re-evaluation. When Transaction s2 (RC) is blocked by Transaction s1, InnoDB is supposed to re-scan the record once the lock is released to check if it still matches the WHERE clause based on the newly committed version (Semi-Consistent Read / Row Re-evaluation). In this trace: s1 modified Row 1 so that c3 = ''. s2 was searching for c3 = '' and got blocked by s1's lock on Row 1. After s1 committed, s2 resumed. The Bug: s2 failed to "see" that Row 1 now matches its criteria and silently skipped the update for this row. This "silent skip" results in an inconsistent database state. The presence of the prefix index c0(5) and the concurrent update to other columns in the same index may be interfering with the cursor's ability to correctly re-evaluate the row after the lock is released. How to repeat: /* --- 1. Initialization --- */ DROP TABLE IF EXISTS mtest; CREATE TABLE mtest(c0 VARCHAR(7) DEFAULT NULL, c1 FLOAT DEFAULT NULL, c2 INT, c3 TEXT); INSERT INTO mtest(c1) VALUES (0.3830294021405778); INSERT INTO mtest(c1, c2) VALUES (0.10366036771819787, 49); INSERT IGNORE INTO mtest(c3, c0, c1, c2) VALUES ("恈F}", "ss", 0.9312915170983301, 54); INSERT INTO mtest(c3) VALUES (""); CREATE INDEX i0 ON mtest (c0(5), c1, c2); /* s1 */ BEGIN; /* s2 */ BEGIN; /* s1 */ SELECT c3, c0, c1, c2 FROM mtest WHERE c2 = 49 LIMIT 2 LOCK IN SHARE MODE; /* s1 */ -- Update Row 1: Sets c3 to "", which matches s2's future criteria /* s1 */ UPDATE mtest SET c3 = "", c1 = 0.704839902710349, c2 = 37 WHERE (ABS(c1 - 0.383029) < 0.000010); /* s2 */ SELECT c3 FROM mtest WHERE c2 = 54 LIMIT 3; /* s2 */ -- Attempts to update rows where c3 is empty. /* s2 */ -- This statement will BLOCK because Row 1 (modified by s1) now has c3 = "" /* s2 */ UPDATE mtest SET c3 = "", c2 = 66 WHERE c3 = ''; -- (BLOCKED) /* s1 */ SELECT c3, c0, c1, c2 FROM mtest WHERE (c2)IS NULL LOCK IN SHARE MODE; /* s1 */ UPDATE mtest SET c3 = "/P8vV㕶濲L]", c0 = "-sD{BG?", c1 = 0.19454098257880525, c2 = 65 WHERE c0 = 'ss'; /* s1 */ COMMIT; /* s2 */ -- s2 RESUMES here after s1 releases the lock on Row 1. /* s2 */ COMMIT; Actual Final State (Observed): -- Row 1: [null, 0.70484, 37, ""] -- Note: Row 1's c2 is 37. It was NOT updated by s2. FinalState: [null, 0.70484, 37, , null, 0.10366, 49, null, -sD{BG?, 0.194541, 65, /P8vV㕶濲L], null, null, 66, ] Expected Final State (Inferred): -- Row 1: [null, 0.70484, 66, ""] -- Note: Row 1's c2 should have been updated to 66 by s2 after s1 committed. FinalState: [null, 0.70484, 66, , null, 0.10366, 49, null, -sD{BG?, 0.194541, 65, /P8vV㕶濲L], null, null, 66, ]