Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.2.2, 12.2.2
-
None
-
None
-
Ubuntu 20.04
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.
/* 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(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; |