MySQL的SQL语句事务性语句和锁定语句5LOCKTABLES和UNLOCKTABLES 语句2

表锁释放

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

● 会话可以通过 UNLOCK TABLES 语句显式释放锁。

● 如果会话在已经持有锁的情况下发出 LOCK TABLES 语句以获取锁,则在授予新锁之前,将隐式释放其现有锁。

● 如果会话开始一个事务(例如,使用 START TRANSACTION),会隐式执行 UNLOCK TABLES 释放现有锁。

如果客户端会话连接终止,无论是正常终止还是异常终止,服务器都会隐式释放会话持有的所有表锁(事务性和非事务性)。如果客户端重新连接,锁将不再有效。另外,如果客户端有一个活动事务,服务器在断开连接时回滚事务,如果发生重新连接,新会话将以启用自动提交开始。因此,客户端可能希望禁用自动重新连接。在自动重新连接生效后,如果重新连接发生,但任何表锁或当前事务丢失,则不会通知客户端。禁用自动重新连接时,如果连接断开,则下一条语句将出错。客户端可以检测到错误并采取适当的操作,例如重新获取锁或重做事务。

注意

如果在锁定的表上使用 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 将释放全局读锁,但不会提交事务,因为没有表锁处于生效状态:

1. FLUSH TABLES WITH READ LOCK;
2. START TRANSACTION;
3. SELECT ... ;
4. UNLOCK TABLES;

● 开始一个事务(例如,使用 START TRANSACTION)会隐式地提交任何当前事务并释放现有的表锁。

● FLUSH TABLES WITH READ LOCK 获取全局读锁,而不是表锁,因此在表锁定和隐式提交方面,它不受与 LOCK TABLES 和 UNLOCK TABLES 相同的行为的约束。例如,START TRANSACTION 不会释放全局读锁。

● 其他隐式导致提交事务的语句不会释放现有的表锁。

对事务性表(如InnoDB 表)使用 LOCK TABLES 和 UNLOCK TABLES 的正确方法是,以 SET autocommit = 0(不是 START TRANSACTION)开始一个事务,其后紧跟 LOCK TABLES 语句,并在显式提交事务之前不调用 UNLOCK TABLES。例如,如果需要写入表 t1 并从表 t2 读取,可以执行以下操作:

1. SET autocommit=0;
2. LOCK TABLES t1 WRITE, t2 READ, ...;
3. ... do something with tables t1 and t2 here ...
4. COMMIT;
5. 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:

1. LOCK TABLES t1 WRITE, t2 READ;

如果 t1 或 t2 有任何触发器,则触发器中使用的表也会被锁定。假设 t1 有一个定义如下的触发器:

1. CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW
2. BEGIN
3.   UPDATE t4 SET count = count+1
4.       WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);
5.   INSERT INTO t2 VALUES(1, 2);
6. END;

LOCK TABLES 语句的结果是 t1 和 t2 被锁定,因为它们出现在语句中,而 t3 和 t4 被锁定是因为它们在触发器中使用:

● 根据 WRITE 锁定请求,t1 被锁定用于写入。

● t2 被锁定用于写入,即使请求是 READ 锁。发生这种情况的原因是在触发器中向 t2 有插入数据的操作,因此 READ 请求被转换为 WRITE 请求。

● t3 被锁定用于读取,因为它只从触发器内部读取。

● t4 被锁定用于写入,因为它可能在触发器内更新。

表锁定限制和条件

可以安全地使用 KILL 终止等待表锁的会话。

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

除了 setup_xxx 表之外,performance_schema 数据库中的表不能用 LOCK TABLES 锁定。

当 LOCK TABLES 语句生效时,禁止使用以下语句:CREATE TABLE、CREATE TABLE ... LIKE、CREATE VIEW、DROP VIEW 和存储函数、过程和事件中的 DDL 语句。

有些操作需要访问 mysql 数据库中的系统表。例如,HELP 语句需要服务器端帮助表的内容,CONVERT_TZ() 可能需要读取时区表。服务器根据需要隐式地锁定系统表以供读取,这样不需要显式地锁定它们。以下这些表将按照刚才所述方式处理:

1. mysql.help_category
2. mysql.help_keyword
3. mysql.help_relation
4. mysql.help_topic
5. mysql.time_zone
6. mysql.time_zone_leap_second
7. mysql.time_zone_name
8. mysql.time_zone_transition
9. mysql.time_zone_transition_type

如果要用 LOCK TABLES 语句显式地对这些表添加 WRITE 锁,则该表必须是唯一被锁定的表;不能用同一语句锁定其他表。

通常,不需要锁定表,因为所有单个 UPDATE 语句都是原子的;没有其他会话可以干扰任何其他当前正在执行的 SQL 语句。但是,在以下情况下,锁定表可以提供一些优势:

● 如果要对一组 MyISAM 表运行许多操作,那么锁定要使用的表会使执行快得多。锁定 MyISAM 表可以加快插入、更新或删除这些表的速度,因为在调用 UNLOCK TABLES 之前,MySQL 不会刷新锁定表的键缓存。通常,在每个 SQL 语句之后刷新键缓存。

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

● 如果要将表用于非事务存储引擎,则必须使用 LOCK TABLES 以确保在 SELECT 和 UPDATE 之间没有其他会话修改表。此处显示的示例要求使用 LOCK TABLES 语句以便能安全执行:

1. LOCK TABLES trans READ, customer WRITE;
2. SELECT SUM(value) FROM trans WHERE customer_id=some_id;
3. UPDATE customer
4.   SET total_value=sum_from_previous_statement
5.   WHERE customer_id=some_id;
6. 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() 实现以获取高速度。

官方网址:
https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

MySQL的SQL语句事务性语句和锁定语句5LOCKTABLES和UNLOCKTABLES 语句2

上一篇:oracle查看历史执行过的SQL语句


下一篇:MySQL的SQL语句 -事务性语句和锁定语句(7)- XA 事务 SQL 语句