MySQL的锁

根据加锁的范围，MySQL 里面的锁可以分为三种：全局锁、表级锁和行锁。

全局锁

全局锁对整个数据库实例加锁。加锁方法是使用命令flush tables with read lock(FTWRL)。这个命令可以使整个库处于只读状态。

只读状态的数据库会阻塞：数据更新（数据的增删改）、数据定义语句（建表、修改表结构）和更新类事务的提交语句。

那么什么场景下会用到这把锁呢？

曰：全库备份。

在备份时让数据库处于只读状态会出现几个问题：

1. 如果是备份主库的话，使用到数据库的业务就得暂停（因为不能实现数据的存储更新）；
2. 如果是备份从库的话，备份期间从库只读，不能正常同步主库，这样会导致一段时间的主从不一致。

不加锁行不行？

假设我们现在维护一个用户购买课程系统。这个系统里面有两张表：账户余额表和课程条目表。

如图

图中用户余额表为 u_account，用户课程表为 u_course。

用户购买课程之前，系统对u_account进行了备份，此时因为未执行购买动作，所以u_account中账户余额仍为200。

user_buy() 函数执行了用户的购买动作，执行过后，账户余额为101，u_course表里增加了一门课程。

此时要备份的是 u_course这张表，所以此时课程表里面已经多了一门课程。

最终我们会发现：A用户的账户余额没有减少，课程反倒增加了一门。

这显然是不符合逻辑的。

反过来也一样，如果先备份u_course，再备份u_account，则会发现：用户A的课程没有增加，可是余额却少了。

所以备份如果不加锁，就会出现逻辑问题。原因就在于：备份不同表不是在一个时间点上进行的。

参见前面讲事务隔离级别)那一篇文章，如果我们能实现 repeatable read 这种隔离级别就好了,这样就能保证在事务里前后一致了。

官方自带了逻辑备份工具 mysqldump。当mysqldump命令执行时，使用参数-single-transaction，即可启动一个事务，确保拿到一致性视图。

可是这个命令只适用于支持事务的存储引擎如 InnoDB，MyISAM 是不支持事务的。

对于不支持事务的，只能使用 FTWRL命令了。

现在问题来了， set global readonly=true同样可以实现全库只读，为什么不用这个呢？

原因有二：

1. 修改 global 变量的值影响面更大，会影响到系统中其他的逻辑，如判断主备库。
2. 在处理异常方面：
   1. 如果FTWRL命令出异常，MySQL 会自动释放该全局锁
   2. 如果 set global readonly=true客户端发生异常，则数据库会一直保持只读状态，会导致数据库长期不可写。

表级锁

MySQL 中的表级锁有两种：表锁和元数据锁（MDL，meta data lock）。

表锁

如何加锁？

使用语法：

lock tables … read/write

比如对表T1执行锁读权限，T2执行锁写权限。

如何释放锁？

释放锁的途径有两种：

1. 使用 unlock tables 命令
2. 客户端断开，自动释放

锁会有什么影响？

如果某一个线程A执行了下面的语句

lock tables T1 read, T2 write

则线程A在释放表锁之前，只能执行对 T1 表的读操作，对 T2 表的读写操作（写的权限较高一点，能write则也能read），而其他对T1表进行写的操作会阻塞，对T2表的读写操作也会阻塞（同样也是写的锁级别更高一点，一旦对某表上了写锁，则其他线程的读写操作均会阻塞）。

一句话：读锁只阻塞其他线程写，而写锁则会同时阻塞其他线程读写。

MDL 锁

如何加锁？

无需显式添加，在访问表的时候会自动加上。

当对一个表进行增删改查操作时加读锁，当对一个表做结构变更时，加写锁。

锁会有什么影响？

1. 读锁之间不互斥，多个线程可以同时对一张表进行增删改查操作；

2. 读写锁之间、写锁之间是互斥的，多个线程同时更改表结构时（如加字段），必须串行执行。

例子：

下图是四个Session执行的语句：

sessionA先启动，这时候会给表 t 加一个 MDL 读锁；

sessionB 执行的也是查询操作，所以也需要一把表 t 的 MDL读锁，因为读锁之间不冲突，所以sessionB并不会阻塞；

sessionC执行的是更改表结构操作，给表t增加一个字段f，这个时候要获取的是 MDL 写锁，由于读写锁之间是互斥的，所以sessionC阻塞；

sessionD虽然执行的也是一个查询操作，需要获取一把 MDL 读锁，但是由于排在 sessionC 后面，所以只能也被阻塞。

好了，如果 sessionA 和 sessionB 遇到什么问题，一直挂在那儿，则这张表 t 就会变得既不可读，也不可写。

事务中的 MDL 机制并不是语句结束后就释放锁，而是整个事务都提交后，MDL锁才释放。

一个最佳实践：如果要给一张小表添加字段，则最好是给执行语句设定一个等待时间，如果在等待时间内拿不到锁，就先放弃，交给用户重试，有点像 Java 并发包里面的 trylock 机制。

行锁

锁的粒度从大到小依次为：全局锁、表锁和行锁。

行锁粒度最小，但是并非所有存储引擎都支持行锁，不同的存储引擎行锁的实现方式也不尽相同，比如 MyISAM 就不支持，难怪现在 InnoDB 的风头已经盖过 MyISAM 了。

这部分主要讲 InnoDB 存储引擎的行锁机制。

行锁用来控制多个事务对同一行记录的并发修改。

对于上图中的场景，事务A 更新表 t 中的 id=1和id=2两行的k字段，而事务B更新表 t 中的 id=1 那一行的 k 字段。

可以看出，在 id=1 这一行记录上存在两个事务的并发修改。

那么这个场景会发生什么呢？

事务A在 commit 事务前，事务B会一直阻塞，直到事务A提交之后，事务B的更新操作才能执行。也就是说只有事务 commit 之后，才会释放锁。在InnoDB 事务的实现中，这个就叫两阶段协议。

行锁的最佳实践

如果事务的执行一定要锁多行数据，则需要把最有可能造成冲突的影响并发度的锁尽量往后放，这样是为了减少持有锁的时间。

在这里丁奇举了一个电影院在线业务的例子。

顾客A要在电影院C买票，假如一张电影票 30元。

该业务涉及三个步骤：

1. 顾客A的账户余额减去30元
2. 电影院C的账户上增加30元
3. 记录交易日志

步骤1和2是update操作，3是insert操作。

为了保证交易的原子性，这三条语句应该放在一个事务中，可是如何安排顺序最好呢？

因为是两阶段协议，所以只有事务 commit 之后，才能释放行锁。我们想下最有可能造成冲突的是哪一步呢？

必然是步骤2，因为如果另外有顾客B、D、E、F也同样购票，则他们都会在更新电影院账户余额上冲突。

所以我们把步骤2应该安排到最后。

行锁死锁

使用锁就怕一种现象的产生：死锁。

对于数据库的行锁也不例外。

举个例子。

事务A和事务B并发。

事务A的第一句显然锁住了行 id=1，而事务B的第一句锁住了行 id=2。

事务A的第二句在等待行id=2的锁释放，事务B的第二句在等待行 id=1的锁的释放。

死锁发生了。

死锁常见的化解策略：

1. 等待超时。

   事务A在请求id=2的行锁超时之后，自动放弃。

   在 InnoDB 存储引擎中可以通过设置参数 innodb_lock_wait_timeout来控制超时时间。

2. 主动检测死锁

   发现死锁后，主动回滚死锁链条中的一个事务，让路其他事务执行。

   在 InnoDB 存储引擎中可以通过设置参数 innodb_deadlock_detect的值为on来实现。

下面我们来权衡比较这两种策略。

策略一：核心在设置这个超时时长。设置大了，等待时间过于长；设置小了，容易误判，本来是正常的锁等待，给人家判断成死锁了，造成了不必要的成本。所以这个超时时长太不好设置了，一般采用策略二。

死锁检测机制

那么 InnoDB 引擎是如何检测死锁的呢？

行锁采用hashtable来管理。当前页所在bucket的所有项， 遍历找到当前行的所有锁。构建一个以事务为顶点，锁为边的有向图，判断有向图是否存在环，如果存在环，则说明有死锁产生，时间复杂度为O(n)。

对于多个事务并发更新同一行数据的场景：

每个新来的被堵住的线程都会这样去判断是否因为自己的加入出现了死锁。

如果存在 1000 个线程在等待行锁，则每个线程都会判断1000次，则总的检测操作就是 100w 量级的，这会消耗大量的 CPU 资源。

遇到这种场景怎么办呢？

1. 关掉死锁检测

   这种方法治标不治本，关掉死锁检测就需要业务方不犯错了。

2. 控制并发度

   分在客户端控制并发和服务端控制并发度两种。

   客户端控制并发度：假设只有 600 个客户端，每个客户端有 5 个线程，那么峰值也能达到 3000 左右，服务端压力同样巨大。

   服务端控制并发度：考虑使用中间件。对于相同行的执行，在进入存储引擎前排队。

目前来看第二种方案较优。

参考资料

1. 丁奇 《MySQL 45讲》

（全文完）