[#MDEV-5602] CREATE OR REPLACE obtains stricter locks than the connection had before

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.

CREATE OR REPLACE TABLE (MDEV-5491) [MDEV-5602] CREATE OR REPLACE obtains stricter locks than the connection had before Created: 2014-02-02 Updated: 2014-02-05 Resolved: 2014-02-05
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	None
Fix Version/s:	10.0.8

[MDEV-5602] CREATE OR REPLACE obtains stricter locks than the connection had before Created: 2014-02-02 Updated: 2014-02-05 Resolved: 2014-02-05