Search

  • Project: All
  • Type: All
  • Status: All
  • Assignee: All
  • Label:
    isolation
AdvancedBasic
Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-36358

Read another insertion WITH CONSISTENT SNAPSHOT

Details

    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

          marko Marko Mäkelä added a comment -

          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 Marko Mäkelä added a comment - 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;

          People

            vlad.lesin Vladislav Lesin
            dlxue huicong xu
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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

                1 of 5