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

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33802 Weird read view after ROLLBACK of other transactions.

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-33762 Document innodb_snapshot_isolation

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-35124 Set innodb_snapshot_isolation=ON by default

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14589 InnoDB should not lock a delete-marked record

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26642 Weird SELECT view when a record is modified to the same value by two transactions

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26643 Inconsistent behaviors of UPDATE under RU & RC isolation level

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-33455 skip modified rows in parallel XA replication, don't wait

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35140 Support innodb-snapshot-isolation in Galera cluster

          • Major - Major loss of function.
          • In Progress

          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.