Description:
In READ UNCOMMITTED (or READ COMMITTED), InnoDB is documented to use "semi-consistent reads" for UPDATE full table scans. If a row is locked but doesn't match the WHERE clause, it should be skipped.
We have found that this optimization is unreliable and non-deterministic. Even with identical data and execution order, the UPDATE statement sometimes correctly "slides past" the lock and sometimes "hangs" on it. This flakiness suggests a race condition in how InnoDB evaluates row visibility or lock status during the "semi-consistent" check.
How to repeat:
/* init */ CREATE TABLE mtest(
c0 VARCHAR(8) NOT NULL,
c1 VARCHAR(15) NOT NULL,
c2 INT PRIMARY KEY,
c3 INT,
c4 CHAR(15) DEFAULT NULL
);
INSERT INTO mtest(c0, c1, c2) VALUES ("", "TRUE", 57);
INSERT IGNORE INTO mtest(c4, c1, c0, c2) VALUES ("]3", "O", "", 63);
INSERT INTO mtest(c4, c1, c0, c2) VALUES ("Q0C&f", "", "35", 63);
INSERT INTO mtest(c0, c1, c2) VALUES ("", "-oP8%jo", 17);
INSERT IGNORE INTO mtest(c3, c4, c0, c1, c2) VALUES (77, "", "", "", 52);
INSERT INTO mtest(c1, c2, c0) VALUES ("t", 85, "E");
/* s2 */SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
/* s2 */ BEGIN;
/* s1 */SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
/* s1 */BEGIN;
/* s1 */ SELECT c4, c0, c1, c2 FROM mtest WHERE "" LIMIT 1 LOCK IN SHARE MODE; -- Returns empty, no row lock.
/* s2 */ SELECT c3, c4, c0 FROM mtest WHERE c0 = 'P' LIMIT 2; -- Reads Row A.
/* s1 */ SELECT c4, c1 FROM mtest WHERE c0 = 'P' LOCK IN SHARE MODE;
-- s1 acquires S-Lock on Row A (c2=25).
/* s2 */ UPDATE mtest SET c4 = 'W', c2 = 71 WHERE c4 = '';
-- [CRITICAL POINT]
-- Optimization Expectation: Row A (c4=' ]p%') does NOT match 'WHERE c4='''.
-- s2 should perform a semi-consistent read, see the non-match, and SKIP Row A without blocking.
-- Observed Inconsistency: s2 BLOCKS here waiting for s1's S-Lock on Row A.
/* s2 */ COMMIT;
/* s1 */ ROLLBACK;
Suggested fix:
We suggest the following for your consideration:
Ensure Deterministic Behavior: UPDATE should consistently skip locks on non-matching rows in RU/RC, regardless of timing or transaction history.
Documentation Update: If this is a known technical limitation, we suggest clarifying in the docs that semi-consistent reads are "best-effort" rather than guaranteed.
Thank you for your time and help!
Description: In READ UNCOMMITTED (or READ COMMITTED), InnoDB is documented to use "semi-consistent reads" for UPDATE full table scans. If a row is locked but doesn't match the WHERE clause, it should be skipped. We have found that this optimization is unreliable and non-deterministic. Even with identical data and execution order, the UPDATE statement sometimes correctly "slides past" the lock and sometimes "hangs" on it. This flakiness suggests a race condition in how InnoDB evaluates row visibility or lock status during the "semi-consistent" check. How to repeat: /* init */ CREATE TABLE mtest( c0 VARCHAR(8) NOT NULL, c1 VARCHAR(15) NOT NULL, c2 INT PRIMARY KEY, c3 INT, c4 CHAR(15) DEFAULT NULL ); INSERT INTO mtest(c0, c1, c2) VALUES ("", "TRUE", 57); INSERT IGNORE INTO mtest(c4, c1, c0, c2) VALUES ("]3", "O", "", 63); INSERT INTO mtest(c4, c1, c0, c2) VALUES ("Q0C&f", "", "35", 63); INSERT INTO mtest(c0, c1, c2) VALUES ("", "-oP8%jo", 17); INSERT IGNORE INTO mtest(c3, c4, c0, c1, c2) VALUES (77, "", "", "", 52); INSERT INTO mtest(c1, c2, c0) VALUES ("t", 85, "E"); /* s2 */SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; /* s2 */ BEGIN; /* s1 */SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; /* s1 */BEGIN; /* s1 */ SELECT c4, c0, c1, c2 FROM mtest WHERE "" LIMIT 1 LOCK IN SHARE MODE; -- Returns empty, no row lock. /* s2 */ SELECT c3, c4, c0 FROM mtest WHERE c0 = 'P' LIMIT 2; -- Reads Row A. /* s1 */ SELECT c4, c1 FROM mtest WHERE c0 = 'P' LOCK IN SHARE MODE; -- s1 acquires S-Lock on Row A (c2=25). /* s2 */ UPDATE mtest SET c4 = 'W', c2 = 71 WHERE c4 = ''; -- [CRITICAL POINT] -- Optimization Expectation: Row A (c4=' ]p%') does NOT match 'WHERE c4='''. -- s2 should perform a semi-consistent read, see the non-match, and SKIP Row A without blocking. -- Observed Inconsistency: s2 BLOCKS here waiting for s1's S-Lock on Row A. /* s2 */ COMMIT; /* s1 */ ROLLBACK; Suggested fix: We suggest the following for your consideration: Ensure Deterministic Behavior: UPDATE should consistently skip locks on non-matching rows in RU/RC, regardless of timing or transaction history. Documentation Update: If this is a known technical limitation, we suggest clarifying in the docs that semi-consistent reads are "best-effort" rather than guaranteed. Thank you for your time and help!