Details
-
Bug
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
11.2.2, 12.2.2
-
None
-
None
-
Ubuntu22.0
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, we identified a failure in this re-evaluation logic where a resumed RC transaction silently skips rows that were modified by a concurrent SERIALIZABLE transaction to match the filter criteria. This leads to a Lost Update anomaly and an inconsistent database state.
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 */ SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
/* s1 */ BEGIN; |
/* s2 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; |
/* 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 */ UPDATE mtest SET c3 = "", c2 = 66 WHERE c3 = ''; -- blocked' |
|
|
/* s1 */ UPDATE mtest SET c3 = "/P8]", 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, /P8], 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, /P8], null, null, 66, ] |