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

innodb_snapshot_isolation=1 gives error for not committed row changes

Details

    Description

      When running under innodb_snapshot_isolation=1, InnoDB gives error for conflicts that are not yet committed.

      The following example shows this issue:
      Note that if one removes the last SELECT statement or replaces that with an 'UPDATE SET b=3 where a=2', one will not get an error for the last update. Instead the last update would hang, which is what is expected.

      --source include/have_innodb.inc
       
      connect con1,localhost,root,,;
      set @@innodb_snapshot_isolation=1;
      connection default;
      set @@innodb_snapshot_isolation=1;
       
      create table t1 (a int primary key auto_increment, b int) engine=innodb;
      insert into t1 values (1,1),(2,2);
      begin;
      update t1 set b=3 where a=1;
      connection con1;
      begin;
      select * from t1;
      update t1 set b=4 where a=1;
      

      produces

      mysqltest: At line 15: query 'update t1 set b=4 where a=1' failed: ER_CHECKREAD (1020): Record has changed since last read in table 't1'
      

      Note that connection default has not done a commit or rollback yet.
      What I would expect is that the last update should hang until default has done a commit or rollback.

      This issue was reported by a user started to get ER_CHECKREAD errors when upgrading to 11.6.2.
      I think that changing default of innodb_snapshot_isolation is the wrong thing to do for MariaDB as this will break any application that uses incremental updates (very typical in data warehouse applications) to increase performance:
      UPDATE warehouse set inventory=inventory-1 where item="pc";

      innodb_snapshot_isolation is a good thing to have, but probably not for the current MariaDB user base.

      Attachments

        Issue Links

          Activity

            People

              vlad.lesin Vladislav Lesin
              monty Michael Widenius
              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.