8.11.4 Metadata Locking

If there are multiple waiters for a given lock, the highest-priority lock request is satisfied first, with an exception related to the max_write_lock_count system variable. Write lock requests have higher priority than read lock requests. However, if max_write_lock_count is set to some low value (say, 10), read lock requests may be preferred over pending write lock requests if the read lock requests have already been passed over in favor of 10 write lock requests. Normally this behavior does not occur because max_write_lock_count by default has a very large value.

Statements acquire metadata locks one by one, not simultaneously, and perform deadlock detection in the process.

DML statements normally acquire locks in the order in which tables are mentioned in the statement.

DDL statements, LOCK TABLES, and other similar statements try to reduce the number of possible deadlocks between concurrent DDL statements by acquiring locks on explicitly named tables in name order. Locks might be acquired in a different order for implicitly used tables (such as tables in foreign key relationships that also must be locked).

For example, RENAME TABLE is a DDL statement that acquires locks in name order:

Both statements acquire locks on tbla and tblc, in that order, but differ in whether the lock on the remaining table name is acquired before or after tblc.

Metadata lock acquisition order can make a difference in operation outcome when multiple transactions execute concurrently, as the following example illustrates.

Begin with two tables x and x_new that have identical structure. Three clients issue statements that involve these tables:

Client 1:

The statement requests and acquires write locks in name order on x and x_new.

Client 2:

The statement requests and blocks waiting for a write lock on x.

Client 3:

The statement requests exclusive locks in name order on x, x_new, and x_old, but blocks waiting for the lock on x.

Client 1:

The statement releases the write locks on x and x_new. The exclusive lock request for x by Client 3 has higher priority than the write lock request by Client 2, so Client 3 acquires its lock on x, then also on x_new and x_old, performs the renaming, and releases its locks. Client 2 then acquires its lock on x, performs the insert, and releases its lock.

Lock acquisition order results in the RENAME TABLE executing before the INSERT. The x into which the insert occurs is the table that was named x_new when Client 2 issued the insert and was renamed to x by Client 3:

Now begin instead with tables named x and new_x that have identical structure. Again, three clients issue statements that involve these tables:

Client 1:

The statement requests and acquires write locks in name order on new_x and x.

Client 2:

The statement requests and blocks waiting for a write lock on x.

Client 3:

The statement requests exclusive locks in name order on new_x, old_x, and x, but blocks waiting for the lock on new_x.

Client 1:

The statement releases the write locks on x and new_x. For x, the only pending request is by Client 2, so Client 2 acquires its lock, performs the insert, and releases the lock. For new_x, the only pending request is by Client 3, which is permitted to acquire that lock (and also the lock on old_x). The rename operation still blocks for the lock on x until the Client 2 insert finishes and releases its lock. Then Client 3 acquires the lock on x, performs the rename, and releases its lock.

In this case, lock acquisition order results in the INSERT executing before the RENAME TABLE. The x into which the insert occurs is the original x, now renamed to old_x by the rename operation:

If order of lock acquisition in concurrent statements makes a difference to an application in operation outcome, as in the preceding example, you may be able to adjust the table names to affect the order of lock acquisition.

Metadata locks are extended, as necessary, to tables related by a foreign key constraint to prevent conflicting DML and DDL operations from executing concurrently on the related tables. When updating a parent table, a metadata lock is taken on the child table while updating foreign key metadata. Foreign key metadata is owned by the child table.

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:

The same behavior applies for The LOCK TABLES ... READ. That is, explicitly or implicitly started transactions that update any table (transactional or nontransactional) will block and be blocked by LOCK TABLES ... READ for that table.

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.

As of MySQL 8.0.13, for XA transactions in PREPARED state, metadata locks are maintained across client disconnects and server restarts, until an XA COMMIT or XA ROLLBACK is executed.