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

The error "Record has changed since last read in table 't'" occurs at Repeatable Read

    XMLWordPrintable

Details

    Description

      Test case 1:
      Isolation Level: Repeatable Read.
      Only at the repeatable read isolation level, the DELETE statement in transaction t1 reported error "Record has changed since last read in table 't'".
      At the serializable isolation level, neither of the two transactions reported any errors.

      /* init */ DROP TABLE IF EXISTS t;
      		 
      /* init */ CREATE TABLE t (c1 INT);
      		 
      /* init */ INSERT INTO t(c1) VALUES (1);
      		 
       
      /* t1 */ BEGIN;
      		 
      /* t1 */ SELECT * FROM t;
      		 
      /* t2 */ BEGIN;
      		 
      /* t2 */ INSERT INTO t(c1) VALUES (2);
      		 
      /* t2 */ COMMIT;
      		 
      /* t1 */ DELETE FROM t ;
      		 
      ERROR 1020 (HY000): Record has changed since last read in table 't'
      		 
      /* t1 */ COMMIT;

      Test case 2:
      Isolation Level: Repeatable Read.
      At the repeatable read isolation level, the DELETE statement in transaction t1 reported error "Record has changed since last read in table 't'". 

      /* init */ DROP TABLE IF EXISTS t;
      		 
      /* init */ CREATE TABLE t (c1 INT);
      		 
      /* init */ INSERT INTO t(c1) VALUES (1);
      		 
       
      /* t1 */ BEGIN;
      		 
      /* t1 */ SELECT * FROM t;
      		 
      /* t2 */ BEGIN;
      		 
      /* t2 */ UPDATE t SET c1 = 2;
      		 
      /* t2 */ COMMIT;
      		 
      /* t1 */ DELETE FROM t ;
      		 
      ERROR 1020 (HY000): Record has changed since last read in table 't'
      		 
      /* t1 */ COMMIT;

      Isolation Level: Serializable.
      At the serializable isolation level, the UPDATE statement in transaction t2 reported error "Record has changed since last read in table 't'". 

      /* init */ DROP TABLE IF EXISTS t;
      		 
      /* init */ CREATE TABLE t (c1 INT);
      		 
      /* init */ INSERT INTO t(c1) VALUES (1);
      		 
       
      /* t1 */ BEGIN;
      		 
      /* t1 */ SELECT * FROM t;
      		 
      /* t2 */ BEGIN;
      		 
      /* t2 */ UPDATE t SET c1 = 2; -- blocked
      		 
      /* t1 */ DELETE FROM t ;
      		 
      /* t1 */ COMMIT; -- t2 released and reported error:
      		 
      ERROR 1020 (HY000): Record has changed since last read in table 't'
      		 
      /* t2 */ COMMIT;

      Could you please explain why transactions did not report an error at the SER but did at the RR level in test case 1? Why are the error statements different for different isolation levels in the test case 2?

      Attachments

        Activity

          People

            Unassigned Unassigned
            John Jove John Jove
            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.