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

    Details

    • Type: Technical task
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.0.8
    • Component/s: None
    • Labels:
      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

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

              Dates

              • Created:
                Updated:
                Resolved: