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

Inconsistent behaviors of UPDATE under RU & RC isolation level

Details

    Description

      Isolation Level: Read Uncommitted & Read Committed.
      The behaviors of UPDATE for different rows are inconsistent. Just some of the rows are not updated while others are.

      /* init */ drop table if exists t;
      		 
      /* init */ create table t(a int, b int);
      		 
      /* init */ insert into t values(null, 1), (2, 2), (null, null), (null, 3), (4, null);
      		 
       
      		 
      /* s1 */ begin;
      		 
      /* s1 */ update t set a = 10 where 1;
      		 
      /* s2 */ begin;
      		 
      /* s2 */ update t set b = 20 where a; -- blocked
      		 
      /* s1 */ commit; -- s2 unblocked
      		 
      /* s2 */ commit;
      		 
       
      		 
      select * from t;
      		 
       
      		 
      +------+------+
      		 
      | a    | b    |
      		 
      +------+------+
      		 
      |   10 |    1 |
      		 
      |   10 |   20 |
      		 
      |   10 |   20 |
      		 
      |   10 |   20 |
      		 
      |   10 |   20 |
      		 
      +------+------+

      The field b of the first row is not updated but other rows are updated.

      After several attempts, I found that the results depend on the distribution of the data in the table rather than the timing.

      If the initial table is like:

      +------+------+
      		 
      | a    | b    |
      		 
      +------+------+
      		 
      |    1 |    1 |
      		 
      |    2 |    2 |
      		 
      | null | null |
      		 
      | null |    3 |
      		 
      |    4 | null |
      		 
      +------+------+

      then, the result will be

      +------+------+
      		 
      | a    | b    |
      		 
      +------+------+
      		 
      |   10 |   20 |
      		 
      |   10 |   20 |
      		 
      |   10 |   20 |
      		 
      |   10 |   20 |
      		 
      |   10 |   20 |
      		 
      +------+------+

      More specifically, if there exists a row whose column `a` is not NULL, and it is the first record from top to bottom whose column `a` is not NULL. Then, all rows before it with a NULL column `a` will not be updated, while all rows after it with a NULL column `a` will be updated. This means that even the execution of a single statement is not atomic.

      This bug can be reproduced under Read Uncommitted and Read Committed.

      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-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-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-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-34108 Inappropriate semi-consistent read in RC if innodb_snapshot_isolation=ON

          • 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

            Transition Time In Source Status Execution Times
            Vladislav Lesin made transition -
            In Progress Closed
            		4d 16h 1m 1
            Marko Mäkelä made transition -
            Closed Stalled
            		4h 26m 1
            Ian Gilfillan made transition -
            Stalled Open
            		6d 18h 48m 1
            Marko Mäkelä made transition -
            Open In Progress
            		872d 8h 16m 2
            Marko Mäkelä made transition -
            In Progress Stalled
            		1d 18h 34m 1
            Marko Mäkelä made transition -
            Stalled Closed
            		26d 21h 52m 1

            People

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