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

            Ascending order - Click to sort in descending order
            zhuangliu Zhuang Liu created issue -
            zhuangliu Zhuang Liu made changes -
            Field Original Value New Value
            Environment Ubuntu 20.04 Ubuntu 22.04
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.2 [ 28603 ]
            serg Sergei Golubchik made changes -
            Assignee Marko Mäkelä [ marko ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6 [ 24028 ]
            Affects Version/s 10.11 [ 27614 ]
            Affects Version/s 11.0 [ 28320 ]
            Affects Version/s 11.1 [ 28549 ]
            Affects Version/s 11.2 [ 28603 ]
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] Needs Feedback [ 10501 ]
            serg Sergei Golubchik made changes -
            Status Needs Feedback [ 10501 ] Open [ 1 ]
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Vladislav Lesin [ vlad.lesin ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            Summary Phantom rows caused by update statements which changes value of the primary key Phantom rows caused by UPDATE of PRIMARY KEY
            marko Marko Mäkelä made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.0 [ 28320 ]
            Fix Version/s 11.1 [ 28549 ]
            Fix Version/s 11.3 [ 28565 ]
            Fix Version/s 11.4 [ 29301 ]
            vlad.lesin Vladislav Lesin made changes -
            Assignee Vladislav Lesin [ vlad.lesin ] Marko Mäkelä [ marko ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Vladislav Lesin [ vlad.lesin ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            vlad.lesin Vladislav Lesin made changes -
            Assignee Vladislav Lesin [ vlad.lesin ] Marko Mäkelä [ marko ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            marko Marko Mäkelä made changes -
            issue.field.resolutiondate 2024-03-20 08:30:34.0 2024-03-20 08:30:34.198
            marko Marko Mäkelä made changes -
            Fix Version/s 10.6.18 [ 29627 ]
            Fix Version/s 10.11.8 [ 29630 ]
            Fix Version/s 11.0.6 [ 29628 ]
            Fix Version/s 11.1.5 [ 29629 ]
            Fix Version/s 11.2.4 [ 29631 ]
            Fix Version/s 11.4.2 [ 29633 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.0 [ 28320 ]
            Fix Version/s 11.1 [ 28549 ]
            Fix Version/s 11.3 [ 28565 ]
            Fix Version/s 11.2 [ 28603 ]
            Fix Version/s 11.4 [ 29301 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Axel Schwenke [ axel ]
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            marko Marko Mäkelä made changes -
            Status Stalled [ 10000 ] In Testing [ 10301 ]
            axel Axel Schwenke made changes -
            Attachment tpcc1.pdf [ 73303 ]
            axel Axel Schwenke made changes -
            Status In Testing [ 10301 ] Stalled [ 10000 ]
            axel Axel Schwenke made changes -
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            axel Axel Schwenke made changes -
            Attachment tpcc1.pdf [ 73303 ]
            axel Axel Schwenke made changes -
            Attachment tpcc1.pdf [ 73306 ]
            greenman Ian Gilfillan made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -

            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.