2

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

2

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.

New contributor
sbingner is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1

It could be as innocent as avoiding creating two tables with the same name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.