Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5.12
-
OS: Ubuntu-20.04
Description
If a transaction modifies an inserted record by a concurrent transaction,
the visibility of this new record for the current transaction depends on the value of the inserted record.
This issue is similar to the issue reported in
https://jira.mariadb.org/browse/MDEV-26643
but the trigger conditions and behaviors of them are different.
How to repeat:
/* init */ drop table if exists t; |
/* init */ create table t(a int, b int); |
/* init */ insert into t values (0, 0), (1, 1); |
|
|
/* s1 */ begin; |
/* s1 */ select * from t; -- [(0, 0), (1, 1)] |
/* s2 */ begin; |
/* s2 */ insert into t values (10, 2); |
/* s2 */ commit; |
/* s1 */ select * from t; -- [(0, 0), (1, 1)] |
/* s1 */ update t set a = 10 where true; |
/* s1 */ select * from t; -- [(10, 0), (10, 1)] |
/* s1 */ commit; |
|
|
-------------------------------------------------------------
|
|
|
/* init */ drop table if exists t; |
/* init */ create table t(a int, b int); |
/* init */ insert into t values (0, 0), (1, 1); |
|
|
/* s1 */ begin; |
/* s1 */ select * from t; -- [(0, 0), (1, 1)] |
/* s2 */ begin; |
/* s2 */ insert into t values (11, 2); |
/* s2 */ commit; |
/* s1 */ select * from t; -- [(0, 0), (1, 1)] |
/* s1 */ update t set a = 10 where true; |
/* s1 */ select * from t; -- [(10, 0), (10, 1), (10, 2)] |
/* s1 */ commit; |
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
I created a .test file for mtr out of this:
--source include/have_innodb.inc
|
|
|
create table t(a int, b int) engine=innodb; |
insert into t values (0, 0), (1, 1); |
|
|
begin; select * from t; |
|
|
--connect con1,localhost,root
|
insert into t values (10,2); |
|
|
--connection default
|
update t set a = 10; |
select * from t; |
commit; |
select * from t; |
|
|
truncate table t; |
insert into t values (0, 0), (1, 1); |
|
|
begin; select * from t; |
--connection con1
|
insert into t values (11,2); |
--disconnect con1
|
--connection default
|
|
|
update t set a = 10; |
select * from t; |
commit; |
select * from t; |
|
|
drop table t; |
With the fix of MDEV-26642, MDEV-26643, MDEV-32898 (PR#3067) and with innodb_snapshot_isolation=ON, the test will fail like this:
|
10.6 b8a671988954870b7db22e20d1a1409fd40f8e3d |
mysqltest: At line 12: query 'update t set a = 10' failed: ER_CHECKREAD (1020): Record has changed since last read in table 't'
|
If I leave innodb_snapshot_isolation=OFF but change line 20 to
set innodb_snapshot_isolation=ON;begin; select * from t; |
then the second example will fail as follows:
|
10.6 b8a671988954870b7db22e20d1a1409fd40f8e3d |
mysqltest: At line 26: query 'update t set a = 10' failed: ER_CHECKREAD (1020): Record has changed since last read in table 't'
|
I suspect that the root cause of this the way how InnoDB read views work. I posted some explanation and executable test cases in
MDEV-26642.