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
            View 13 older comments
            axel Axel Schwenke added a comment -

            The results are somewhat surprising. The baseline and innodb_snapshot_isolation=on show about the same performance where innodb_snapshot_isolation=off falls back a bit. The averages are ~5750 tps and ~5600 tps.

            I double checked the configuration and plotted even the counters for Handler_commit and Handler_rollback. But the results are real. As expected we get more rollbacks for innodb_snapshot_isolation=on.

            Attached: tpcc1.pdf

            axel Axel Schwenke added a comment - The results are somewhat surprising. The baseline and innodb_snapshot_isolation=on show about the same performance where innodb_snapshot_isolation=off falls back a bit. The averages are ~5750 tps and ~5600 tps. I double checked the configuration and plotted even the counters for Handler_commit and Handler_rollback . But the results are real. As expected we get more rollbacks for innodb_snapshot_isolation=on . Attached: tpcc1.pdf
            serg Sergei Golubchik added a comment -

            So, 3x more rollbacks (and Monty said it'll be ~3x), as expected.

            axel, how was your tps calculated? Are rollbacks count towards tps? Are rolled back transactions re-applied?

            serg Sergei Golubchik added a comment - So, 3x more rollbacks (and Monty said it'll be ~3x), as expected. axel , how was your tps calculated? Are rollbacks count towards tps? Are rolled back transactions re-applied?
            axel Axel Schwenke added a comment -

            how was your tps calculated? Are rollbacks count towards tps?

            The TPS numbers come from sysbench. It's simply the number of executions of the event() function in the LUA script. Which for TPC-C calls one of 5 functions for one of 5 transactions. Of those only the NEW ORDER transaction does an explicit ROLLBACK. And AFAICS that rollback is not connected to an SQL error, but to an empty result.

            I have now added plots of Com_commit and Com_rollback counters (in tpcc1.pdf) . If I compare the TPS plot with Com_commit the latter is a bit lower. That would mean a rollback is counted towards TPS. But this changes nothing of the throughput comparison of the 3 commits/configurations.

            The numbers for Handler_rollback are higher than those for Com_rollback - but only for the case innodb_snapshot_isolation=on. Those rollbacks must be implicit then and are ignored by sysbench. However sysbench reports those errors independently.

            For innodb_snapshot_isolation=off:

            SQL statistics:
            		 
                queries performed:
            		 
                    read:                            262257215
            		 
                    write:                           272207592
            		 
                    other:                           40437558
            		 
                    total:                           574902365
            		 
                transactions:                        20218683 (5616.26 per sec.)
            		 
                queries:                             574902365 (159693.89 per sec.)
            		 
                ignored errors:                      87982  (24.44 per sec.)
            		 
                reconnects:                          0      (0.00 per sec.)

            And innodb_snapshot_isolation=on:

            SQL statistics:
            		 
                queries performed:
            		 
                    read:                            268903843
            		 
                    write:                           279039940
            		 
                    other:                           41682778
            		 
                    total:                           589626561
            		 
                transactions:                        20730792 (5758.51 per sec.)
            		 
                queries:                             589626561 (163783.92 per sec.)
            		 
                ignored errors:                      200264 (55.63 per sec.)
            		 
                reconnects:                          0      (0.00 per sec.)

            Are rolled back transactions re-applied?

            I can see that nowhere in the LUA code.

            axel Axel Schwenke added a comment - how was your tps calculated? Are rollbacks count towards tps? The TPS numbers come from sysbench. It's simply the number of executions of the event() function in the LUA script. Which for TPC-C calls one of 5 functions for one of 5 transactions. Of those only the NEW ORDER transaction does an explicit ROLLBACK. And AFAICS that rollback is not connected to an SQL error, but to an empty result. I have now added plots of Com_commit and Com_rollback counters (in tpcc1.pdf ) . If I compare the TPS plot with Com_commit the latter is a bit lower. That would mean a rollback is counted towards TPS. But this changes nothing of the throughput comparison of the 3 commits/configurations. The numbers for Handler_rollback are higher than those for Com_rollback - but only for the case innodb_snapshot_isolation=on . Those rollbacks must be implicit then and are ignored by sysbench. However sysbench reports those errors independently. For innodb_snapshot_isolation=off : SQL statistics: queries performed: read: 262257215 write: 272207592 other: 40437558 total: 574902365 transactions: 20218683 (5616.26 per sec.) queries: 574902365 (159693.89 per sec.) ignored errors: 87982 (24.44 per sec.) reconnects: 0 (0.00 per sec.) And innodb_snapshot_isolation=on : SQL statistics: queries performed: read: 268903843 write: 279039940 other: 41682778 total: 589626561 transactions: 20730792 (5758.51 per sec.) queries: 589626561 (163783.92 per sec.) ignored errors: 200264 (55.63 per sec.) reconnects: 0 (0.00 per sec.) Are rolled back transactions re-applied? I can see that nowhere in the LUA code.
            serg Sergei Golubchik added a comment -

            I cannot explain why the number of commits stayed the same even though the number of rollbacks increased 3x

            serg Sergei Golubchik added a comment - I cannot explain why the number of commits stayed the same even though the number of rollbacks increased 3x
            marko Marko Mäkelä added a comment -

            For what it is worth, in InnoDB a rollback is like a commit, with the added step of undoing all changes. It seems to me that Com_commit is defined in com_status_vars as the following entry:

              {"commit",               STMT_STATUS(SQLCOM_COMMIT)},

            This is something that would be updated outside InnoDB. Maybe the Lua scripts keep executing COMMIT statements after ignoring ER_CHECKREAD errors, or maybe autocommit transactions that are in fact rolled back due to an error will be counted as committed.

            marko Marko Mäkelä added a comment - For what it is worth, in InnoDB a rollback is like a commit, with the added step of undoing all changes. It seems to me that Com_commit is defined in com_status_vars as the following entry: { "commit" , STMT_STATUS(SQLCOM_COMMIT)}, This is something that would be updated outside InnoDB. Maybe the Lua scripts keep executing COMMIT statements after ignoring ER_CHECKREAD errors, or maybe autocommit transactions that are in fact rolled back due to an error will be counted as committed.

            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.