Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 26.9Mb
PDF (A4) - 26.9Mb
PDF (RPM) - 25.3Mb
HTML Download (TGZ) - 6.3Mb
HTML Download (Zip) - 6.3Mb
HTML Download (RPM) - 5.3Mb
Man Pages (TGZ) - 158.7Kb
Man Pages (Zip) - 262.0Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

8.11.4 Metadata Locking

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events).

Metadata locking does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects.

Metadata locking is not a replacement for the table definition cache, and its mutexes and locks differ from the LOCK_open mutex. The following discussion provides some information about how metadata locking works.

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 explicitly or implicitly started transaction in another session. 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.

This principle applies not only to transactional tables, but also to nontransactional tables. Suppose that a session begins a transaction that uses transactional table t and nontransactional table nt as follows:


The server holds metadata locks on both t and nt until the transaction ends. If another session attempts a DDL or write lock operation on either table, it blocks until metadata lock release at transaction end. For example, a second session blocks if it attempts any of these operations:


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.

In autocommit mode, each statement is in effect a complete transaction, so metadata locks acquired for the statement are held only to the end of the statement.

Metadata locks acquired during a PREPARE statement are released once the statement has been prepared, even if preparation occurs within a multiple-statement transaction.

User Comments
  Posted by Mohamed Nabil on October 6, 2013
If you have a desktop program with multi-user connections to database that freezes when you try to Lock Tables using statement "LOCK TABLES table_1 WRITE, table_2 WRITE, table_n WRITE"
then in mysql command line tool run command "SHOW FULL PROCESSLIST;"
If you faced the problem of "Waiting for table metadata lock" then consider the following

After Reading the resource attached down here I could figure out my problems was:
1- Since 5.5 any table accessed during a transaction its meta data is locked by mysql
2- Since in transactional database desktop application are transaction enabled all the time then JDBC connection is set always to be connection.setAutoCommit(false);
3- running complete successful transaction with commit/rollback methods will release any locks held by mysql during this successful transaction
4- any next call to select statements queries is considered a transactional operation and then metadata are held locked by mysql server
5- again if you run a select stament only queries then all these selected tables metadata are held locked by mysql server
6- you have to call commit/rollback to release these tables metadata even if you had never executed Update/Delete statements in your queries
7- as a solution you have to:
a- when use connection.setAutoCommit(false); you have to run commit or rollback after every select statement you make to release metadata locks and allow other sessions can run select queries on tables you used.
b- use connection.setAutoCommit(true); for the life of connection and switch the use to be connection.setAutoCommit(false); when making Update/Delete commit/rollback transactional operations to avoid the metadata lock situations

Please Note that running rollback is much safer (losing valuable data is better than injecting wrong data) in this situation, for me now I attached connection.rollback() after calling select-only statements queries statement.close(); to keep connection.setAutoCommit(false); during connection life time.

for further discussion read my post here:

Before MySQL 5.5, when a transaction acquired the equivalent of 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.
Also, just to make it clear, if a statement inside a transaction ever access a table, a metadata lock is kept on the table until the transaction is either committed or rolled back. For example:

session 1>

session 2>

Even after the SELECT has been executed, the ALTER TABLE won't succeed until session 1 either commits or rollbacks the transaction.
The statement might have been run inside a transaction that hasn't been properly ended (commit/rollback).
Sign Up Login You must be logged in to post a comment.