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

Phantom rows caused by UPDATE of PRIMARY KEY

    XMLWordPrintable

Details

    Description

      Under REPEATABLE-READ isolation level,an UPDATE statement which update the value of primary key caused phantom rows in another transaction.
      How to repeat:

      /* init */ CREATE TABLE t(a INT PRIMARY KEY, b INT);
      /* init */ INSERT INTO t VALUES (1, 1);
      /* init */ INSERT INTO t VALUES (2, 2);
      /* t1 */ SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
      /* t2 */ SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
       
      /* t1 */ BEGIN;
      /* t1 */ SELECT * FROM t LOCK IN SHARE MODE;
      /* t2 */ BEGIN;
      /* t2 */ SELECT * FROM t;  -- [(1, 1), (2, 2)]
      /* t1 */ UPDATE t SET a=3 WHERE b = 2;
      /* t1 */ COMMIT;
      /* t2 */ UPDATE t SET b=3;
      /* t2 */ SELECT * FROM t; -- [(1, 3), (2, 2), (3, 3)] 
      /* t2 */ COMMIT;
      

      It appears that a phantom row (2, 2) showed up in the second consistent read of T2. And if you commit the second transaction, the phantom row will disappear. I'm not sure whether this is a new bug or a duplicate one. From the user's perspective, I haven't inserted a new row, updating existing rows should not result in phantom rows.

      Attachments

        1. tpcc1.pdf
          191 kB
          Axel Schwenke

        Issue Links

          Activity

            People

              axel Axel Schwenke
              zhuangliu Zhuang Liu
              Votes:
              1 Vote for this issue
              Watchers:
              10 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.