MySQL :: MySQL 8.3 Reference Manual :: 15.3.6 LOCK TABLES and UNLOCK TABLES Statements

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / LOCK TABLES and UNLOCK TABLES Statements

15.3.6 锁定表和解锁表语句

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

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] 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表。LOCK TABLES对于TEMPORARY表是允许的（但被忽略）。该表可以被创建该表的会话自由访问，无论其他锁定是否生效。不需要锁定，因为其他会话无法看到该表。

表锁定获取

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

以下锁类型可用：

READ [LOCAL] 锁：

持有锁的会话可以读取表（但不能写入）。
多个会话可以同时获取表的 READ 锁。
其他会话可以在不明确获取 READ 锁的情况下读取表。
LOCAL 修饰符启用了其他会话的并发插入语句（并发插入）执行，而锁被持有。（见第 10.11.3 节，“并发插入”）。但是，如果您计划使用服务器外部进程来操作数据库，而您持有锁，则不能使用 READ LOCAL。对于 InnoDB 表，READ LOCAL 与 READ 相同。

[LOW_PRIORITY] WRITE 锁：

持有锁的会话可以读取和写入表。
只有持有锁的会话可以访问表。其他会话无法访问表，直到锁被释放。
其他会话对表的锁请求将被阻塞，直到 WRITE 锁被释放。
LOW_PRIORITY 修饰符不再影响锁定行为，现在已弃用，并生成警告。请使用 WRITE 而不是 LOW_PRIORITY 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，从而释放现有的锁。（有关表锁定和事务之间的交互的更多信息，请参阅表锁定和事务之间的交互。）

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

Note

如果您在锁定的表格上使用 ALTER TABLE，它可能会被解锁。例如，如果您尝试第二个 ALTER TABLE 操作，结果可能是一个错误 表 'tbl_name' 未锁定 LOCK TABLES。要处理这种情况，请在第二次修改之前重新锁定表格。请参阅 B.3.6.1 节，“ALTER TABLE 问题”。

表锁定和事务之间的交互

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

LOCK TABLES 不是事务安全的，并且隐式提交任何活动事务，然后尝试锁定表格。
释放表隐式提交任何活动事务，但仅当锁定表已用于获取表锁时。例如，在以下语句集中，释放表释放全局读锁，但不提交事务，因为没有表锁在生效：
```
FLUSH TABLES WITH READ LOCK;
START TRANSACTION;
SELECT ... ;
UNLOCK TABLES;
```
开始事务（例如，使用开始事务）隐式提交任何当前事务并释放现有的表锁。
刷新表 WITH 读锁获取全局读锁，而不是表锁，因此它不受锁定表和释放表的表锁定和隐式提交行为的影响。例如，开始事务不释放全局读锁。见第 15.7.8.3 节，“刷新语句”。
其他语句隐式导致事务提交不释放现有的表锁。有关此类语句的列表，请参见第 15.3.3 节，“隐式提交语句”。
正确使用锁定表和释放表与事务表（例如 InnoDB 表）是开始事务使用 SET autocommit = 0（不是开始事务），然后是锁定表，并且不调用释放表，直到您明确提交事务。例如，如果您需要写入表 t1 并从表 t2 读取，可以这样做：
```
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
```
当您调用锁定表时，InnoDB 内部获取其自己的表锁，MySQL 获取其自己的表锁。InnoDB 在下一个提交时释放其内部表锁，但对于 MySQL 来说，需要调用释放表来释放其表锁。您不应该将autocommit = 1，因为那样 InnoDB 将在锁定表调用后立即释放其内部表锁，死锁很容易发生。InnoDB 不会获取内部表锁，如果autocommit = 1，以帮助旧应用程序避免不必要的死锁。
回滚不释放表锁。

锁定表和触发器

如果您使用锁定表显式锁定表，则触发器中使用的任何表也将隐式锁定：

锁是在与锁定表语句同时获取的。
触发器中使用的表的锁取决于该表是否仅用于读取。如果是，则读锁足够。否则，使用写锁。
如果表被明确锁定为读取使用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 也将被锁定，因为它们在触发器中被使用：

t1 被锁定为写入，因为它出现在触发器中。
t2 被锁定为写入，即使请求的是读锁。这是因为 t2 在触发器中被插入，因此读锁请求被转换为写锁请求。
t3 被锁定为读取，因为它仅在触发器中被读取。
t4 被锁定为写入，因为它可能在触发器中被更新。

表锁定限制和条件

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

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

性能模式数据库中的表不能使用 LOCK TABLES 锁定，除了 setup_xxx 表。

由 LOCK TABLES 生成的锁的范围是一个单个 MySQL 服务器。它与 NDB Cluster 不兼容，NDB Cluster 无法在多个实例之间强制执行 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 语句在任何这些表上显式地锁定写入锁，那么该表必须是唯一被锁定的表；不能与同一语句锁定其他表。

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

如果您要在一组 MyISAM 表上运行许多操作，那么锁定这些表将非常快。锁定 MyISAM 表可以加速在它们上面的插入、更新或删除操作，因为 MySQL 不会在锁定表上刷新键缓存，直到 UNLOCK TABLES 被调用。通常，键缓存是在每个 SQL 语句后刷新的。

锁定表的缺点是没有会话可以更新 READ-锁定的表（包括持有锁的会话），并且没有会话可以访问 WRITE-锁定的表，除了持有锁的会话。
如果您使用的是非事务存储引擎的表，您必须使用 LOCK TABLES，以确保在 SELECT 和 UPDATE 之间没有其他会话修改表。下面的示例需要 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 节，“Locking Functions”。

请参阅第 10.11.1 节，“Internal Locking Methods”，以获取更多关于锁定策略的信息。

PREV HOME UP NEXT