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

Phantom rows caused by UPDATE of PRIMARY KEY

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

        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
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34496 select wrong value after update statement when another session update the same row

          • 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.
          • Stalled

          Activity

            Transition Time In Source Status Execution Times
            Marko Mäkelä made transition -
            Open Needs Feedback
            		2d 7h 2m 1
            Sergei Golubchik made transition -
            Needs Feedback Open
            		39d 7m 1
            Marko Mäkelä made transition -
            Open In Progress
            		7d 19h 19m 1
            Marko Mäkelä made transition -
            Stalled In Progress
            		11d 19h 26m 1
            Marko Mäkelä made transition -
            In Progress In Review
            		2d 4h 51m 2
            Vladislav Lesin made transition -
            In Review Stalled
            		49d 20h 27m 2
            Marko Mäkelä made transition -
            Closed Stalled
            		1d 5h 36m 1
            Marko Mäkelä made transition -
            Stalled In Testing
            		5s 1
            Axel Schwenke made transition -
            In Testing Stalled
            		1d 3h 57m 1
            Axel Schwenke made transition -
            Stalled Closed
            		2h 9m 2

            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.