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

Explicit lock mode not taken into account while choosing implicit algorithm

    XMLWordPrintable

    Details

      Description

      When the algorithm is not specified in an ALTER operation (and alter_algorithm variable value remains default DEFAULT), ALTER is supposed to be executed with the best algorithm available for the operation.

      However, if locking mode is specified as a part of the operation, it is apparently ignored at the time when the algorithm is being selected. It makes ALTER later be rejected even if there is an algorithm available for the operation and for the specified locking mode.

      create table t (a int);
      alter table t rename to t2, lock=shared, algorithm=copy;
      alter table t2 rename to t, lock=shared;
       
      # Cleanup
      drop table if exists t, t2;
      

      10.3 be99d0dd

      alter table t rename to t2, lock=shared, algorithm=copy;
      alter table t2 rename to t, lock=shared;
      mysqltest: At line 3: query 'alter table t2 rename to t, lock=shared' failed: 1845: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE
      

      So, ALTER ... RENAME is executed fine when ALGORITHM=COPY is specified explicitly along with LOCK=SHARED; but if only LOCK=SHARED is specified, ALTER doesn't consider COPY as a viable algorithm option and refuses operation claiming that LOCK=SHARED is impossible.

      I think a case can be made that in some situations it might be preferable to allow ALTER take longer if necessary, but reduce the amount of locking to minimal, so using LOCK=SHARED would be a reasonable choice. With implementation of online alter (MDEV-16329) the case becomes stronger, as LOCK=NONE can make bigger difference and be more frequently desired than LOCK=SHARED.

      Adding explicit ALGORITHM=COPY in this case is a weak workaround, as it enforces the copying even if the user doesn't need it (and it may happen that at some point a better algorithm emerges which could be used if COPY wasn't explicitly specified).

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.