🪴 Obsidian Publish

MySQL 表锁

5分钟阅读

LOCK TABLES

表锁的语法是 lock tables … read/write，可以用 unlock tables 主动释放锁，也可在断开的时自动释放。

需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

例如，线程 A 中执行 lock tables t1 read, t2 write;，那么除了影响其他线程，自己在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。

这都是在服务器层(MySQL Server 层)实现的，和存储引擎无关，它们有自己的用途，并不能替代事务处理。(除了禁用了 autocommint 后可以使用，其他情况不建议使用)：

  • LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
  • UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES 时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁

LOCK TABLES 语法

  • 在用 LOCK TABLES 对 InnoDB 表加锁时要注意，要将 AUTOCOMMIT 设为 0，否则 MySQL 不会给表加锁；
  • 事务结束前，不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK TABLES 会隐含地提交事务；
  • COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁，必须用 UNLOCK TABLES 释放表锁。

正确的方式见如下语句。例如，如果需要写表 t1 并从表 t 读，可以按如下做：

SET AUTOCOMMIT=0; 
LOCK TABLES t1 WRITE, t2 READ, ...; 
[do something with tables t1 and t2 here]; 
COMMIT; 
UNLOCK TABLES;

使用 LOCK TABLES 的场景

给表显示加表级锁(InnoDB 表和 MyISAM 都可以)，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。(与 MyISAM 默认的表锁行为类似)

在用 LOCK TABLES 给表显式加表锁时，必须同时取得所有涉及到表的锁，并且 MySQL 不支持锁升级。也就是说，在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。

其实，在 MyISAM 自动加锁（表锁）的情况下也大致如此，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁的原因。

例如，有一个订单表 orders，其中记录有各订单的总金额 total，同时还有一个订单明细表 order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检查这两个表的金额合计是否相符，可能就需要执行如下两条 SQL：

Select sum(total) from orders; 
Select sum(subtotal) from order_detail;

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，
Order_detail 表可能已经发生了改变。因此，正确的方法应该是：

Lock tables orders read local, order_detail read local; 
Select sum(total) from orders; 
Select sum(subtotal) from order_detail; 
Unlock tables;

在 LOCK TABLES 时加了 local 选项，其作用就是允许当你持有表的读锁时，其他用户可以在满足 MyISAM 表并发插入条件的情况下，在表尾并发插入记录(MyISAM 存储引擎支持并发插入)

反向链接