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

    XMLWordPrintable

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.
          • In Progress

          Activity

            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.