MySQL：分析表锁的常见问题

表锁

若我们想对 user 表加锁，则加锁语句：

//读锁
lock tables user read;//写锁
lock tables user write;

它们遵循：读读共享、读写互斥、写写互斥
在这里，我们主要讨论以下话题：表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作

假设有两个表 t1 和 t2，当线程 A 执行了 lock tables t1 read, t2 write;这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。不允许写 t1 ，不能访问其他表。----选自小林Coding-MySQL中的锁

1. 为什么持有 t1 读锁的线程 A，自己也不能写 t1？

这主要是为了防止“锁升级”导致的死锁。

我们先来理解 LOCK TABLES ... READ 的含义。当线程 A 执行 LOCK TABLES t1 READ 时，它其实是在向系统做出一个“承诺”：“在接下来的操作中，我只会读取 t1 表，我需要一个稳定不变的数据视图。”

如果系统允许线程 A 在持有读锁（共享锁）的情况下，再去尝试写入（需要排他锁），会发生什么？我们来看一个经典的死锁场景：

线程 A 执行 LOCK TABLES t1 READ;，成功获得了 t1 的读锁。

线程 B 在此时也执行 LOCK TABLES t1 READ;，它也可以成功获得 t1 的读锁，因为读锁是共享的。

现在，线程 A 和 B 都持有 t1 的读锁。

线程 A 突然想更新数据，执行 UPDATE t1 SET ...;。要执行这个操作，它需要将自己的读锁升级为写锁。但是，由于线程 B 还持有读锁，线程 A 的写锁请求无法被满足，必须等待线程 B 释放读锁。于是，线程 A 开始阻塞等待。

线程 B 此时也想更新数据，执行 UPDATE t1 SET ...;。同样，它也需要将自己的读锁升级为写锁。但它发现线程 A 也持有读锁，所以它也必须等待线程 A 释放。于是，线程 B 也开始阻塞等待。

结果： 线程 A 在等线程 B 释放锁，而线程 B 也在等线程 A 释放锁。双方互相等待，形成了一个完美的死锁。

为了从根本上杜绝这种“读锁升级”造成的死锁，MySQL 的设计者制定了一条简单而粗暴的规则：一旦你以 READ 模式锁定了某张表，那么在解锁之前，你（当前线程）就彻底失去了对这张表进行写操作的权限。 如果你预见到可能会写入，就必须在一开始申请 WRITE 锁。

2. 为什么线程 A 也不能访问其他未锁定的表？

这个规则同样是为了防止多表操作时产生死锁。

LOCK TABLES 命令的设计是：在一个会话中，一旦你决定使用表锁，就必须在一条命令里，把你接下来需要用到的所有表都“登记”并锁定起来。

如果你只锁了 t1 和 t2，却在后续又去尝试访问 t3，这同样可能导致死锁。我们再看一个场景：

线程 A 执行 LOCK TABLES t1 WRITE;，成功锁定了 t1。

线程 B 在此时执行 LOCK TABLES t3 WRITE;，成功锁定了 t3。

现在，线程 A 持有 t1 的写锁，线程 B 持有 t3 的写锁。

线程 A 执行 SELECT * FROM t3;。它发现 t3 被线程 B 锁着，于是开始等待。

线程 B 执行 SELECT * FROM t1;。它发现 t1 被线程 A 锁着，于是也开始等待。

结果： 又一个死锁产生了。

为了避免这种情况，LOCK TABLES 命令要求你“一次性”获取所有你需要的锁。当你执行LOCK TABLES t1 READ, t2 WRITE;后，MySQL 会认为这就是你本次操作需要用到的所有表的“清单”。

对于清单上的表（t1, t2），你拥有了指定的权限。

对于所有未在清单上的表，MySQL 会隐式地认为你不需要访问它们，为了防止你后续的即兴操作引发死锁，干脆禁止你访问。