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

            Activity

              People

              Assignee:
              greenman Ian Gilfillan
              Reporter:
              dinary dinary
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration