MySQL :: MySQL 8.4 参考手册 :: 15.3.6 LOCK TABLES 和 UNLOCK TABLES 语句

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / LOCK TABLES 和 UNLOCK TABLES 语句

15.3.6 LOCK TABLES 和 UNLOCK TABLES 语句

LOCK {TABLE | TABLES}
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | WRITE
}

UNLOCK {TABLE | TABLES}

MySQL 允许客户端会话显式获取表锁，以便与其他会话协作访问表，或防止其他会话在某个会话需要独占访问表时修改表。会话只能为自己获取或释放锁。一个会话不能为另一个会话获取锁，也不能释放另一个会话持有的锁。

锁可以用于模拟事务或在更新表时提高速度。这在表锁限制和条件中有更详细的说明。

LOCK TABLES 为当前客户端会话显式获取表锁。可以为基表或视图获取表锁。您必须具有 LOCK TABLES 权限以及要锁定的每个对象的 SELECT 权限。

对于视图锁定，LOCK TABLES 会将视图中使用的所有基表添加到要锁定的表集中，并自动锁定它们。对于被锁定视图下的表，LOCK TABLES 会检查视图定义者（对于 SQL SECURITY DEFINER 视图）或调用者（对于所有视图）是否对这些表具有适当的权限。

如果您使用 LOCK TABLES 显式锁定一个表，则触发器中使用的任何表也会被隐式锁定，如 LOCK TABLES 和触发器中所述。

如果您使用 LOCK TABLES 显式锁定一个表，则任何通过外键约束关联的表都将被隐式打开和锁定。对于外键检查，会在相关表上获取共享的只读锁（LOCK TABLES READ）。对于级联更新，会在参与操作的相关表上获取共享无写锁（LOCK TABLES WRITE）。

UNLOCK TABLES 显式释放当前会话持有的任何表锁。LOCK TABLES 在获取新锁之前，会隐式释放当前会话持有的任何表锁。

UNLOCK TABLES 的另一个用途是释放使用 FLUSH TABLES WITH READ LOCK 语句获取的全局读锁，该语句允许您锁定所有数据库中的所有表。请参阅第 15.7.8.3 节，“FLUSH 语句”。（如果您拥有像 Veritas 这样的文件系统，可以在某个时间点拍摄快照，那么这是一种非常方便的获取备份的方法。）

LOCK TABLE 是 LOCK TABLES 的同义词；UNLOCK TABLE 是 UNLOCK TABLES 的同义词。

表锁仅防止其他会话进行不适当的读取或写入。持有 WRITE 锁的会话可以执行表级操作，例如 DROP TABLE 或 TRUNCATE TABLE。对于持有 READ 锁的会话，不允许执行 DROP TABLE 和 TRUNCATE TABLE 操作。

以下讨论仅适用于非 TEMPORARY 表。对于 TEMPORARY 表，允许使用 LOCK TABLES（但会被忽略）。创建该表的会话可以自由访问该表，而不管其他锁的生效情况。不需要锁定，因为其他会话无法看到该表。

表锁获取

要在当前会话中获取表锁，请使用 LOCK TABLES 语句，该语句会获取元数据锁（请参阅第 10.11.4 节，“元数据锁”）。

可以使用以下锁类型

READ [LOCAL] 锁

持有锁的会话可以读取表（但不能写入）。
多个会话可以同时获取表的 READ 锁。
其他会话无需显式获取 READ 锁即可读取表。
LOCAL 修饰符允许其他会话在持有锁时执行非冲突的 INSERT 语句（并发插入）。（请参阅第 10.11.3 节，“并发插入”。）但是，如果您要在持有锁时使用服务器外部的进程操作数据库，则不能使用 READ LOCAL。对于 InnoDB 表，READ LOCAL 与 READ 相同。

WRITE 锁

持有锁的会话可以读取和写入表。
只有持有锁的会话才能访问该表。其他任何会话都无法访问它，直到锁被释放。
在持有 WRITE 锁时，其他会话对该表的锁请求会被阻塞。

WRITE 锁通常比 READ 锁具有更高的优先级，以确保更新尽快得到处理。这意味着，如果一个会话获取了 READ 锁，然后另一个会话请求 WRITE 锁，则后续的 READ 锁请求将等待，直到请求 WRITE 锁的会话获取到该锁并释放它。（对于 max_write_lock_count 系统变量的小值，可能会出现此策略的例外情况；请参阅第 10.11.4 节，“元数据锁”。）

如果 LOCK TABLES 语句由于其他会话持有的锁而必须等待，则它会阻塞，直到可以获取所有锁。

需要锁的会话必须在单个 LOCK TABLES 语句中获取其需要的所有锁。在持有获得的锁的同时，会话只能访问已锁定的表。例如，在以下语句序列中，尝试访问 t2 时会发生错误，因为它没有在 LOCK TABLES 语句中被锁定

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 获取的表锁，也可以在不显式锁定它们的情况下访问它们。

您不能在单个查询中使用相同的名称多次引用锁定的表。请改用别名，并为表和每个别名获取单独的锁

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 成功了，因为对表的引用使用了不同的名称。

如果您的语句通过别名引用表，则必须使用相同的别名锁定该表。不指定别名而锁定表是行不通的

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

相反，如果您使用别名锁定表，则必须在语句中使用该别名引用它

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;

表锁释放

当会话持有的表锁被释放时，它们会同时全部释放。会话可以显式释放其锁，或者在某些情况下可以隐式释放锁。

会话可以使用 UNLOCK TABLES 显式释放其锁。
如果会话在已经持有锁的情况下发出 LOCK TABLES 语句来获取锁，则在授予新锁之前，其现有锁会被隐式释放。
如果会话开始事务（例如，使用 START TRANSACTION），则会执行隐式的 UNLOCK TABLES，这将导致释放现有锁。（有关表锁和事务之间交互的更多信息，请参阅表锁和事务的交互。）

如果客户端会话的连接终止，无论是正常终止还是异常终止，服务器都会隐式释放会话持有的所有表锁（事务性和非事务性）。如果客户端重新连接，则锁将不再生效。此外，如果客户端具有活动事务，则服务器会在断开连接时回滚事务，如果发生重新连接，则新会话将在启用自动提交的情况下开始。因此，客户端可能希望禁用自动重新连接。启用自动重新连接后，如果发生重新连接，客户端不会收到通知，但任何表锁或当前事务都会丢失。禁用自动重新连接后，如果连接断开，则发出的下一条语句会发生错误。客户端可以检测到错误并采取适当的操作，例如重新获取锁或重做事务。请参阅自动重新连接控制。

注意

如果您在锁定的表上使用 ALTER TABLE，则该表可能会被解锁。例如，如果您尝试第二次执行 ALTER TABLE 操作，则结果可能是错误 表 'tbl_name' 未使用 LOCK TABLES 锁定。要解决此问题，请在第二次更改之前再次锁定该表。另请参阅第 B.3.6.1 节，“ALTER TABLE 问题”。

表锁和事务的交互

LOCK TABLES 和 UNLOCK TABLES 与事务的使用交互如下

LOCK TABLES 不是事务安全的，并且在尝试锁定表之前会隐式提交任何活动事务。
UNLOCK TABLES 会隐式提交任何活动事务，但前提是已使用 LOCK TABLES 获取表锁。例如，在以下语句集中，UNLOCK TABLES 会释放全局读锁，但不会提交事务，因为没有生效的表锁
```
FLUSH TABLES WITH READ LOCK;
START TRANSACTION;
SELECT ... ;
UNLOCK TABLES;
```
开始事务（例如，使用 START TRANSACTION）会隐式提交任何当前事务并释放现有的表锁。
FLUSH TABLES WITH READ LOCK 获取的是全局读锁，而不是表锁，因此它与 LOCK TABLES 和 UNLOCK TABLES 在表锁定和隐式提交方面的行为不同。例如，START TRANSACTION 不会释放全局读锁。参见第 15.7.8.3 节，“FLUSH 语句”。
其他隐式导致事务提交的语句不会释放现有的表锁。有关此类语句的列表，请参见第 15.3.3 节，“导致隐式提交的语句”。
对事务性表（例如 InnoDB 表）使用 LOCK TABLES 和 UNLOCK TABLES 的正确方法是使用 SET autocommit = 0（而不是 START TRANSACTION）开始事务，然后使用 LOCK TABLES，并且直到显式提交事务才调用 UNLOCK TABLES。例如，如果需要写入表 t1 并从表 t2 读取数据，则可以执行以下操作：
```
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
```
调用 LOCK TABLES 时，InnoDB 在内部获取自己的表锁，MySQL 也获取自己的表锁。InnoDB 在下一次提交时释放其内部表锁，但要使 MySQL 释放其表锁，必须调用 UNLOCK TABLES。不应该将 autocommit 设置为 1，因为那样 InnoDB 会在调用 LOCK TABLES 后立即释放其内部表锁，从而很容易发生死锁。如果 autocommit = 1，则 InnoDB 根本不会获取内部表锁，以帮助旧应用程序避免不必要的死锁。
ROLLBACK 不会释放表锁。

LOCK TABLES 和触发器

如果使用 LOCK TABLES 显式锁定表，则触发器中使用的任何表也会被隐式锁定

锁定的获取时间与使用 LOCK TABLES 语句显式获取锁的时间相同。
对触发器中使用的表的锁定取决于该表是否仅用于读取。如果是，则读锁就足够了。否则，将使用写锁。
如果使用 LOCK TABLES 显式锁定表以进行读取，但由于该表可能会在触发器中被修改，因此需要将其锁定以进行写入，则会获取写锁而不是读锁。（也就是说，由于表出现在触发器中而需要的隐式写锁会导致对该表的显式读锁请求转换为写锁请求。）

假设使用以下语句锁定两个表 t1 和 t2：

LOCK TABLES t1 WRITE, t2 READ;

如果 t1 或 t2 有任何触发器，则触发器中使用的表也会被锁定。假设 t1 具有如下定义的触发器：

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 被锁定，因为它们在触发器中被使用

根据 WRITE 锁请求，t1 被锁定以进行写入。
t2 被锁定以进行写入，即使请求的是 READ 锁。这是因为 t2 在触发器中被插入，因此 READ 请求被转换为 WRITE 请求。
t3 被锁定以进行读取，因为它仅在触发器中被读取。
t4 被锁定以进行写入，因为它可能会在触发器中被更新。

表锁定限制和条件

可以安全地使用 KILL 终止正在等待表锁的会话。参见第 15.7.8.4 节，“KILL 语句”。

不能在存储程序中使用 LOCK TABLES 和 UNLOCK TABLES。

performance_schema 数据库中的表不能使用 LOCK TABLES 锁定，但 setup_xxx 表除外。

LOCK TABLES 生成的锁的作用范围是单个 MySQL 服务器。它与 NDB Cluster 不兼容，后者无法在多个 mysqld 实例之间强制执行 SQL 级别的锁。可以改为在 API 应用程序中强制执行锁定。有关更多信息，请参见第 25.2.7.10 节，“与多个 NDB Cluster 节点相关的限制”。

LOCK TABLES 语句生效时，禁止以下语句：CREATE TABLE、CREATE TABLE ... LIKE、CREATE VIEW、DROP VIEW 以及对存储函数、存储过程和事件的 DDL 语句。

对于某些操作，必须访问 mysql 数据库中的系统表。例如，HELP 语句需要服务器端帮助表的内容，而 CONVERT_TZ() 可能需要读取时区表。服务器会根据需要隐式锁定系统表以进行读取，因此无需显式锁定它们。这些表的处理方式如上所述

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

如果要使用 LOCK TABLES 语句对这些表中的任何一个显式放置 WRITE 锁，则该表必须是唯一锁定的表；不能使用同一语句锁定其他表。

通常，不需要锁定表，因为所有单个 UPDATE 语句都是原子的；任何其他会话都不能干扰任何其他当前正在执行的 SQL 语句。但是，在少数情况下，锁定表可能会提供一些优势

如果要对一组 MyISAM 表运行许多操作，则锁定要使用的表会快得多。锁定 MyISAM 表可以加快对它们的插入、更新或删除速度，因为在调用 UNLOCK TABLES 之前，MySQL 不会刷新已锁定表的键缓存。通常，键缓存会在每个 SQL 语句之后刷新。
锁定表的缺点是，除了持有锁的会话之外，任何会话都不能更新 READ 锁定的表（包括持有锁的会话），并且任何会话都不能访问 WRITE 锁定的表。
如果对非事务性存储引擎使用表，则如果要确保在 SELECT 和 UPDATE 之间没有其他会话修改表，则必须使用 LOCK TABLES。此处显示的示例需要 LOCK TABLES 才能安全执行
```
LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer
  SET total_value=sum_from_previous_statement
  WHERE customer_id=some_id;
UNLOCK TABLES;
```
如果没有 LOCK TABLES，则另一个会话可能会在执行 SELECT 和 UPDATE 语句之间在 trans 表中插入新行。

在许多情况下，可以通过使用相对更新（UPDATE customer SET value=value+new_value）或 LAST_INSERT_ID() 函数来避免使用 LOCK TABLES。

在某些情况下，还可以通过使用用户级咨询锁函数 GET_LOCK() 和 RELEASE_LOCK() 来避免锁定表。这些锁保存在服务器的哈希表中，并使用 pthread_mutex_lock() 和 pthread_mutex_unlock() 实现，以提高速度。参见第 14.14 节，“锁定函数”。

有关锁定策略的更多信息，请参见第 10.11.1 节，“内部锁定方法”。