Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5491 CREATE OR REPLACE TABLE
  3. MDEV-5602

CREATE OR REPLACE obtains stricter locks than the connection had before

    XMLWordPrintable

Details

    • Technical task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 10.0.8
    • None
    • None

    Description

      In the test case below, connection 'default' locks the table t1 with WRITE lock and re-creates the table using CREATE OR REPLACE. After that and until it performs UNLOCK, other connections cannot do SHOW TABLE (or new connections with auto-rehash cannot be established, etc.):

      show processlist;
      Id	User	Host	db	Command	Time	State	Info	Progress
      3	root	localhost	test	Query	0	init	show processlist	0.000
      4	root	localhost	test	Query	5	Waiting for table metadata lock	show create table t1	0.000

      If CREATE OR REPLACE is supposed to restore the locks that the connection had before, currently it's not the case – the locks become stricter.

      --enable_connect_log
       
      create table t1 (i int);
       
      --connect (con1,localhost,root,,)
      show create table t1;
       
      --connection default
      lock table t1 write;
       
      --connection con1
      --echo # SHOW CREATE works (as it should) even though the table is write-locked: 
      show create table t1;
       
      --connection default
      create or replace table t1 (j int);
       
      --connection con1
      send show create table t1;
       
      --connection default
      --sleep 5
      --echo # SHOW CREATE now hangs even though the other connection
      --echo #  is supposed to have the same locks as before:
      show processlist;
      --echo # We won't wait forever
       
      unlock tables;
       
      --connection con1
      --reap
      --disconnect con1
       
      --connection default
      drop table t1;

      revision-id: monty@askmonty.org-20140202093802-a6jtuy6ni29biigz
      revno: 3968
      branch-nick: mariadb-monty

      Note:
      It also causes appearance of a strange lock mode in metadata_lock_info:

      MariaDB [test]> select * from information_schema.metadata_lock_info;
      Empty set (0.00 sec)
       
      MariaDB [test]> lock table t1 write;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> select * from information_schema.metadata_lock_info;
      +-----------+-------------------------+---------------+----------------------+--------------+------------+
      | THREAD_ID | LOCK_MODE               | LOCK_DURATION | LOCK_TYPE            | TABLE_SCHEMA | TABLE_NAME |
      +-----------+-------------------------+---------------+----------------------+--------------+------------+
      |         8 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT  | Global read lock     |              |            |
      |         8 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT  | Schema metadata lock | test         |            |
      |         8 | MDL_EXCLUSIVE           | MDL_EXPLICIT  | Table metadata lock  | test         | t1         |
      +-----------+-------------------------+---------------+----------------------+--------------+------------+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> create or replace table t1 (i int);
      Query OK, 0 rows affected (1.08 sec)
       
      MariaDB [test]> select * from information_schema.metadata_lock_info;
      +-----------+-------------------------+---------------+----------------------+--------------+------------+
      | THREAD_ID | LOCK_MODE               | LOCK_DURATION | LOCK_TYPE            | TABLE_SCHEMA | TABLE_NAME |
      +-----------+-------------------------+---------------+----------------------+--------------+------------+
      |         8 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT  | Global read lock     |              |            |
      |         8 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT  | Schema metadata lock | test         |            |
      |         8 | MDL_STATEMENT           | MDL_EXPLICIT  | Table metadata lock  | test         | t1         |
      +-----------+-------------------------+---------------+----------------------+--------------+------------+
      3 rows in set (0.00 sec)

      As I understand, MDL_STATEMENT is a duration, not a mode, it should not even appear in LOCK_MODE column.

      Attachments

        Activity

          People

            monty Michael Widenius
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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