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

ER_CHECKREAD 1020 on concurrent UPDATE under SERIALIZABLE

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 12.2.2
    • N/A
    • MariaDB Server 12.2.2
      Storage engine: InnoDB
      Isolation level: SERIALIZABLE
      Table engine: InnoDB
      innodb_snapshot_isolation = ON
    • Not for Release Notes

    Description

      I found a behavior difference under SERIALIZABLE isolation with concurrent UPDATE statements.

      In this case, Transaction 1 updates a non-primary-key column of all rows, while Transaction 2 concurrently updates the primary key of one of the rows. Transaction 2 waits for Transaction 1 to commit, but in MariaDB 12.2.2 it may fail with:

      ```text
      ERROR 1020 (HY000): Record has changed since last read in table 't0'
      ```

      Minimal reproducible test case:

      ```sql
      DROP TABLE IF EXISTS t0;

      CREATE TABLE t0(
      c0 INT,
      c1 INT,
      c2 INT PRIMARY KEY
      );

      INSERT IGNORE INTO t0(c0, c1, c2) VALUES (-1, 0, 1);
      INSERT IGNORE INTO t0(c0, c1, c2) VALUES (2, 3, 4);
      ```

      Connection 1:

      ```sql
      SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
      BEGIN;
      UPDATE t0 SET c0 = 6 WHERE 4;
      ```

      Connection 2:

      ```sql
      SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
      BEGIN;
      UPDATE t0 SET c2 = 2 WHERE c2 = 1;
      ```

      Then in Connection 1:

      ```sql
      COMMIT;
      ```

      Observed behavior in MariaDB 12.2.2:

      After Connection 1 commits, the UPDATE in Connection 2 fails with:

      ```text
      ERROR 1020 (HY000): Record has changed since last read in table 't0'
      ```

      Expected behavior:

      The UPDATE in Connection 2 should wait until Connection 1 commits, then continue execution and update the row whose primary key is `c2 = 1` to `c2 = 2`.

      The expected final table state is:

      ```text
      c0 c1 c2
      6 0 2
      6 3 4
      ```

      Schedule:

      ```text
      T1 BEGIN
      T2 BEGIN
      T1 UPDATE t0 SET c0 = 6 WHERE 4
      T2 UPDATE t0 SET c2 = 2 WHERE c2 = 1
      T1 COMMIT
      T2 COMMIT
      ```

      This concurrent execution is equivalent to the serial order:

      ```text
      T1 -> T2
      ```

      because Transaction 1 only changes `c0`, while Transaction 2 changes the primary key `c2` of the row where `c2 = 1` after Transaction 1 commits.

      Attachments

        Activity

          People

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