MySQL 5.7-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.15, “Locking Functions”), and locks acquired with the locking service described in Section 5.5.6.1, “The Locking Service”.

MySQL使用元数据锁定来管理对数据库对象的并发访问，确保数据一致性。元数据锁不仅适用于表，还适用于模式、存储程序(过程、函数、触发器、计划事件)、表空间、用GET_LOCK()函数获取的用户锁(参见12.15节“锁定函数”)，以及用5.5.6.1节“锁定服务”中描述的锁定服务获取的锁。

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”.

Performance Schema metadata_locks表公开了元数据锁信息，这对于查看哪些会话持有锁、哪些会话在等待锁时被阻塞等等非常有用。

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.

元数据锁不能替代表定义缓存，它的互斥锁和锁与LOCK_open互斥锁不同。下面的讨论提供了关于元数据锁定如何工作的一些信息。

• 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.

如果给定的锁有多个等待者，则优先级最高的锁请求首先得到满足，并伴随一个与max_write_lock_count系统变量相关的异常。写锁优先级高于读锁。然而，如果max_write_lock_count设置为某个较低的值(比如10)，读锁请求可能会优先于挂起的写锁请求，如果读锁请求已经被传递给了10个写锁请求。通常这种行为不会发生，因为max_write_lock_count默认值非常大。

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.

DML语句通常按照语句中提到表的顺序获取锁。

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).

DDL语句、LOCK TABLES和其他类似语句试图通过按名称顺序获取显式命名表上的锁来减少并发DDL语句之间可能的死锁数量。对于隐式使用的表(例如外键关系中的表也必须被锁定)，可能会以不同的顺序获得锁。

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

例如，RENAME TABLE是一条DDL语句，它按名称顺序获取锁:
​

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

这个RENAME TABLE语句将tbla重命名为其他东西，并将tblc重命名为tbla:

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

该语句在tbla、tblc和tbld上按顺序获得元数据锁(因为tblc的名字顺序在tblc之后):

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

这个稍微不同的语句还将tbla重命名为其他东西，并将tblc重命名为tbla:

 In this case, the statement acquires metadata locks, in order, on tbla, tblb, and tblc (because tblb precedes tblc in name order):

在本例中，该语句按顺序获得tbla、tblb和tblc上的元数据锁(因为按名称顺序，tblb在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.

这两个语句都按顺序获得tbla和tblc上的锁，但不同的是剩余表名上的锁是在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:

从具有相同结构的两个表x和x_new开始。三个客户端发出了涉及这些表的语句:

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

 该语句按照x和x_new上的名称顺序请求和获取写锁。

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

语句请求并阻塞等待x上的写锁。

 

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

该语句以名字顺序请求x、x_new和x_old上的排他锁，但是在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.

该语句释放x和x_new上的写锁。客户机3对x的排他锁请求的优先级高于客户机2的写锁请求，因此客户机3在x上获取锁，然后在x_new和x_old上获取锁，执行重命名并释放锁。客户机2然后获得它对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:

锁获取顺序导致在INSERT之前执行RENAME TABLE。发生插入的x是当Client 2发出插入时命名为x_new的表，并被Client 3重命名为x:

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

 现在以具有相同结构的名为x和new_x的表开始。同样，有三个客户端发出了包含这些表的语句:

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

 该语句在new_x和x上按名称顺序请求和获得写锁。

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

语句请求并阻塞等待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.

该语句以名称顺序请求new_x、old_x和x上的排他锁，但是在new_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. 

该语句释放x和new_x上的写锁。对于x，唯一挂起的请求是Client 2发出的，因此Client 2获取它的锁，执行插入，然后释放锁。对于new_x，唯一挂起的请求是Client 3，它被允许获得锁(以及old_x上的锁)。重命名操作仍然阻塞x上的锁，直到Client 2插入完成并释放它的锁。然后客户机3获得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:

 在这种情况下，锁获取顺序导致INSERT在RENAME TABLE之前执行。插入发生的x是原始的x，现在通过rename操作重命名为old_x:

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.

为了确保事务可序列化，服务器必须不允许一个会话对另一个会话中未完成的显式或隐式启动的事务中使用的表执行数据定义语言(DDL)语句。服务器通过获取事务中使用的表上的元数据锁，并延迟释放这些锁，直到事务结束。表上的元数据锁可以防止对表结构的更改。这种锁定方法意味着在一个会话中事务正在使用的表不能在DDL状态下使用

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:

这个原则不仅适用于事务性表，也适用于非事务性表。假设一个会话开始一个使用事务性表t和非事务性表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: 

服务器持有t和nt的元数据锁，直到事务结束。如果另一个会话尝试在任何一个表上执行DDL或写锁操作，它将阻塞，直到事务结束时元数据锁释放。例如，如果第二个会话尝试以下任何一个操作，它就会阻塞:

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. 

同样的行为也适用于LOCK TABLES…阅读。也就是说，显式或隐式启动的事务更新任何表(事务性或非事务性)块，并被LOCK TABLES阻塞…读取那个表。

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.

在PREPARE语句期间获取的元数据锁在语句准备完成后释放，即使是在多语句事务中进行准备。