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

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 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]
  | WRITE
}

UNLOCK {TABLE | TABLES}

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

锁可以用来模拟事务或在更新表时提高速度。这方面的详细信息请参阅Table-Locking Restrictions and Conditions。

LOCK TABLES 明确地为当前客户端会话获取表锁。可以为基本表或视图获取表锁。你必须拥有LOCK TABLES特权，以及每个要锁定的对象的SELECT特权。

对于视图锁定，LOCK TABLES 将视图中使用的所有基本表添加到要锁定的表集中，并自动锁定它们。对于任何被锁定的视图 underlying 表，LOCK TABLES 检查视图定义者（对于SQL SECURITY DEFINER视图）或调用者（对于所有视图）对表的合法特权。

如果使用LOCK TABLES显式锁定一个表，任何在触发器中使用的表也将隐式锁定，如LOCK TABLES and Triggers中所述。

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

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

另一个使用UNLOCK TABLES的方法是释放由FLUSH TABLES WITH READ LOCK语句获取的全局读锁定，这使得你可以锁定所有数据库中的所有表。请参阅第15.7.8.3节，“FLUSH Statement”。（这是一种非常方便的方式来获取备份，如果你使用的是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语句（并发插入），但如果您计划使用外部进程来操作数据库，而持有锁，则不能使用READ LOCAL。对于InnoDB表，READ LOCAL与READ相同。

WRITE锁：

持有锁的会话可以读取和写入该表。
只有持有锁的会话才能访问该表，直到锁被释放其他会话不能访问该表。
其他会话对该表的锁请求将阻塞，直到WRITE锁被释放。

WRITE 锁通常具有更高的优先级，确保更新尽快被处理。这意味着，如果一个会话获得了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

信息架构数据库中的表是例外。它们可以在会话持有使用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，导致现有锁定被释放。有关表锁定和事务的交互信息，请见Table Locking and Transactions Interaction。

如果客户端会话连接终止，是否正常或非正常，服务器将隐式释放该会话持有的所有表锁定（事务性和非事务性）。如果客户端重新连接，那些锁定将不再生效。此外，如果客户端有活动事务，服务器在断开连接时回滚事务，并且如果重新连接，新的会话将以自动提交模式开始。因此，客户端可能想禁用自动重连。启用自动重连时，客户端不会收到重新连接的通知，但任何表锁定或当前事务都将丢失。禁用自动重连时，如果连接断开，下一个语句执行将出现错误。客户端可以检测错误并采取适当行动，如重新获取锁定或redo事务。见Automatic Reconnection Control。

Note

如果对已锁定的表执行ALTER TABLE操作，它可能会被解锁。例如，如果您尝试第二个ALTER TABLE操作，结果可能是错误Table 'tbl_ name' was not locked with LOCK TABLES。为了解决这个问题，在第二次修改之前重新锁定表。请参见第B.3.6.1节，“ALTER TABLE问题”。

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

LOCK TABLES不是事务安全的，并且在尝试锁定表之前隐式地提交任何活动的事务。
UNLOCK TABLES隐式提交任何活动事务，但只有在使用LOCK TABLES获取表锁定的情况下。例如，在以下语句集中，UNLOCK TABLES释放全局读锁，但不提交事务，因为没有在 effect 的表锁定：
```
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。您 shouldn't 设置autocommit = 1，因为这样InnoDB会在调用LOCK TABLES后立即释放其内部表锁，从而可能导致死锁。InnoDB在autocommit = 1的情况下不获取内部表锁，以帮助老应用程序避免不必要的死锁。
ROLLBACK不会释放表锁。

如果您使用LOCK TABLES显式锁定一个表，那么触发器中使用的任何表也将隐式锁定：

锁定是在与使用LOCK TABLES语句同时获取的，explicitly。
触发器中使用表的锁定取决于该表是否仅用于读取。如果是，则只需要读锁。否则，写锁将被使用。
如果使用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 是以写锁定的，因为是由 WRITE 锁请求所导致的。
t2 对写入锁定，因为请求是对该表的读取锁定，但是在触发器中插入了该表，因此读取请求被转换为写入请求。
t3 对读取锁定，因为在触发器中只从该表读取数据。
t4 对写入锁定，因为在触发器中可能会更新该表。

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

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

性能_schema 数据库中的表不能使用LOCK TABLES锁定，except 是setup_xxx表。

使用LOCK TABLES生成的锁定的作用域是一个单个MySQL服务器。它不兼容NDB集群，因为NDB集群没有办法在多个mysqld实例之间强制SQL级别的锁定。您可以在API应用程序中实现锁定。请参阅第25.2.7.10节，“多个NDB集群节点的限制”，获取更多信息。

在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 表上运行多个操作，那么锁定这些表将大大加速插入、更新或删除操作，因为 MySQL 在锁定表时不会flush 键缓存，直到调用 UNLOCK TABLES。通常，键缓存在每个 SQL 语句执行后被flush。

锁定表的缺点是，任何会话都不能更新一个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表中。

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

您还可以在某些情况下避免锁定表格，例如通过使用用户级别的建议锁定函数GET_LOCK()和RELEASE_LOCK()。这些锁定保存在服务器的哈希表中，并使用pthread_mutex_lock()和pthread_mutex_unlock()实现高速度。请参阅第14.14节，“锁定函数”。

请查看第10.11.1节，“内部锁定方法”，了解更多关于锁定策略的信息。

PREV HOME UP NEXT