LOCK TABLES和UNLOCK TABLES

2020-11-19

语法：

-- 锁表
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}
-- 释放表锁
UNLOCK TABLES

READ [LOCAL] 锁：

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

WRITE 锁：

  • 持有锁的会话可以读取和写入表。
  • 只有持有锁的会话才能访问该表。在释放锁之前，没有其他会话可以访问它。
  • 保持锁定状态时，其他会话对表的锁定请求将阻塞。
  • WRITE锁通常比READ锁具有更高的优先级，以确保尽快处理更新。这意味着，如果一个会话获取了一个READ锁，然后另一个会话请求了一个WRITE锁，则随后的 READ锁请求将一直等待，直到请求该WRITE锁的会话已获取并释放了该锁。（对于较小的max_write_lock_count系统变量值，可能会发生此策略的异常；）

注：

  • 表锁只能防止其他会话进行不适当的读取或写入。持有WRITE 锁的会话可以执行表级操作
  • 在更新表时，锁可用于模拟事务或提高速度。可以为基本表或视图获取表锁
  • LOCK TABLES语句由于任何其他表上的其他会话持有的锁而必须等待，则它将阻塞直到可以获取所有锁为止。
  • 需要锁的会话必须在单个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;
  • 如果语句通过别名引用表，则必须使用相同的别名锁定表。如果不指定别名，则无法锁定表 
    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显式释放其锁。会释放当前会话持有的所有表锁
  • 如果会话开始事务，则将执行隐式操作UNLOCK TABLES，这将导致释放现有的锁。
  • 如果会话在已持有锁的同时发出LOCK TABLES获取锁，则会在授予新锁之前隐式释放其现有锁。
  • 对于视图锁定，LOCK TABLES将视图中使用的所有基本表添加到要锁定的表集中，并自动锁定它们
  • 如果客户端会话的连接正常或异常终止，服务器将隐式释放该会话持有的所有表锁（事务性和非事务性）。如果客户端重新连接，则锁定的时间更长。另外，如果客户端有活动的事务，则服务器在断开连接时会回滚事务，如果发生重新连接，则新会话将从启用自动提交开始。因此，客户端可能希望禁用自动重新连接。启用自动重新连接后，如果发生重新连接，则不会通知客户端，但是会丢失任何表锁或当前事务。在禁用自动重新连接的情况下，如果连接断开，则下一条发出的语句将发生错误。客户端可以检测到错误并采取适当的措施，例如重新获得锁或重做事务。
  • 如果在锁定的表上使用ALTER TABLE，它可能会被解锁。如果您尝试第二次ALTER TABLE 操作，则结果可能是error。要解决此问题，请在第二次更改之前再次锁定表。
  • LOCK TABLES不是事务安全的，而是在尝试锁定表之前隐式提交任何活动事务。
  • UNLOCK TABLES隐式提交任何活动事务，但前提是已用LOCK TABLES获取表锁。例如，在以下语句集中， UNLOCK TABLES释放全局读取锁，但由于没有有效表锁，因此不提交事务 
    FLUSH TABLES WITH READ LOCK;
START TRANSACTION;
SELECT ... ;
UNLOCK TABLES;
  • LOCK TABLES或UNLOCK TABLES，当应用于分区表时，始终锁定或解锁整个表；这些语句不支持分区锁
  • 开始事务（例如，使用 START TRANSACTION）会隐式提交任何当前事务并释放现有的表锁。
  • FLUSH TABLES WITH READ LOCK 获得全局读锁定，而不是表锁，所以它不会受到相同的行为LOCK TABLES和UNLOCK TABLES相对于表锁定和隐式的提交。START TRANSACTION不释放全局读取锁定
  • UNLOCK TABLES可以释放通过FLUSH TABLES WITH READ LOCK 语句获取的全局读取锁
  • 其他隐式导致事务提交的语句不会释放现有的表锁
  • LOCK TABLES和UNLOCK TABLES不能在存储的程序中使用
  • performance_schema数据库中的表不能通过LOCK TABLES锁定

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

  • 使用LOCK TABLES显式锁定表，则触发器中使用的任何表也将隐式锁定：
  • 这些表锁与使用LOCK TABLES语句显式获取的锁在同一时间获取。
  • 触发器中使用的表上的锁取决于该表是否仅用于读取。如果是这样，则读锁就足够了。否则，将使用写锁。
  • 如果使用LOCK TABLES显式锁定了表以进行读取，但由于表在触发器中的出现需要隐式写锁定将导致表的显式读锁定请求转换为写锁定请求

某些情况下可能会带来的好处：

  • 如果要在一组MyISAM表上运行许多操作 ，则锁定要使用的表要快得多。锁定 MyISAM表可加快对其上的插入，更新或删除操作，因为MySQL直到UNLOCK TABLES被调用之前不会刷新锁定表的键高速缓存 。通常，在每个SQL语句之后刷新键高速缓存。
  • 如果要将表用于非事务性存储引擎，则必须使用LOCK TABLES来确保没有其他会话修改SELECT和UPDATE之间的表

缺点：

  • 锁定表的缺点是，没有会话可以更新READ锁定的表（包括持有锁的表），没有会话可以访问拥有 WRITE锁定表的表以外的其他锁定表。
CATALOG
标签
mysql redis sqlserver
{/if}