MySQL – Why CREATE TABLE IF NOT EXISTS Locks a Table

MySQL

We recently run into "waiting for table metadata lock" issues, after "CREATE TABLE IF NOT EXISTS" has been fired several hundred times per second.

This is not a request for help (obviously we refactored that code), but I try to understand why that command locks table metadata. It was my understanding that this would be a read-only operation if the table indeed does exist.

Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6

Thanks

Best Answer

The locks in mysql are un-necessarily restrictive before it even checks if the table already exists. The bug report, that seems to be being ignored by mysql but has been fixed on a fork of mysql, explains it pretty well:

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.

https://bugs.mysql.com/bug.php?id=63144

The fork that fixed it by using the (rather obvious) solution of doing a shared lock to see if it exists, then if it did not exist upgrading to an exclusive lock.

REASON #1

This could be a bug that never got squashed out of MySQL 5.5.17

Nov 08, 2011 : http://bugs.mysql.com/bug.php?id=63144 (Status is Needs Triage on MySQL 5.5.17)
Mar 21, 2011 : http://bugs.mysql.com/bug.php?id=60563 (MySQL 5.5.10)
Jun 23, 2006 : http://bugs.mysql.com/bug.php?id=20662

REASON #2

You are trying to acquire a metadata lock on an InnoDB table in an RDS environment? Ouch !!!

Possible Workaround

Instead of trusting the metadata locking in RDS, ask the information_schema if the table exists already. Suppose the table is mydb.backupfiles. Run this query:

SELECT COUNT(1) FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='backupfiles';

If the answer comes back 0, run CREATE TABLE backupfiles .... Otherwise, don't run it.

Possible Solution

Upgrade to the Latest MySQL (5.6.12) or the Latest MySQL5.5 (5.5.31). Hopefully, this issue was solved in a more recent version

Give it a Try !!!

UPDATE 2013-07-11 15:16 EDT

Here is a rewrite of your bash code to check if the table already exists

    echo "CREATE DATABASE IF NOT EXISTS $DATABASE;" > /tmp/runthis.sql
    echo "use $DATABASE;" >> /tmp/runthis.sql
    SQLSTMT="SELECT COUNT(1) FROM information_schema.tables"
    SQLSTMT="${SQLSTMT} WHERE table_schema='${DATABASE}' AND table_name='backupfiles';"
    TABLE_EXISTS=`mysql -h portal-rds -u $user --password=$mysqlpw -ANe"${SQLSTMT}"`
    if [[ ${TABLE_EXISTS} -eq 0 ]]; then
      SQLSTMT="CREATE TABLE backupfiles (fileName VARCHAR(20),"
      SQLSTMT="${SQLSTMT} archiveId VARCHAR(500), checkSum VARCHAR(100),"
      SQLSTMT="${SQLSTMT} glacierVault VARCHAR(100), timeStamp date);"
      echo "${SQLSTMT}" >> /tmp/runthis.sql
    fi
    SQLSTMT="INSERT INTO backupfiles VALUES ('$archive_file_name',"
    SQLSTMT="${SQLSTMT} '$archiveID', '$CURRENTVAULT', '$checkSum', CURDATE());"
    echo "${SQLSTMT}" >> /tmp/runthis.sql
    echo "COMMIT;" >> /tmp/runthis.sql
    mysql -h portal-rds -u $user --password=$mysqlpw < /tmp/runthis.sql

Mysql – Why does Deadlock occur for this INSERT/UPDATE combination despite seemingly holding an X lock

Can some or all of the 4 steps be replaced by a single query:

INSERT ... ON DUPLICATE KEY UPDATE ...

or maybe

INSERT IGNORE ... and check rows_affected?

It would be better if you showed us all the SQL between BEGIN and COMMIT.

Are you doing any time-consuming processing between BEGIN and COMMIT? Can any of it be moved out. The faster the transaction runs, the less likely it will deadlock.

Do you have code to rerun the transaction when it deadlocks? This a useful "last resort". (It is not yet obvious whether the particular deadlock can always be avoided.)