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

Weird SELECT view when a record is modified to the same value by two transactions

Details

    Description

      Under REPEATABLE-READ isolation level, if two transactions concurrently modify the same row to the same value, the transaction that modifies later does not see the modified content.

      /* init */ create table t(a int, b int);
      		 
      /* init */ insert into t values (0, 0), (1, 1), (2, 2);
      		 
       
      		 
      /* s1 */ begin;
      		 
      /* s1 */ select * from t; -- [(0, 0), (1, 1), (2, 2)]
      		 
      /* s2 */ begin;
      		 
      /* s2 */ update t set a = 10 where b = 1;
      		 
      /* s2 */ commit;
      		 
      /* s1 */ select * from t; -- [(0, 0), (1, 1), (2, 2)]
      		 
      /* s1 */ update t set a = 10 where true;
      		 
      /* s1 */ select * from t;  -- [(10, 0), (1, 1), (10, 2)]
      		 
      /* s1 */ commit;

      The result of final SELECT should be (10, 0), (10, 1), (10, 2).

      I think it is so weird for session 1 to see the second row is still (1, 1) after the successful execution of an UPDATE with the "WHERE TRUE" predicate.

      So I think it will be better for s1 to see all records it updates regardless of whether the values before and after the UPDATE are the same.

      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
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32898 Phantom rows caused by UPDATE of PRIMARY KEY

          • Critical - Crashes, loss of data, severe memory leak.
          • 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-35262 INT violation when two transactions modify a record to the same value concurrently

          • Major - Major loss of function.
          • Open

          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-26643 Inconsistent behaviors of UPDATE under RU & RC isolation level

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26671 Inconsistent SELECT View when modify a record added by another transaction

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29565 Inconsistent read and write, which use the same predicate (WHERE clause)

          • Major - Major loss of function.
          • Closed

          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
            dinary dinary created issue -
            dinary dinary made changes -
            Field Original Value New Value
            Labels innodb need_feedback innodb
            dinary dinary made changes -
            Epic/Theme server
            alice Alice Sherepa made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            alice Alice Sherepa made changes -
            Affects Version/s 5.5 [ 15800 ]
            Affects Version/s 10.0 [ 16000 ]
            Affects Version/s 10.1 [ 16100 ]
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6 [ 24028 ]
            alice Alice Sherepa made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            alice Alice Sherepa made changes -
            Assignee Marko Mäkelä [ marko ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Ian Gilfillan [ greenman ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 125161 ] MariaDB v4 [ 144409 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.2 [ 14601 ]
            marko Marko Mäkelä made changes -
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.3 [ 22126 ]
            marko Marko Mäkelä made changes -
            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.2.4 [ 29631 ]
            Fix Version/s 11.4.2 [ 29633 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Assignee Ian Gilfillan [ greenman ] Marko Mäkelä [ marko ]
            Resolution Fixed [ 1 ]
            Status Confirmed [ 10101 ] Closed [ 6 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.1.5 [ 29629 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Xiaozhi ZH Cai made changes -
            marko Marko Mäkelä made changes -

            People

              marko Marko Mäkelä
              dinary dinary
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.