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

'create temporary table as select' generates unnecessary table locks

    XMLWordPrintable

Details

    • 10.1.18

    Description

      A 'create temporary table as select' statement seems to produce an unnecessary table lock (which applies across all connections) against the table identifier. This can cause deadlocks in a simple case and in the more complex case (where the select is marked 'for update') can produce a ER_LOCK_WAIT_TIMEOUT error. Attached is a sql script to set up the database for reproduction. Call the procedure `sp_test` from two connections to see the error.

      Breaking the statement into separate 'create' and 'insert' statements produces the expected locking behaviour whereby neither thread is held up.

      Tested against mariadb 10.1.17 on ubuntu 16.04.1 LTS.

      Attachments

        1. test.sql
          0.6 kB
          Matthew Dean

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              matthew-dean-hpe Matthew Dean (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.