要在当前会话中获取表锁，请使用 LOCK TABLES 语句，它会获取元数据锁（参见 第 10.11.4 节，“元数据锁定”）。To acquire table locks within the current session, use the LOCK TABLES statement, which acquires metadata locks (see Section 10.11.4, “Metadata Locking”).

以下锁类型可用：The following lock types are available:

READ [LOCAL] 锁：READ [LOCAL] lock:

WRITE 锁：WRITE lock:

WRITE 锁通常优先于 READ 锁，以确保更新尽快处理。这意味着，如果一个会话先获取了 READ 锁，然后另一个会话请求 WRITE 锁，随后的 READ 锁请求将等待，直到请求 WRITE 锁的会话获取并释放该锁。（当 max_write_lock_count 系统变量值较小时，该策略可能会有例外；参见 第 10.11.4 节，“元数据锁定”。）WRITE locks normally have higher priority than READ locks to ensure that updates are processed as soon as possible. This means that if one session obtains a READ lock and then another session requests a WRITE lock, subsequent READ lock requests wait until the session that requested the WRITE lock has obtained the lock and released it. (An exception to this policy can occur for small values of the max_write_lock_count system variable; see Section 10.11.4, “Metadata Locking”.)

如果 LOCK TABLES 语句由于其他会话对任何表持有锁而必须等待，则会阻塞，直到可以获取所有锁为止。If the LOCK TABLES statement must wait due to locks held by other sessions on any of the tables, it blocks until all locks can be acquired.

需要锁的会话必须在一个 LOCK TABLES 语句中获取所有锁。在获得的锁被保留期间，会话只能访问被锁定的表。例如，在接下来的语句序列中，尝试访问 t2，因为该请求未被锁定在 LOCK TABLES 语句：A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. While the locks thus obtained are held, the session can access only the locked tables. For example, in the following sequence of statements, an error occurs for the attempt to access t2 because it was not locked in the LOCK TABLES statement:

mysql> LOCK TABLES t1 READ;
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
mysql> SELECT COUNT(*) FROM t2;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

INFORMATION_SCHEMA是例外。即使在会话中保存通过 LOCK TABLES。Tables in the INFORMATION_SCHEMA database are an exception. They can be accessed without being locked explicitly even while a session holds table locks obtained with LOCK TABLES.

你不能在同一查询中多次引用同一个锁名的表。改用别名，并为表和每个别名分别获得独立锁：You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias:

mysql> LOCK TABLE t WRITE, t AS t1 READ;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

错误发生在第一个INSERT，因为锁定表有两个指向同名的引用。第二个 INSERT 成功，因为对表的引用使用了不同的名称。The error occurs for the first INSERT because there are two references to the same name for a locked table. The second INSERT succeeds because the references to the table use different names.

如果你的语句通过别名来指代表，你必须用相同的别名锁定表。不指定别名就无法锁定表：If your statements refer to a table by means of an alias, you must lock the table using that same alias. It does not work to lock the table without specifying the alias:

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

反过来，如果你用别名锁住表，你必须在语句中引用该别名：Conversely, if you lock a table using an alias, you must refer to it in your statements using that alias:

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

当会话持有的表锁被释放时，它们会同时释放。会话可以显式释放锁，或者在特定条件下隐式释放锁。When the table locks held by a session are released, they are all released at the same time. A session can release its locks explicitly, or locks may be released implicitly under certain conditions.

如果客户端会话的连接终止，无论是正常还是异常，服务器都会隐式释放会话持有的所有表锁（事务性和非事务性）。如果客户端重新连接，这些锁将不再生效。此外，如果客户端有一个活动事务，服务器在断开连接时会回滚该事务，如果重新连接发生，新会话将以自动提交启用开始。因此，客户端可能希望禁用自动重连。在启用自动重连的情况下，如果发生重连，客户端不会收到通知，但任何表锁或当前事务都将丢失。在禁用自动重连时，如果连接中断，下一个执行的语句将发生错误。客户端可以检测该错误并采取适当的操作，例如重新获取锁或重新执行事务。参见 自动重连控制。If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session (transactional and nontransactional). If the client reconnects, the locks are no longer in effect. In addition, if the client had an active transaction, the server rolls back the transaction upon disconnect, and if reconnect occurs, the new session begins with autocommit enabled. For this reason, clients may wish to disable auto-reconnect. With auto-reconnect in effect, the client is not notified if reconnect occurs but any table locks or current transaction are lost. With auto-reconnect disabled, if the connection drops, an error occurs for the next statement issued. The client can detect the error and take appropriate action such as reacquiring the locks or redoing the transaction. See Automatic Reconnection Control.

LOCK TABLES 和 UNLOCK TABLES 与事务的使用交互如下：LOCK TABLES and UNLOCK TABLES interact with the use of transactions as follows:

如果你显式地用 LOCK TABLES，触发器中使用的任何表也被隐式锁定：If you lock a table explicitly with LOCK TABLES, any tables used in triggers are also locked implicitly:

假设你用以下语句锁定了两个表，t1 和 t2，使用如下语句：Suppose that you lock two tables, t1 and t2, using this statement:

LOCK TABLES t1 WRITE, t2 READ;

如果 t1 或 t2 有触发器，触发器中使用的表也会被锁定。假设 t1 的触发条件定义如下：If t1 or t2 have any triggers, tables used within the triggers are also locked. Suppose that t1 has a trigger defined like this:

CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW
BEGIN
  UPDATE t4 SET count = count+1
      WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);
  INSERT INTO t2 VALUES(1, 2);
END;

LOCK TABLES 语句表明 t1 和 t2 是因为它们出现在语句中而被锁定，t3 和 t4 被锁定，因为它们被用于触发条件：The result of the LOCK TABLES statement is that t1 and t2 are locked because they appear in the statement, and t3 and t4 are locked because they are used within the trigger:

你可以安全地使用 KILL“来终止等待表锁的会话。参见 第 15.7.8.4 节，”KILL Statement“。You can safely use KILL to terminate a session that is waiting for a table lock. See Section 15.7.8.4, “KILL Statement”.

LOCK TABLES 和 UNLOCK TABLES 不能在存储程序中使用。LOCK TABLES and UNLOCK TABLES cannot be used within stored programs.

performance_schema不能被锁定为 LOCK TABLES，除了 setup_xxx 表。Tables in the performance_schema database cannot be locked with LOCK TABLES, except the setup_xxx tables.

由 LOCK TABLES是一个 MySQL 服务器。它与 NDB Cluster 不兼容，后者无法在多个实例中强制执行 SQL 级别锁mysqld。你可以在 API 应用中强制锁定。更多信息请参见 第 25.2.7.10 节，“关于多个 NDB 集群节点的限制”。The scope of a lock generated by LOCK TABLES is a single MySQL server. It is not compatible with NDB Cluster, which has no way of enforcing an SQL-level lock across multiple instances of mysqld. You can enforce locking in an API application instead. See Section 25.2.7.10, “Limitations Relating to Multiple NDB Cluster Nodes”, for more information.

以下语句在 LOCK TABLES 语句生效时被禁止： CREATE TABLE CREATE TABLE ... LIKECREATE VIEW， DROP VIEW，以及存储函数、过程和事件的 DDL 语句。The following statements are prohibited while a LOCK TABLES statement is in effect: CREATE TABLE, CREATE TABLE ... LIKE, CREATE VIEW, DROP VIEW, and DDL statements on stored functions and procedures and events.

对于某些操作，必须访问 mysql。例如，HELP 语句需要服务器端帮助表的内容，CONVERT_TZ() 可能需要读取时区表。服务器会隐式锁定系统表以便必要时读取，这样你就不必显式锁定它们。这些表的处理方式如下：For some operations, system tables in the mysql database must be accessed. For example, the HELP statement requires the contents of the server-side help tables, and CONVERT_TZ() might need to read the time zone tables. The server implicitly locks the system tables for reading as necessary so that you need not lock them explicitly. These tables are treated as just described:

mysql.help_category
mysql.help_keyword
mysql.help_relation
mysql.help_topic
mysql.time_zone
mysql.time_zone_leap_second
mysql.time_zone_name
mysql.time_zone_transition
mysql.time_zone_transition_type

如果你想在这些表上显式地放置一个WRITE并且LOCK TABLES 语句，必须是唯一被锁定的表;不能用相同的语句锁定其他表。If you want to explicitly place a WRITE lock on any of those tables with a LOCK TABLES statement, the table must be the only one locked; no other table can be locked with the same statement.

通常，你不需要锁定表，因为所有单一的 UPDATE语句都是原子性的;没有其他会话可以干扰当前执行的任何 SQL 语句。不过，在某些情况下，锁定表可能带来优势：Normally, you do not need to lock tables, because all single UPDATE statements are atomic; no other session can interfere with any other currently executing SQL statement. However, there are a few cases when locking tables may provide an advantage:

在许多情况下，你可以通过使用相对更新（LOCK TABLES）或UPDATE customer SET value=value+new_value函数来避免使用LAST_INSERT_ID() function.You can avoid using LOCK TABLES in many cases by using relative updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function.

你还可以通过使用用户级的咨询锁函数GET_LOCK()和 RELEASE_LOCK()。这些锁保存在服务器的哈希表中，并以 pthread_mutex_lock() 和 pthread_mutex_unlock() 实现以实现高速。参见 第 14.14 节“锁定函数”。You can also avoid locking tables in some cases by using the user-level advisory lock functions GET_LOCK() and RELEASE_LOCK(). These locks are saved in a hash table in the server and implemented with pthread_mutex_lock() and pthread_mutex_unlock() for high speed. See Section 14.14, “Locking Functions”.

有关锁策略的更多信息，请参见 第 10.11.1 节“内部锁定方法”。See Section 10.11.1, “Internal Locking Methods”, for more information on locking policy.