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, and scheduled events), and tablespaces.

MySQL使用元数据锁定来管理对数据库对象的并发访问，并确保数据的一致性。

元数据锁定不仅适用于表，还适用于模式、存储程序（过程、函数、触发器和计划事件）和表空间。

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表公开了元数据锁信息，这对于查看哪些会话持有锁、哪些会话被阻止等待锁等信息非常有用。有关详细信息，请参阅第25.12.12.1节“元数据锁定表”。

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.

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

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

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

语句按顺序在tbla、tblc和tbld上获取元数据锁（因为tbld按名称顺序跟在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:

锁获取顺序导致在插入之前执行重命名表。发生插入的x是在客户机2发出插入时名为x_new并由客户机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，唯一挂起的请求是由客户机2执行的，因此客户机2获取其锁，执行插入，然后释放锁。对于new_x，唯一挂起的请求是客户端3，它被允许获取该锁（以及old_x上的锁）。重命名操作仍然会阻塞x上的锁，直到client2 insert完成并释放其锁。然后客户机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:

在这种情况下，lock acquisition order会导致在RENAME表之前执行INSERT。插入的x是原始x，现在通过重命名操作重命名为 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.

如果并发语句中的锁获取顺序对操作结果中的应用程序产生影响（如上例所示），则可以调整表名以影响锁获取顺序。

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) will block and be blocked by LOCK TABLES ... READ for that table.

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

在autocommit模式下，每条语句实际上都是一个完整的事务，因此为语句获取的元数据锁只保留到语句的末尾。

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语句中获取的元数据锁在语句准备好之后就被释放，即使在多语句事务中进行了准备。

 External locking is the use of file system locking to manage contention for MyISAM database tables by multiple processes. External locking is used in situations where a single process such as the MySQL server cannot be assumed to be the only process that requires access to tables. Here are some
examples:

外部锁定是使用文件系统锁定来管理多个进程对MyISAM数据库表的争用。外部锁定用于单个进程（如MySQL服务器）不能被认为是唯一需要访问表的进程的情况。这里有一些

示例：

• If you run multiple servers that use the same database directory (not recommended), each server must have external locking enabled.

如果运行多个使用同一数据库目录的服务器（不推荐），则每个服务器都必须启用外部锁定。

• If you use myisamchk to perform table maintenance operations on MyISAM tables, you must either ensure that the server is not running, or that the server has external locking enabled so that it locks table files as necessary to coordinate with myisamchk for access to the tables. The same is true for use of myisampack to pack MyISAM tables.

如果使用myisamchk对MyISAM表执行表维护操作，则必须确保服务器未运行，或者服务器已启用外部锁定，以便根据需要锁定表文件，以便与myisamchk协调以访问表。使用myisampack打包MyISAM表也是如此。

If the server is run with external locking enabled, you can use myisamchk at any time for read
operations such a checking tables. In this case, if the server tries to update a table that myisamchk is using, the server will wait for myisamchk to finish before it continues.

如果服务器在启用外部锁定的情况下运行，则可以随时使用myisamchk执行诸如检查表之类的读取操作。在这种情况下，如果服务器尝试更新myisamchk正在使用的表，则服务器将等待myisamchk完成后再继续。

If you use myisamchk for write operations such as repairing or optimizing tables, or if you use
myisampack to pack tables, you must always ensure that the mysqld server is not using the table.If you do not stop mysqld, at least do a mysqladmin flush-tables before you run myisamchk.
Your tables may become corrupted if the server and myisamchk access the tables simultaneously.

如果使用myisamchk进行诸如修复或优化表之类的写操作，或者使用myisampack打包表，则必须始终确保mysqld服务器未使用该表。如果不停止mysqld，则至少在运行myisamchk之前执行mysqladmin刷新表。如果服务器和myisamchk同时访问表，则表可能会损坏。

With external locking in effect, each process that requires access to a table acquires a file system lock for the table files before proceeding to access the table. If all necessary locks cannot be acquired, the process is blocked from accessing the table until the locks can be obtained (after the process that currently holds the locks releases them).

在外部锁定生效的情况下，每个需要访问表的进程都会在继续访问表之前获取表文件的文件系统锁。如果无法获取所有必需的锁，则会阻止进程访问表，直到可以获取锁为止（在当前持有锁的进程释放锁之后）。

External locking affects server performance because the server must sometimes wait for other
processes before it can access tables.

外部锁定会影响服务器性能，因为服务器有时必须等待其他进程才能访问表。

External locking is unnecessary if you run a single server to access a given data directory (which is the usual case) and if no other programs such as myisamchk need to modify tables while the server is running. If you only read tables with other programs, external locking is not required, although myisamchk might report warnings if the server changes tables while myisamchk is reading them.

如果运行单个服务器来访问给定的数据目录（通常是这样），并且在服务器运行时没有其他程序（如myisamchk）需要修改表，则不需要外部锁定。如果您只使用其他程序读取表，则不需要外部锁定，不过如果myisamchk在读取表时服务器更改了表，myisamchk可能会报告警告。

With external locking disabled, to use myisamchk, you must either stop the server while myisamchk executes or else lock and flush the tables before running myisamchk. (See Section 8.12.1, “System Factors”.) To avoid this requirement, use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables.

在禁用外部锁定的情况下，要使用myisamchk，必须在myisamchk执行时停止服务器，或者在运行myisamchk之前锁定并刷新表(请参阅第8.12.1节“系统因素”。）为避免此要求，请使用CHECK TABLE和REPAIR TABLE语句检查和修复MyISAM表。

For mysqld, external locking is controlled by the value of the skip_external_locking system
variable. When this variable is enabled, external locking is disabled, and vice versa. External locking is disabled by default.

对于mysqld，外部锁定由skip_external_locking系统变量的值控制。启用此变量时，将禁用外部锁定，反之亦然。默认情况下禁用外部锁定。

Use of external locking can be controlled at server startup by using the --external-locking or --
skip-external-locking option.

通过使用--external locking或--skip external locking选项，可以在服务器启动时控制外部锁定的使用。

If you do use external locking option to enable updates to MyISAM tables from many MySQL
processes, you must ensure that the following conditions are satisfied:

如果确实使用外部锁定选项从许多MySQL进程启用对MyISAM表的更新，则必须确保满足以下条件：

• Do not use the query cache for queries that use tables that are updated by another process.

对于使用其他进程更新的表的查询，不要使用查询缓存。

• Do not start the server with the delay_key_write system variable set to ALL or use the
DELAY_KEY_WRITE=1 table option for any shared tables. Otherwise, index corruption can occur.

不要在将delay_key_write系统变量设置为ALL时启动服务器，也不要对任何共享表使用DELAY_KEY_WRITE=1 table选项。否则，可能会发生索引损坏。

The easiest way to satisfy these conditions is to always use --external-locking together with
--delay-key-write=OFF and --query-cache-size=0. (This is not done by default because in
many setups it is useful to have a mixture of the preceding options.)

满足这些条件的最简单方法是始终使用--external locking和--delay key write=OFF以及--query cache size=0（默认情况下不会这样做，因为在许多设置中，混合使用前面的选项是很有用的）