如果某个锁有多个等待者，优先满足优先级最高的锁请求，但与max_write_lock_count系统变量相关的情况除外。写锁请求的优先级高于读锁请求。然而，如果将max_write_lock_count设置为较低值（比如 10），则如果读锁请求已经在 10 个写锁请求的优先考虑下被跳过，待处理的写锁请求可能会优先于读锁请求。通常这种情况不会发生，因为max_write_lock_count默认值非常大。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 语句通常按照语句中提到的表的顺序获取锁。DML statements normally acquire locks in the order in which tables are mentioned in the statement.

DDL 语句、LOCK TABLES 以及其他类似语句通过按名称顺序对显式命名的表加锁，尝试减少并发 DDL 语句之间可能发生的死锁数量。对于隐式使用的表（例如外键关系中的表，也必须加锁的表），锁可能以不同的顺序获取。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).

例如，RENAME TABLE 是一种按名称顺序获取锁的 DDL 语句：For example, RENAME TABLE is a DDL statement that acquires locks in name order:

两个语句都按顺序对 tbla 和 tblc 获取锁，但是否在获取 tblc 之前或之后获取剩余表名的锁有所不同。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.

从两个表 x 和 x_new 开始，它们具有相同的结构。三个客户端发出涉及这些表的语句：Begin with two tables x and x_new that have identical structure. Three clients issue statements that involve these tables:

客户 1：Client 1:

LOCK TABLE x WRITE, x_new WRITE;

该语句按名称顺序请求并获取 x 和 x_new 的写锁。The statement requests and acquires write locks in name order on x and x_new.

客户 2：Client 2:

INSERT INTO x VALUES(1);

该语句请求并阻塞，等待对 x 的写锁。The statement requests and blocks waiting for a write lock on x.

客户 3：Client 3:

RENAME TABLE x TO x_old, x_new TO x;

该语句按名称顺序请求对 x、x_new 和 x_old 的排他锁，但在等待 x 的锁时被阻塞。The statement requests exclusive locks in name order on x, x_new, and x_old, but blocks waiting for the lock on x.

客户 1：Client 1:

UNLOCK TABLES;

该语句释放了对 x 和 x_new 的写锁。客户端 3 对 x 的排他锁请求优先级高于客户端 2 的写锁请求，因此客户端 3 获取了 x 的锁，然后也获取了 x_new 和 x_old 的锁，执行重命名操作，并释放它的锁。然后客户端 2 获取 x 的锁，执行插入操作，并释放其锁。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.

锁获取顺序导致 RENAME TABLE 在 INSERT 之前执行。插入发生的 x 是 Client 2 执行插入时名为 x_new 的表，并被 Client 3 重命名为 x：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)

现在改为使用名为 x 和 new_x 的表，它们结构相同。仍然有三个客户端对这些表发出语句：Now begin instead with tables named x and new_x that have identical structure. Again, three clients issue statements that involve these tables:

客户 1：Client 1:

LOCK TABLE x WRITE, new_x WRITE;

该语句按名称顺序请求并获取 new_x 和 x 的写锁。The statement requests and acquires write locks in name order on new_x and x.

客户 2：Client 2:

INSERT INTO x VALUES(1);

该语句请求并阻塞，等待对 x 的写锁。The statement requests and blocks waiting for a write lock on x.

客户 3：Client 3:

RENAME TABLE x TO old_x, new_x TO x;

该语句按名称顺序请求对 new_x、old_x 和 x 的独占锁，但在等待 new_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.

客户 1：Client 1:

UNLOCK TABLES;

该语句释放了对 x 和 new_x 的写锁。对于 x，唯一的挂起请求是来自客户端 2，因此客户端 2 获取其锁，执行插入操作，并释放锁。对于 new_x，唯一的挂起请求是来自客户端 3，允许其获取该锁（以及 old_x 的锁）。重命名操作仍然被 x 的锁阻塞，直到客户端 2 的插入完成并释放其锁。然后客户端 3 获取 x 的锁，执行重命名操作，并释放其锁。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.

在这种情况下，锁获取顺序导致 INSERT 在 RENAME TABLE 之前执行。插入发生的 x 是原来的 x，现在已被重命名操作改名为 old_x：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.

元数据锁会根据需要扩展到通过外键约束相关的表，以防止在相关表上同时执行冲突的 DML 和 DDL 操作。在更新父表时，在更新外键元数据时会在子表上获取元数据锁。外键元数据由子表拥有。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.

为了确保事务的可串行化，服务器必须不允许一个会话对另一个会话中在显式或隐式启动的未完成事务中使用的表执行数据定义语言（DDL）语句。服务器通过在事务中使用的表上获取元数据锁来实现这一点，并在事务结束前延迟释放这些锁。表上的元数据锁可以防止该表结构的更改。这种锁定方式的含义是，在一个会话的事务中使用的表，在事务结束之前，不能被其他会话用于 DDL 语句。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.

该原则不仅适用于事务表，也适用于非事务表。假设一个会话开始一个事务，该事务使用事务表t和非事务表nt，如下所示：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;

服务器会在事务结束之前对t和nt保持元数据锁。如果另一个会话尝试对任一表执行 DDL 或写锁操作，它将被阻塞，直到事务结束释放元数据锁。例如，如果第二个会话尝试以下任意操作，它会被阻塞：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;

同样的行为也适用于 LOCK TABLES ... READ。也就是说，显式或隐式启动的更新任何表（无论是事务性还是非事务性）的事务会被阻塞，并且会被该表的 LOCK TABLES ... READ 阻塞。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.

在 PREPARE 语句期间获取的元数据锁在语句准备完成后会被释放，即使准备操作发生在多语句事务中也是如此。Metadata locks acquired during a PREPARE statement are released once the statement has been prepared, even if preparation occurs within a multiple-statement transaction.

对于处于 PREPARED 状态的 XA 事务，元数据锁会在客户端断开连接和服务器重启期间保持，直到执行 XA COMMIT 或 XA ROLLBACK。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.