Lock and Unlock

高性能MySQL建议： 另外， InnoDB 也支持通过特定的语句进行显式锁定，这些语句不属于SQL 规范 : • SELECT... LOCK IN SHARE MODE

• SELECT... FOR UPDATE

MySQL 也支持LOCK TABLES 和UNLOCK TABLES 语句，这是在服务器层实现的，和存储引擎无关。它们有自己的用途，但并不能替代事务处理。如果应用需要用到事务，还是应该选择事务型存储引擎。

经常可以发现，应用已经将表从MylSAM 转换到InnoDB, 但还是显式地使用LOCK``TABLES 语句。这不但没有必要，还会严重影响性能，实际上InnoDB 的行级锁工作得更好。

LOCK TABLES 和事务之间相互影响的话，情况会变得非常复杂，在某些MySQL 版本中甚至会产生无法预料的结果。因此，本书建议，除了事务中禁用了AUTO COMMIT,可以使用LOCK TABLES 之外，其他任何时候都不要显式地执行LOCK 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显式地锁定一个表，那么触发器中使用的任何表都将被隐式地锁定。
UNLOCK TABLES显式地释放当前会话持有的任何表锁。LOCK TABLE在获取新锁之前隐式地释放当前会话持有的任何表锁。
UNLOCK TABLES的另一个用途是释放用FLUSH TABLES WITH READ LOCK语句获得的全局读锁，这使您能够锁定所有数据库中的所有表。详见MySQL官方文档 Section 13.7.6.3, “FLUSH Statement”.
表锁只保护其他会话的不适当读写。持有写锁的会话可以执行表级操作，如DROP TABLE或TRUNCATE TABLE。对于持有读锁的会话，不允许DROP TABLE或TRUNCATE TABLE。
下面的讨论只适用于非临时表。允许(但忽略)对临时表使用LOCK TABLE。创建该表的会话可以自由地访问该表，而不管其他哪些锁定是有效的。不需要锁，因为其他会话无法看到该表。

表锁的获取

要在当前会话中获取表锁，可以使用LOCK TABLES语句，该语句获取元数据锁（元数据锁参见官方文档：Section 8.11.4, “Metadata Locking”）。

有以下几种锁类型：
READ [LOCAL]:

• 持有锁的会话可以读表(但不能写表)。
• 多个会话可以同时获取表的读锁。
• 其他会话可以在不显式获取读锁的情况下读取表。
• LOCAL修饰符允许其他会话在持有锁时执行不冲突的插入语句(并发插入，参见 官方文档Section 8.11.3, “Concurrent Inserts”)。但是，如果要在持有锁时使用服务器外部的进程操作数据库，则不能使用READ LOCAL。对于InnoDB表，READ LOCAL和READ是一样的。

[LOW_PRIORITY] WRITE :

• 持有锁的会话可以读写表。
• 只有持有锁的会话才能访问表。在锁释放之前，其他会话不能访问它。
• 当写锁被持有时，其他会话对表的锁请求被阻塞。
• LOW_PRIORITY修饰符没有效果。在以前的MySQL版本中，它会影响锁行为，但现在不再是这样了。现在不赞成使用它，它的使用会产生警告。使用WRITE而无需LOW_PRIORITY。

写锁通常比读锁具有更高的优先级，以确保尽快处理更新。这意味着，如果一个会话获得一个读锁，然后另一个会话请求一个写锁，那么后续的读锁请求将一直等待，直到请求写锁的会话获得锁并释放它。（若max_write_lock_count系统变量的值较小，可能会出现此策略的异常，详见官方文档：Section 8.11.4, “Metadata Locking”）
如果由于任意表上的其他会话持有锁，LOCK TABLES语句必须等待，那么它将阻塞，直到所有锁能被获取。
需要锁的会话必须在一条LOCK TABLES语句中获得它需要的所有锁。当这样获得的锁被持有时，会话只能访问被锁的表。例如，在下面的语句序列中，由于test1没有被锁在LOCK TABLES语句中，所以在尝试访问test1时发生错误:

mysql> LOCK TABLES t1 READ;
mysql> select * from test1;
ERROR 1100 (HY000): Table 'test1' was not locked with LOCK TABLES

INFORMATION_SCHEMA数据库中的表是一个例外。即使会话持有从LOCK TABLES获得的表锁，也可以在不显式锁定的情况下访问它们。
您不能使用相同的名称在单个查询中多次引用锁定的表。使用别名代替，对表和每个别名获得单独的锁:

mysql> lock table test1 write, test1 as test1_read read;
mysql> insert into test1 select * from test1;
ERROR 1100 (HY000): Table 'test1' was not locked with LOCK TABLES
mysql> insert into test1 select * from test1 as test1_read;

第一次插入时发生错误，因为有两个对锁定表的相同名称的引用。第二次插入成功是因为对表的引用使用不同的名称。
如果您的语句通过别名引用表，则必须使用相同的别名锁定表。如果不指定别名，锁定表将不起作用:

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;

当应用于已分区的表时，LOCK TABLES 或 UNLOCK TABLES总是锁定或解除锁定整个表;这些语句不支持分区锁。

表锁释放

当会话持有的表锁被释放时，它们都同时被释放。会话可以显式地释放锁，也可以在某些条件下隐式地释放锁。

• 会话可以使用UNLOCK TABLES显式地释放其锁。
• 如果一个会话发出一个LOCK TABLES语句来在已经持有锁的情况下获取锁，那么在授予新锁之前，它将隐式地释放现有的锁。
• 如果一个会话开始了一个事务(例如，使用START TRANSACTION)，就会执行一个隐式的UNLOCK TABLES，这将导致释放现有的锁。(有关表锁定和事务之间交互的更多信息，请参见表锁定和事务的交互。)

如果客户端会话的连接终止，无论是否正常，服务器都会隐式释放会话持有的所有表锁(事务性和非事务性)。如果客户端重新连接，锁将不再有效。此外，如果客户端有一个活动事务，服务器在断开连接时回滚事务，如果发生重新连接，则新会话开始时会启用autocommit。出于这个原因，客户可能希望禁用自动重新连接。当自动重新连接生效时，如果发生重新连接，客户端不会收到通知，但是任何表锁或当前事务都将丢失。禁用自动重新连接后，如果连接断开，则下一个发出的语句将出现错误。客户端可以检测错误并采取适当的操作，如重新获取锁或重新执行事务。详见官方文档：Section 27.7.19, “C API Automatic Reconnection Control”.
如果您在一个锁定的表上使用ALTER TABLE，它可能会被解锁。例如，如果尝试第二个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不释放全局读锁。参见官方文档：Section 13.7.6.3, “FLUSH Statement”.
• 其他隐式导致事务提交的语句不会释放现有的表锁。有关此类语句的列表，参见事务隐式提交。
• 在支持事务的表(如InnoDB表)中使用LOCK TABLES和UNLOCK TABLES的正确方法是在事务开始时使用SET autocommit = 0 (not 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因为在触发器中使用而被锁定:

• 根据写锁请求，t1被锁定进行写操作。
• t2被锁定为写，即使请求是读锁。这是因为t2在触发器中被插入，所以读请求被转换为写请求。
• t3被锁定为读取，因为它只从触发器中读取。

• t4为写锁定，因为它可能在触发器中更新。

  表锁定的限制和条件

  您可以安全地使用KILL来终止正在等待表锁的会话。参见官方文档 Section 13.7.6.4, “KILL Statement”.。
  不能在存储程序中使用LOCK TABLES和UNLOCK TABLES。
  除了setup_xxx表之外，performance_schema数据库中的表不能被LOCK TABLES锁定。
  在LOCK TABLES语句生效时，禁止执行以下语句:CREATE TABLE,CREATE TABLE…例如，CREATE VIEW、DROP VIEW和DDL语句，存储函数、过程和事件。
  对于某些操作，必须访问mysql数据库中的系统表。例如，HELP语句需要服务器端帮助表的内容，CONVERT_TZ()可能需要读取时区表。服务器隐式地锁定系统表，以便根据需要进行读取，这样就不需要显式地锁定它们。这些表格的处理方法如下:

  mysql.help_category
  mysql.help_keyword
  mysql.help_relation
  mysql.help_topic
  mysql.proc
  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在调用UNLOCKTABLES之前不会刷新锁定表的键缓存。通常，在每条SQL语句之后都会刷新键缓存。

• 锁定表的缺点是，没有会话可以更新读锁表(包括持有锁的表)，并且除了持有锁的表之外，没有会话可以访问写锁表。

如果您将表用于非事务性存储引擎，则如果希望确保没有其他会话修改SELECT和UPDATE之间的表，则必须使用LOCKTABLES。这里显示的例子需要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()实现，以实现高速。参见官方文档 Section 12.14, “Locking Functions”.

参考 https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html#lock-tables-and-triggers