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
Triage: Needs Triage: D3 (Medium)

[8 Nov 2011 10:46] Michael Skulsky
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 ...
[8 Nov 2011 11:09] Valeriy Kravchuk
Thank you for the problem report. Verified with 5.5.17.
[8 Nov 2012 8:56] Kaczmarek michal
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
[12 Dec 2012 13:48] Sinisa Milivojevic
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.
[11 Apr 2013 11:32] Alex Davies
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
[20 Jun 2013 15:09] Paul Dubois
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.
[2 Aug 2013 14:03] Laurynas Biveinis
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.
[22 Oct 2014 13:23] Nyaapa Arseny
What about fix for 5.5?
[10 Mar 2017 13:11] Kapil Bhagchandani
This issue still exists with Mysql 5.6.33-0ubuntu0.14.04.1