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

Inconsistent behaviors of UPDATE under RU & RC isolation level

    XMLWordPrintable

    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

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              dinary dinary
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration