Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
11.2.2, 10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL)
-
Ubuntu 22.04
Description
Under REPEATABLE-READ isolation level,an UPDATE statement which update the value of primary key caused phantom rows in another transaction.
How to repeat:
/* init */ CREATE TABLE t(a INT PRIMARY KEY, b INT); |
/* init */ INSERT INTO t VALUES (1, 1); |
/* init */ INSERT INTO t VALUES (2, 2); |
/* t1 */ SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
/* t2 */ SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|
/* t1 */ BEGIN; |
/* t1 */ SELECT * FROM t LOCK IN SHARE MODE; |
/* t2 */ BEGIN; |
/* t2 */ SELECT * FROM t; -- [(1, 1), (2, 2)] |
/* t1 */ UPDATE t SET a=3 WHERE b = 2; |
/* t1 */ COMMIT; |
/* t2 */ UPDATE t SET b=3; |
/* t2 */ SELECT * FROM t; -- [(1, 3), (2, 2), (3, 3)] |
/* t2 */ COMMIT; |
It appears that a phantom row (2, 2) showed up in the second consistent read of T2. And if you commit the second transaction, the phantom row will disappear. I'm not sure whether this is a new bug or a duplicate one. From the user's perspective, I haven't inserted a new row, updating existing rows should not result in phantom rows.
Attachments
Issue Links
- blocks
-
MDEV-33802 Weird read view after ROLLBACK of other transactions.
- Closed
- relates to
-
MDEV-33762 Document innodb_snapshot_isolation
- Closed
-
MDEV-35124 Set innodb_snapshot_isolation=ON by default
- Closed
-
MDEV-14589 InnoDB should not lock a delete-marked record
- Closed
-
MDEV-26642 Weird SELECT view when a record is modified to the same value by two transactions
- Closed
-
MDEV-26643 Inconsistent behaviors of UPDATE under RU & RC isolation level
- Closed
-
MDEV-33455 skip modified rows in parallel XA replication, don't wait
- Open
-
MDEV-35140 Support innodb-snapshot-isolation in Galera cluster
- In Progress