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 t1READ ; 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 tWRITE , tAS t1READ ; mysql>INSERT INTO tSELECT *FROM t; ERROR 1100: Table 't' was not locked with LOCK TABLES mysql>INSERT INTO tSELECT *FROM tAS t1;
第一个错误发生是INSERT
因为锁定表有两个对相同名称的引用。第二个INSERT
成功,因为对该表的引用使用了不同的名称。
如果您的语句通过别名引用表,则必须使用相同的别名锁定表。如果不指定别名,则无法锁定表:
mysql>LOCK TABLE tREAD ; mysql>SELECT *FROM tAS myalias; ERROR 1100: Table 'myalias' was not locked with LOCK TABLES
相反,如果使用别名锁定表,则必须在使用该别名的语句中引用该表:
mysql>LOCK TABLE tAS myaliasREAD ; mysql>SELECT *FROM t; ERROR 1100: Table 't' was not locked with LOCK TABLES mysql>SELECT *FROM tAS 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 t1WRITE , t2READ , ...;...do somethingwith 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 t1WRITE , t2READ ;
如果t1
或t2
有任何触发器,触发器中使用的表也将被锁定。假设t1
有一个定义如下的触发器:
CREATE TRIGGER t1_a_insAFTER INSERT ON t1FOR EACH ROW BEGIN UPDATE t4SET count = count+1WHERE id =NEW .id ANDEXISTS (SELECT aFROM t3);INSERT INTO t2VALUES (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
来确保没有其他会话修改aSELECT
和之间的表UPDATE
。此处显示的示例要求LOCK TABLES
安全执行:LOCK TABLES transREAD , customerWRITE ;SELECT SUM(value )FROM transWHERE customer_id=some_id;UPDATE customerSET total_value=sum_from_previous_statementWHERE customer_id=some_id;UNLOCK TABLES ;如果不使用
LOCK TABLES
,则另一个会话可能会trans
在执行SELECT
andUPDATE
语句之间在表中插入新行。
LOCK TABLES
通过使用相对更新()或函数,可以避免在许多情况下使用。UPDATE customer SET value=value+new_value
LAST_INSERT_ID()
在某些情况下,您还可以通过使用用户级咨询锁定功能GET_LOCK()
和来避免锁定表RELEASE_LOCK()
。这些锁保存在服务器的哈希表中,pthread_mutex_lock()
并pthread_mutex_unlock()
以高速实现。请参见“锁定函数”。
有关锁定策略的更多信息,请参见“内部锁定方法”。