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

ERROR 1020 after no-op UPDATE and concurrent INSERT under SERIALIZABLE

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 12.2.2
    • None
    • None

    Description

      I found a possible false positive ERROR 1020 in MariaDB 12.2.2 under SERIALIZABLE isolation with innodb_snapshot_isolation=ON.

      In this test case, Transaction 1 first executes a no-op UPDATE with WHERE FALSE, which matches and changes no rows. Then Transaction 2 inserts a new row and commits. After that, Transaction 1 executes another UPDATE on an existing row, but it fails with:
      ERROR 1020 (HY000): Record has changed since last read in table 't0'

      I think this behavior is unexpected because the first UPDATE in Transaction 1 does not read or modify any row, and the concurrent INSERT in Transaction 2 does not conflict with the row updated by Transaction 1. The final state can be serialized.

      Here is the test case:
      /* init */ DROP TABLE IF EXISTS t0;
      /* init */ CREATE TABLE t0(c0 INT);
      /* init */ INSERT INTO t0(c0) VALUES(1);
      /* init */ INSERT INTO t0(c0) VALUES(2);

      /* t1 */ BEGIN;
      /* t1 */ UPDATE t0 SET c0=-1 WHERE false;
      /* t2 */ BEGIN;
      /* t2 */ INSERT INTO t0(c0) VALUES(3);
      /* t2 */ COMMIT;
      /* t1 */ UPDATE t0 SET c0=3 WHERE c0=1;
      ERROR 1020 (HY000): Record has changed since last read in table 't'
      /* t1 */ COMMIT;
      Expected result:
      Transaction 1 should not fail with ERROR 1020.The first UPDATE in Transaction 1 is a no-op and does not modify any row. Transaction 2 only inserts a new row. There is no unique constraint on c0, so duplicate values are allowed.
      A valid serial execution exists. For example, if Transaction 2 is serialized before Transaction 1:

      Initial state:

      {1, 2}

      After Transaction 2:

      {1, 2, 3}

      After Transaction 1:

      {3, 2, 3}

      So the expected final result should be:
      c0
      –
      2
      3
      3
      The observed ERROR 1020 seems to be a false positive. If this behavior is expected under SERIALIZABLE with innodb_snapshot_isolation=ON, please clarify why a no-op UPDATE ... WHERE FALSE can cause a later UPDATE to fail after a concurrent non-conflicting INSERT.

      Attachments

        Activity

          People

            Unassigned Unassigned
            yousaha yiran shen
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.