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

              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.