MySQL Bugs: #63144: CREATE TABLE IF NOT EXISTS metadata lock is too restrictive

Bug #63144	CREATE TABLE IF NOT EXISTS metadata lock is too restrictive
Submitted:	8 Nov 2011 10:46	Modified:	20 Jun 2013 15:09
Reporter:	Michael Skulsky	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.5.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A CREATE TABLE IF NOT EXISTS requires an exclusive metadata lock even if a table is really there. So if you select from a table within a transaction, then it is already impossible to run CREATE TABLE IF NOT EXISTS from another connection.
It is better to acquire a shared lock on metadata lock in this case, like 2 concurrent SELECT statements do.

How to repeat:
Connection 1:
mysql> create table tbl(a int);
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tbl;
Empty set (0.00 sec)

mysql>

Connection 2:

mysql> create table if not exists tbl(a int);

... hangs ...

Thank you for the problem report. Verified with 5.5.17.

Hello,

Can you please provide a status on this issue? When is it going to be resolved?

I am developing and maintaining an application which suffers from this problem.

Issue description:

In certain circumstances the application establishes 2 connections to the same MySQL database. These connections query the data simultaneously.
Connection 1: SELECT * FROM TABLE_X
Connection 2: CREATE TABLE_Y

Result of which is the 'Waiting for table metadata lock' problem. The only cure for this when it happens is to stop and start the server.

The workaround for us would be to run everything on on single connection in that case, but we get into other trouble then.

Thanks,
Michal

Sorry, it is impossible to inform you when will this bug be fixed, for two reasons. 

First one is that it was not decided yet and second is Oracle policy, which is very sound. It is based on the fact that if we promise to have it fixed in the version xx.yy.zz and it does not get fixed, then it could lead to failed expectations and failed planning.

There is also a possibility that this bug can not be fixed, id est that it is expected behavior. Please read the comments in the duplicate bug #67873. However, this will require re-thinking of how are certain locks taken, so it is possible that this behavior gets changed. But, not likely in 5.5, IMHO.

This bug has been fixed by Percona (although, not as of yet, in their actual binaries: https://bugs.launchpad.net/percona-server/5.5/+bug/1127008

Noted in 5.6.13, 5.7.2 changelogs.

If one session had any metadata lock on a table, another session
attempting CREATE TABLE [IF NOT EXISTS] for the same table would 
hang. This occurred due to an attempt in the second session to
acquire an exclusive metadata lock on the table before checking
whether the table already existed. An exclusive metadata lock is not
compatible with any other metadata locks, so the session hung for the
lock timeout period if another session had the table locked.
      
Now the server attempts to acquire a shared metadata lock on the
table first to check whether it exists, then upgrade to an exclusive
lock if it does not. If the table does exist, an error occurs for
CREATE TABLE and a warning for CREATE TABLE IF NOT EXISTS.

revno: 5160
committer: Praveenkumar Hulakund <praveenkumar.hulakund@oracle.com>
branch nick: mysql-5.6
timestamp: Fri 2013-05-24 15:26:03 +0530
message:
  Bug#13418638 - CREATE TABLE IF NOT EXISTS METADATA LOCK IS
                 TOO RESTRICTIVE 
  
  Analysis:
  ---------
  Creating table using "CREATE TABLE [IF NOT EXISTS]" hangs
  if table with same name already exists and if any other MDL
  lock is taken on the lock object of the table.
  
  While creating the table, we try to acquire "X" MDL lock on 
  the lock object of the table. Lock will be acquired on table,
  before even checking the existence of the table. Since "X"
  is not compatible with any other MDL locks, if some other
  lock is granted on the lock object of table then "X' lock
  will not be granted and it will hang for lock timeout
  period. 
  
  Fix:
  ----
  Acquiring "X" MDL lock before even checking for the existence
  of table is resulting in the issue mentioned above. So, as 
  a fix for this bug, modified a logic to acquire "S" lock and
  check for the existence of the table. If the table does not
  exists then upgrade lock to "X" and continue. If table exists
  then,
        for "CREATE TABLE" - report an error.
        for "CREATE TABLE IF NOT EXISTS" - Push a warning.
  and return.

What about fix for 5.5?

This issue still exists with Mysql 5.6.33-0ubuntu0.14.04.1