Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.6
-
Ubuntu 20.04
Description
How to repeat:
/* init */ CREATE TABLE t(a INT, b INT); |
/* init */ INSERT INTO t VALUES (null, 1), (1, 1); |
/* s1 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; |
/* s2 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; |
|
/* s1 */ BEGIN; |
/* s2 */ BEGIN; |
/* s1 */ UPDATE t SET b=3; |
/* s2 */ UPDATE t SET b=2 WHERE a; |
/* s1 */ UPDATE t SET a=1; |
/* s1 */ COMMIT; |
/* s2 */ COMMIT; |
|
/* s1 */ SELECT * FROM t; -- actual: [(1, 3), (1 ,2)], expected: [(1, 2), (1, 2)] |
The UPDATE statement of s2 was initially blocked by s1. It recovered after s1's COMMIT, but its query result is incorrect, even though in innodb_snapshot_isolation = ON mode. I found that this case is very similar to MDEV-26643, but MDEV-26643 has already been fixed in innodb_snapshot_isolation = ON mode by disabling semi-consistent read.
Attachments
Issue Links
- relates to
-
MDEV-26643 Inconsistent behaviors of UPDATE under RU & RC isolation level
-
- Closed
-
Here is my mtr version of this:
--source include/have_innodb.inc
--connect con1,localhost,root
--connection default
let $wait_condition=
--source include/wait_condition.inc
--connection con1
--reap
--disconnect con1
--connection default
With both values of innodb_snapshot_isolation, I get the same result (1,3),(1,2). I get the same result also at the stronger isolation levels REPEATABLE READ and SERIALIZABLE. As far as I understand, the expectation is that the second UPDATE is blocked by the first transaction, and it would then overwrite all changes of the first transaction. We fail to do this for the first record, on which the second transaction was blocked.