MySQL Troubleshooting

Metadata Locking

To ensure data consistency, DDL operations on a table should be blocked if another transaction is using the table. Starting with version 5.5.3, this is achieved by using metadata locks.

When a transaction starts, it acquires metadata locks on all the tables it uses and releases the locks when it finishes. All other threads that try to modify the tables’ definitions wait until the transaction ends.

DDL operations in MySQL servers prior to 5.5.3 knew nothing about parallel transactions. This would lead to collisions similar to the following:

mysql1> BEGIN;
Query OK, 0 rows affected (0.08 sec)

mysql1> SELECT * FROM t1;
+------+
| f1   |
+------+
|  100 |
|  200 |
+------+
2 rows in set (0.10 sec)

In one transaction, we are selecting data from a table, planning to use this result set during the current transaction. At the very same time, another thread drops the table:

mysql2> DROP TABLE t1;
Query OK, 0 rows affected (0.17 sec)

DROP is not an operation that can be rolled back, so the first thread is the one affected by the conflict:

mysql> SELECT * FROM t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

Our transaction obviously cannot complete. A metadata lock would allow our transaction to complete before the other connection’s DROP statement could execute. To illustrate this, we will execute the same example on version 5.5.3 or later:

mysql1> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql1> SELECT * FROM t1; +------+ | f1 | +------+ | 100 | | 200 | +------+ 2 rows in set ...