The MySQL administration SQL command SHOW PROCESSLIST may read "Waiting for table metadata lock" in its "State" column for a statement. The statement in question is waiting for another transaction to release a metadata lock. Its a state that may appear when using the global transaction ID injection feature of PECL/mysqlnd_ms 1.2.0-alpha. But only in case of errors and if not using default settings of the plugin. In the worst case, during testing only, I experienced a deadlock with MySQL 5.5.3 or higher which never got resolved automatically.

Provoking metadata lock

Let a transaction update a record in a table. In my specific case it was an failing UPDATE. It failed because the table did not exist. Let a second transaction run a DDL statement, for example, DROP TABLE on the same table. The second transaction is now in waiting for the first transaction to release a metadata lock. Yes, the second transaction is waiting for a lock hold on a non-existing table, if you are using MySQL 5.5.3 or higher… The SQL is syntactically correct, thus the lock is acquired.

$link = new mysqli($host, $user, $passwd, $db, $port, $socket);
$link->autocommit(false);
$link->query("UPDATE foo SET bar = bar + 1");

$link2 = new mysqli($host, $user, $passwd, $db, $port, $socket);
$link2->query("DROP TABLE IF EXISTS foo");

mysql> show processlist;
+------+------+--------------------+------+---------+------+---------------------------------+--------------------------+
| Id   | User | Host               | db   | Command | Time | State                           | Info                     |
+------+------+--------------------+------+---------+------+---------------------------------+--------------------------+
| 5754 | root | 192.168.78.1:44307 | test | Sleep   |  160 |                                 | NULL                     |
| 5755 | root | 192.168.78.1:44308 | test | Query   |  160 | Waiting for table metadata lock | DROP TABLE IF EXISTS foo |
| 5756 | root | localhost:60751    | test | Query   |    0 | NULL                            | show processlist         |
+------+------+--------------------+------+---------+------+---------------------------------+--------------------------+

This behaviour is different from previous MySQL versions and it is documented in the MySQL 5.5.3 Changes as an incompatible change: A table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends. . The change fixed issues with the binary log. So far, so good – though surprising.

Under normal circumstances the server will release the lock when the clients commit their transactions or disconnect. If they don’t end their transactions nicely but die, the wait_timeout should detect failing TCP/IP clients and solve the deadlock.

How its related to the plugin

What I was doing here was simlating a failure of the plugin to inject a global transaction ID because the transaction table was not set up. The injection, the UPDATE fails, when the client is not in autocommit mode. My initial idea was that the plugin would then report and error to the application and the application could create the transaction id table, if it wanted. In my test I tried catching the error and did DROP TABLE IF EXISTS trx_table, CREATE TABLE trx_table to recreate the table. My test timed out. And, that day it was time for me to call it a day.

The other day, I checked the processlist and saw the deadlock. My PHP test had stopped and disconnected but the deadlock still existed and borked numerous other test runs. It was necessary to KILL the sleeping lock holder manually.

Error handling in the plugin

The plugin offers two ways of dealing with injection errors. By default the injection error is ignored and the function called by the application is executed nonetheless. If, for example, we are running in transaction mode (auto commit off), the user calls the commit() function and injection of the UPDATE statement for increasing the transaction counter fails, the plugin commits anyway. The transaction ends. Other transactions should not be blocked because the metadata lock on the transaction table is released by ending the transaction. No matter if using MySQL 5.5.3 or earlier.

user_commit()
   if (!inject_trx_id() && report_error)
       return false;
   
  return commit()

Optionally, users can tell the plugin to bubble up the injection error. If so, users should roll back the current transaction calling rollback() to prevent the metadata lock issue. Alternatively, you can check for the error code and try to detect cases when its required to recreate the transaction id table.

Moral

As said, if you go for plugin default settings, no locking issue but possibly gaps and holes in your transaction id table. If you know what you do, if you can adapt your application and code changes are possible, you can manually handle the error and recreate the transaction id table on demand.