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

Table is locked for update with Row Constructor Expression Optimization

    Details

      Description

      `Row Constructor Expression Optimization` doesn't work as expected. During the raw update with `Row Constructor Expression Optimization` the whole table is locked.

      This query locks the whole table with `repeatable read` transaction (even so it updates a single row).
      ```
      update mytable set mycolumn=321 where (left_pk, right_pk) = (123, 456);
      ```

      This query works as expected and locks only a single row with `repeatable read` transaction:
      ```
      update mytable set mycolumn=321 where left_pk = 123 and right_pk = 456;
      ```

      Expected result:
      Both queries should lock only a single row.

      An example which shows the bug:

      1. Step one. Create table:

      CREATE TABLE IF NOT EXISTS mytable (
      left_pk bigint,
      right_pk bigint,
      mycolumn bigint,
      PRIMARY KEY (left_pk, right_pk)
      ) ENGINE = InnoDB;

      2. Step two. insert valudes into table:

      INSERT INTO mytable(left_pk, right_pk, mycolumn) values(1,1,1);
      INSERT INTO mytable(left_pk, right_pk, mycolumn) values(2,2,2);
      INSERT INTO mytable(left_pk, right_pk, mycolumn) values(3,3,3);

      3. Step three. Set repeatable read transaction isolation level for the session 1:

      SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

      4. Step four. Open another session with database and set repeatable read transaction isolation level for the session 2:

      SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

      5. Step five. Begin a new transaction in the session 1 and update a single raw using "row constructor expression optimization":

      start transaction;

      update mytable set mycolumn=444 where (left_pk, right_pk) = (1, 1);

      6. Step six. Go into session 2 and execute an update query for another raw:

      update mytable set mycolumn=555 where (left_pk, right_pk) = (2, 2);

      As a result step 6 is locked and will through the lock timeout exception after the timeout (by default 60 seconds):

      ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

      Expected behavior:
      Step 5 should not acquire locks for all raws. Step 6 should be processed immediately without a deadlock while transaction in step 5 is open.

        Attachments

          Activity

            People

            • Assignee:
              svoj Sergey Vojtovich
              Reporter:
              porunov Oleksandr Porunov
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: