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

Absence of expected locks result in different table states under RC

    XMLWordPrintable

    Details

      Description

      When two transactions are committed concurrently under the Read Committed isolation level, an UPDATE is not blocked even it conflicts with the UPDATE of another concurrent transaction from the user's point of view, resulting in a different result from the counterpart under the Repeatable Read isolation level.

      create table t(a int, b int);
      insert into t values(null, 1), (null, 2), (null, 3);
       
      /* s1 */ begin; 
      /* s1 */ update t set a = 10; 
      /* s2 */ begin; 
      /* s2 */ update t set b = 20 where a;
      /* s1 */ commit;
      /* s2 */ commit;
      

      ==================== Read Committed ==========================

      the UPDATE of s2 is NOT blocked and the final table state is

      mysql> select * from t;
      +------+------+
      | a    | b    |
      +------+------+
      |   10 |    1 |
      |   10 |    2 |
      |   10 |    3 |
      +------+------+
      

      =================== Repeatable Read ==========================

      the UPDATE of s2 is blocked and the final table state is

      mysql> select * from t;
      +------+------+
      | a    | b    |
      +------+------+
      |   10 |   20 |
      |   10 |   20 |
      |   10 |   20 |
      +------+------+
      

        Attachments

          Activity

            People

            Assignee:
            greenman Ian Gilfillan
            Reporter:
            dinary dinary
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration