LOCK TABLES 和 UNLOCK TABLES 语句

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

UNLOCK TABLES

MySQL使客户端会话能够显式获取表锁，以便与其他会话协作访问表，或者防止其他会话在会话需要互斥访问的期间修改表。会话只能为其自身获取或释放锁。一个会话无法获取另一会话的锁，也不能释放另一会话持有的锁。

在更新表时，锁可用于模拟事务或提高速度。表锁定限制和条件中对此进行了详细说明。

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

对于视图锁定，LOCK TABLES将视图中使用的所有基本表添加到要锁定的表集中，并自动锁定它们。如果LOCK TABLES使用显式锁定表，则触发器中使用的任何表也会隐式锁定，如 LOCK TABLES和Triggers中所述。

如果使用显式锁定表LOCK TABLES，则任何与外键约束相关的表都将被打开并隐式锁定。对于外键检查，LOCK TABLES READ将在相关表上使用共享的只读锁（）。对于级联更新，LOCK TABLES WRITE将对操作中涉及的相关表采取无共享写锁定（）。

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

另一个用途UNLOCK TABLES是释放通过该FLUSH TABLES WITH READ LOCK语句获取的全局读取锁定，这使您可以锁定所有数据库中的所有表。请参见“ FLUSH声明”。（如果您拥有可以及时拍摄快照的文件系统（例如Veritas），这是获取备份的一种非常方便的方法。）

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

以下讨论仅适用于非TEMPORARY表。LOCK TABLES允许（但忽略）TEMPORARY表。可以通过创建表的会话自由访问该表，而不考虑可能执行了哪些其他锁定。无需锁定，因为没有其他会话可以看到该表。

• 表锁获取
• 桌锁释放
• 表锁定与事务的交互
• 锁定表和触发器
• 表锁定限制和条件

表锁获取

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

可以使用以下锁定类型：

READ[LOCAL]锁：

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

[LOW_PRIORITY] WRITE锁：

• 持有锁的会话可以读取和写入表。
• 只有持有锁的会话才能访问该表。在释放锁之前，没有其他会话可以访问它。
• 保持锁定状态时，其他会话对表的锁定请求将阻塞WRITE。
• 该LOW_PRIORITY修饰符无效。在以前的MySQL版本中，它影响了锁定行为，但现在不再如此。现在已弃用它，并且使用它会产生警告。使用WRITE不使用LOW_PRIORITY代替。

WRITE锁通常比READ锁具有更高的优先级，以确保尽快处理更新。这意味着，如果一个会话获取了一个READ锁，然后另一个会话请求了一个WRITE锁，则随后的READ锁请求将一直等待，直到请求该WRITE锁的会话已获取并释放了该锁。（对于较小的max_write_lock_count系统变量值，可能会发生此策略的异常；请参见“元数据锁定”。）

如果该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执行隐式操作，这将导致释放现有的锁。（有关表锁定和事务之间的交互的其他信息，请参阅表锁定和事务的交互。）

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

注意

如果ALTER TABLE在锁定的桌子上使用，它可能会被解锁。例如，如果您尝试第二次ALTER TABLE操作，则结果可能是error 。要解决此问题，请在第二次更改之前再次锁定表。另请参见第B.4.6.1节“ ALTER TABLE的问题”。Table 'tbl_name' was not locked with LOCK TABLES

表锁定与事务的交互

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不释放全局读取锁定。请参见“ FLUSH声明”。
• 其他隐式导致事务提交的语句不会释放现有的表锁。有关此类语句的列表，请参见“START TRANSACTION，COMMIT和ROLLBACK语句”。

• 正确的方法使用LOCK TABLES和UNLOCK TABLES对事务表，如InnoDB表，是用开始交易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，并且死锁很容易发生。InnoDB如果则根本不获取内部表锁autocommit = 1，以帮助旧应用程序避免不必要的死锁。

• ROLLBACK不释放表锁。

锁定表和触发器

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

• t1被锁定以根据WRITE锁定请求进行写入。
• t2被锁定以进行写入，即使请求是READ锁定的。发生这种情况是因为t2将其插入到触发器中，所以READ请求被转换为WRITE请求。
• t3被锁定以读取，因为它仅从触发器内部读取。
• t4已被锁定以进行写入，因为它可能会在触发器中更新。

表锁定限制和条件

您可以安全地用于KILL终止正在等待表锁的会话。请参见“ KILL语句”。

LOCK TABLES并且UNLOCK TABLES不能在存储的程序中使用。

在表performance_schema的数据库不能被锁定LOCK TABLES，除了表。setup_xxx

下面的语句是禁止的，而一个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

如果要WRITE使用一条LOCK TABLES语句在这些表中的任何一个上显式地放置一个锁，则该表必须是唯一一个被锁定的表。没有其他表可以使用同一语句锁定。

通常，您不需要锁定表，因为所有单个UPDATE语句都是原子的。没有其他会话可以干扰任何其他当前正在执行的SQL语句。但是，在某些情况下，锁定表可能会带来好处：

• 如果要在一组MyISAM表上运行许多操作，则锁定要使用的表要快得多。锁定MyISAM表可以加快对其上的插入，更新或删除操作，因为MySQL在UNLOCK TABLES调用之前不会刷新锁定表的键高速缓存。通常，在每个SQL语句之后刷新键高速缓存。

  锁定表的不利之处在于，没有会话可以更新READ锁定表（包括持有锁的表），并且没有会话可以访问拥有WRITE锁的表以外的锁定表。

• 如果要将表用于非事务性存储引擎，则必须使用LOCK TABLES来确保没有其他会话修改a 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，则另一个会话可能会trans在执行SELECTand UPDATE语句之间在表中插入新行。

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

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

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