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, stored programs (procedures, functions, triggers, scheduled events), tablespaces, user locks acquired with the GET_LOCK() function (see Section 12.14, “Locking Functions”), and locks acquired with the locking service described in Section 5.5.6.1, “The Locking Service”.

The Performance Schema metadata_locks table exposes metadata lock information, which can be useful for seeing which sessions hold locks, are blocked waiting for locks, and so forth. For details, see Section 25.12.12.1, “The metadata_locks Table”.

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.

• Metadata Lock Acquisition

• Metadata Lock Release

Metadata Lock Acquisition

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:

• This RENAME TABLE statement renames tbla to something else, and renames tblc to tbla:

  RENAME TABLE tbla TO tbld, tblc TO tbla;

  The statement acquires metadata locks, in order, on tbla, tblc, and tbld (because tbld follows tblc in name order):

• This slightly different statement also renames tbla to something else, and renames tblc to tbla:

  RENAME TABLE tbla TO tblb, tblc TO tbla;

  In this case, the statement acquires metadata locks, in order, on tbla, tblb, and tblc (because tblb precedes tblc 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:

LOCK TABLE x WRITE, x_new WRITE;

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

Client 2:

INSERT INTO x VALUES(1);

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

Client 3:

RENAME TABLE x TO x_old, x_new TO x;

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:

UNLOCK TABLES;

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:

mysql> SELECT * FROM x;
+------+
| i    |
+------+
|    1 |
+------+

mysql> SELECT * FROM x_old;
Empty set (0.01 sec)

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:

LOCK TABLE x WRITE, new_x WRITE;

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

Client 2:

INSERT INTO x VALUES(1);

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

Client 3:

RENAME TABLE x TO old_x, new_x TO x;

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:

UNLOCK TABLES;

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:

mysql> SELECT * FROM x;
Empty set (0.01 sec)

mysql> SELECT * FROM old_x;
+------+
| i    |
+------+
|    1 |
+------+

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 Lock Release

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:

START TRANSACTION;
SELECT * FROM t;
SELECT * FROM nt;

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:

DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE;

The same behavior applies for The LOCK TABLES ... READ. That is, explicitly or implicitly started transactions that update any table (transactional or nontransactional) block and are 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.