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

Table is locked for update with Row Constructor Expression Optimization

    XMLWordPrintable

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 values 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

            Unassigned Unassigned
            porunov Oleksandr Porunov
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.