[MDEV-36358] Read another insertion WITH CONSISTENT SNAPSHOT - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 11.7.2, 10.11
Fix Version/s: 10.11
Component/s: Storage Engine - InnoDB
Labels:
- isolation
Environment:
Ubuntu 22.04

Description

When I tested serialisable isolation levels, I found a problem.
As the example below shows, the transaction reads an insert from another transaction. However, if the snapshot is created at the beginning of the transaction (), it seems to me that the transaction should not read the insert of another transaction, which is guaranteed by the weaker repeatable read isolation level.

--- session 0 (init database)

DROP TABLE tKRijnTy;

CREATE TABLE tKRijnTy (ID INT, VAL INT, c0 DOUBLE , c1 BIGINT UNSIGNED , c2 TEXT );

INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES (19, 21, 2036.4166, 7408, 'fUt');

INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES (20, 22, 7537.9400, 9201, '1459352324');

INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES (21, 23, 4034.0071, 9788, 'V');

--- session 1

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

--- session 2

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

--- session 1

START TRANSACTION WITH CONSISTENT SNAPSHOT;

--- session 2

START TRANSACTION WITH CONSISTENT SNAPSHOT;

--- session 1

INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES ( 25, 27, 3783.9088, 9258, "1459352324");

--- session 2

INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES ( 26, 28, 3740.4026, 3161, '');

COMMIT;

--- session 1

SELECT ID, VAL, tKRijnTy.c0, tKRijnTy.c1, tKRijnTy.c2 FROM tKRijnTy WHERE ( tKRijnTy.c0 > 4034.0071 OR tKRijnTy.c0 <= 7537.9400) ORDER BY tKRijnTy.ID;

+------+------+-----------+------+------------+

| ID   | VAL  | c0        | c1   | c2         |

+------+------+-----------+------+------------+

|   19 |   21 | 2036.4166 | 7408 | fUt        |

|   20 |   22 |   7537.94 | 9201 | 1459352324 |

|   21 |   23 | 4034.0071 | 9788 | V          |

|   25 |   27 | 3783.9088 | 9258 | 1459352324 |

|   26 |   28 | 3740.4026 | 3161 |            |

+------+------+-----------+------+------------+

5 rows in set (0.01 sec)

COMMIT;

Attachments

Activity

Ascending order - Click to sort in descending order

huicong xu created issue - 2025-03-24 08:04

Marko Mäkelä added a comment - 2025-03-24 13:02

Thank you for the report. I can reproduce this with both values of innodb_snapshot_isolation. The record id=26 would disappear from the results if I let the first transaction use the default isolation level REPEATABLE READ by commenting out a statement:

--source include/have_innodb.inc

CREATE TABLE tKRijnTy (ID INT, VAL INT, c0 DOUBLE , c1 BIGINT UNSIGNED , c2 TEXT ) ENGINE=InnoDB;

INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES

(19, 21, 2036.4166, 7408, 'fUt'),

(20, 22, 7537.9400, 9201, '1459352324'),

(21, 23, 4034.0071, 9788, 'V');

#SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION WITH CONSISTENT SNAPSHOT;

--connect(con2,localhost,root)

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION WITH CONSISTENT SNAPSHOT;

--connection default

INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES ( 25, 27, 3783.9088, 9258, "1459352324");

--connection con2

INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES ( 26, 28, 3740.4026, 3161, '');

COMMIT;

--disconnect con2

--connection default

SELECT ID, VAL, tKRijnTy.c0, tKRijnTy.c1, tKRijnTy.c2 FROM tKRijnTy WHERE ( tKRijnTy.c0 > 4034.0071 OR tKRijnTy.c0 <= 7537.9400) ORDER BY tKRijnTy.ID;

COMMIT;

DROP TABLE tKRijnTy;

Marko Mäkelä added a comment - 2025-03-24 13:02 Thank you for the report. I can reproduce this with both values of innodb_snapshot_isolation . The record id=26 would disappear from the results if I let the first transaction use the default isolation level REPEATABLE READ by commenting out a statement: --source include/have_innodb.inc CREATE TABLE tKRijnTy (ID INT , VAL INT , c0 DOUBLE , c1 BIGINT UNSIGNED , c2 TEXT ) ENGINE=InnoDB; INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES (19, 21, 2036.4166, 7408, 'fUt' ), (20, 22, 7537.9400, 9201, '1459352324' ), (21, 23, 4034.0071, 9788, 'V' ); # SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE ; START TRANSACTION WITH CONSISTENT SNAPSHOT; --connect(con2,localhost,root) SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE ; START TRANSACTION WITH CONSISTENT SNAPSHOT; --connection default INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES ( 25, 27, 3783.9088, 9258, "1459352324" ); --connection con2 INSERT INTO tKRijnTy (ID, VAL, c0, c1, c2) VALUES ( 26, 28, 3740.4026, 3161, '' ); COMMIT ; --disconnect con2 --connection default SELECT ID, VAL, tKRijnTy.c0, tKRijnTy.c1, tKRijnTy.c2 FROM tKRijnTy WHERE ( tKRijnTy.c0 > 4034.0071 OR tKRijnTy.c0 <= 7537.9400) ORDER BY tKRijnTy.ID; COMMIT ; DROP TABLE tKRijnTy;

Marko Mäkelä made changes - 2025-03-24 13:02

Field	Original Value	New Value
Component/s		Storage Engine - InnoDB [ 10129 ]
Fix Version/s		10.11 [ 27614 ]
Affects Version/s		10.11 [ 27614 ]
Assignee		Vladislav Lesin [ vlad.lesin ]
Labels		isolation

Marko Mäkelä made changes - 2025-03-24 13:02

Status

Open [ 1 ]

Confirmed [ 10101 ]

People

Assignee:: Vladislav Lesin

Reporter:: huicong xu

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2025-03-24 08:04

Updated:: 2025-03-24 13:02

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server