Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34108

Inappropriate semi-consistent read in RC if innodb_snapshot_isolation=ON

Details

    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

          Activity

            Here is my mtr version of this:

            --source include/have_innodb.inc
            CREATE TABLE t(a INT, b INT) ENGINE=InnoDB;
            INSERT INTO t VALUES (null, 1), (1, 1);
             
            SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
            BEGIN;
            UPDATE t SET b=3;
             
            --connect con1,localhost,root
            SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
            BEGIN;
            send UPDATE t SET b=2 WHERE a;
            --connection default
            let $wait_condition=
              select count(*) = 1 from information_schema.processlist
              where state = 'Updating' and info = 'UPDATE t SET b=2 WHERE a';
            --source include/wait_condition.inc
            UPDATE t SET a=1;
            COMMIT;
            --connection con1
            --reap
            COMMIT;
            --disconnect con1
            --connection default
            SELECT * FROM t;
            DROP TABLE t;
            

            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.

            marko Marko Mäkelä added a comment - Here is my mtr version of this: --source include/have_innodb.inc CREATE TABLE t(a INT , b INT ) ENGINE=InnoDB; INSERT INTO t VALUES ( null , 1), (1, 1);   SET TRANSACTION ISOLATION LEVEL READ COMMITTED ; BEGIN ; UPDATE t SET b=3;   --connect con1,localhost,root SET TRANSACTION ISOLATION LEVEL READ COMMITTED ; BEGIN ; send UPDATE t SET b=2 WHERE a; --connection default let $wait_condition= select count (*) = 1 from information_schema.processlist where state = 'Updating' and info = 'UPDATE t SET b=2 WHERE a' ; --source include/wait_condition.inc UPDATE t SET a=1; COMMIT ; --connection con1 --reap COMMIT ; --disconnect con1 --connection default SELECT * FROM t; DROP TABLE t; 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.

            marko

            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.

            Why do you think that the second UPDATE should be blocked on the first record (null, 3)? The update does semi-consistent read for searching for records which match the condition in WHERE, i.e. it tries to set lock, is it fails, then it reads visible to the view committed version and checks if it corresponds to the condition(row_sel_build_committed_vers_for_mysql() call). So the second UPDATE is blocked on (1,3) record, and after the first transaction is committed, the second UPDATE continues execution on (1,3) record, and changes only this record.

            If I understood correctly, this change does not cancel semi-consistent read, it just allows to read uncommitted record's version. To disable semi-consistent read we need to change conditions, under which row_sel_build_committed_vers_for_mysql() is invoked, i.e. "goto lock_wait_or_error" if prebuilt->trx->snapshot_isolation.

            vlad.lesin Vladislav Lesin added a comment - marko 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. Why do you think that the second UPDATE should be blocked on the first record (null, 3)? The update does semi-consistent read for searching for records which match the condition in WHERE, i.e. it tries to set lock, is it fails, then it reads visible to the view committed version and checks if it corresponds to the condition(row_sel_build_committed_vers_for_mysql() call). So the second UPDATE is blocked on (1,3) record, and after the first transaction is committed, the second UPDATE continues execution on (1,3) record, and changes only this record. If I understood correctly, this change does not cancel semi-consistent read, it just allows to read uncommitted record's version. To disable semi-consistent read we need to change conditions, under which row_sel_build_committed_vers_for_mysql() is invoked, i.e. "goto lock_wait_or_error" if prebuilt->trx->snapshot_isolation.

            I only conducted a quick initial analysis. I think that it could make sense to disable the semi-consistent read at a higher level when innodb_snapshot_isolation=ON.

            marko Marko Mäkelä added a comment - I only conducted a quick initial analysis. I think that it could make sense to disable the semi-consistent read at a higher level when innodb_snapshot_isolation=ON .

            The difference between this in MDEV-26643 cases is that the current case is for RC, while MDEV-26643 is for RU. But both cases are about fixing the same code.

            vlad.lesin Vladislav Lesin added a comment - The difference between this in MDEV-26643 cases is that the current case is for RC, while MDEV-26643 is for RU. But both cases are about fixing the same code.
            vlad.lesin Vladislav Lesin added a comment - - edited

            It looks like this bug is the same as MDEV-26643 for RC case.

            The difference in test cases is that MDEV-26643 test case modifies the field, which takes part in the condition in WHERE clause of the UPDATE, but the current test case modifies the field, which does not take part in the condition. So, as MDEV-26643 allows to read non-committed records for innodb_snapshot_isolation = ON mode, the UPDATE tries to lock the record, which was changed by uncommitted transaction, but it doesn't do locking read during scanning the table to match condition in WHERE clause, it still does semi-consistent non-locking read, what causes the current bug.

            I would mark it as a duplicate of MDEV-26643 and push additional fix for MDEV-26643.

            After discussion with Marko it was decided to fix it as a separate bug, because it's quite confusing to push two fixes for the same bug.

            vlad.lesin Vladislav Lesin added a comment - - edited It looks like this bug is the same as MDEV-26643 for RC case. The difference in test cases is that MDEV-26643 test case modifies the field, which takes part in the condition in WHERE clause of the UPDATE, but the current test case modifies the field, which does not take part in the condition. So, as MDEV-26643 allows to read non-committed records for innodb_snapshot_isolation = ON mode, the UPDATE tries to lock the record, which was changed by uncommitted transaction, but it doesn't do locking read during scanning the table to match condition in WHERE clause, it still does semi-consistent non-locking read, what causes the current bug. I would mark it as a duplicate of MDEV-26643 and push additional fix for MDEV-26643 . After discussion with Marko it was decided to fix it as a separate bug, because it's quite confusing to push two fixes for the same bug.

            People

              vlad.lesin Vladislav Lesin
              zhuangliu Zhuang Liu
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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