To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted transaction in another session.
As of MySQL 5.5.3, the server achieves this by acquiring
metadata locks on tables used within a transaction and deferring
release of those locks until the transaction ends. A metadata
lock on a table prevents changes to the table's structure. This
locking approach has the implication that 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. For
example, if a table t1
is in use by a
transaction, another session that attempts to execute
DROP TABLE
t1
will block until the transaction ends.
If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.
Metadata locks acquired during a
PREPARE
statement are released
once the statement has been prepared, even if preparation occurs
within a multiple-statement transaction.
Before MySQL 5.5.3, when a transaction acquired a metadata lock for a table used within a statement, it released the lock at the end of the statement. This approach had the disadvantage that if a DDL statement occurred for a table that was being used by another session in an active transaction, statements could be written to the binary log in the wrong order.