To ensure transaction serializability, the server must not allow 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.4.4, 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.
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.4.4, 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.


User Comments
Add your own comment.