不就加个字段嘛？怎么线上业务都挂了！

预热

比较喜欢的一段话：不经一番寒彻骨，怎得梅花扑鼻香，学习是枯燥的请大家坚持！ 这篇文章的是向丁奇老师学习的。不懂的自己搜一下哈！ 阅读这篇文章大概需要35分钟！

大家好前面我们大概了解了MySQL索引的选择，innodb数据结构的选择，索引，覆盖索引，查询优化的细节。今天我们介绍一下数据库全局锁，表锁，行锁，两阶段锁协议以及案例！

相信大家绝对听过这么一个事故。某互联网大厂程序员新增了一个字段导致线上业务瘫痪。没听过也没有关系，我们接下来一起慢慢分析。

线上业务的瘫痪主要因为死锁导致的资源耗尽，导致崩盘。那么我们就开始讲述一下为什么会死锁。MySQL的锁主要分为全局锁，表锁，行级锁。下面我们详细介绍一下。

开始

全局锁

定义

全局锁：顾名思义这个是整个MySQL实例的锁。这是MySQL自己提供的一种加锁方法。命令是

Flush tables with read lock (FTWRL)

当你需要把整个数据库都加锁的时候，或者使数据库处于只读状态的话，可以通过这种方式。通过这种方式之后，所有的更新修改操作，定义操作都将阻塞。

tip：更新修改操作：新增，修改，删除。定义操作：新建表，修改表结构。

应用场景

全局锁是全库逻辑备份的核心，也就是把所有数据输出到文件上。以前这种做法在执行的时候会把整个实例数据库上锁，变成只读模式。听上去就很危险。万一在这段期间有人写库怎么办。没错，没办法写只能等。

如果分主库，从库的话。如果在主库上备份，那么整个业务都将停顿。如果在从库备份的话，由于数据库主从复制的关系那么备份期间从库不能执行主库同步的binlog日志，会导致主从延迟，甚至数据不一致。

看起来全局加锁进行备份看起来不太友好。我们再来反问一下为什么备份要加锁呢？我估计很多人应该猜到了，为了保证数据在某一时刻的视图是一致的。下面我们举个例子来说明一下这个问题。

案例

就举我个人开发的电商系统来说吧。我备份的是8月27日之前的所有数据，如果不进行加锁的话这个时候有个用户正在下单，那么涉及的是订单表的父子表，商品子表。库存表。还有用户的子表操作。订单表要新增一条记录，订单子表要新增这个订单的详细商品信息。商品子表的购买量。库存表的消减库存。还有用户子表的积分情况。如果这个时候执行完了 订单表，库存表。这个时候备份到了这个位置，就会存在这种很不符合常规的情况。订单已下，商品已处理，用户已付款，但是用户的积分没有加。

等恢复数据的时候用户就会发现自己少了一笔积分。这样的程序是错误的。如果备份表的顺序反过来就会存在用户多了莫名的积分。但是订单表没下。这样就导致了老板多赠送了积分。这样也是不对的。这就是加锁与不加锁最终的效果。

备份工具

简介

mysqldump 是 MySQL 自带的逻辑备份工具。

它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。

当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

结论

疑问来了，MySQL自带了一个备份工具mysqldump，那么我们为什么还需要用全局锁呢？因为这个mysqldump必须支持隔离级别。而myisam不支持事务的引擎，所以有些时候不支持事务的时候，并且要数据一致性的话就必须使用全局锁啦。

只读模式

MySQL是有一个只读模式的，通过set global readonly=true可以达到整个库都处于只读模式。但是不建议使用这种方式进行备份。

先介绍一下只读模式的机制吧。在正常的情况下，有些业务逻辑上会借助readonly值用来做其他逻辑处理。比如判断一个库是主库还是从库。因此修改readonly有可能会引发不必要的麻烦。

在出现异常情况下，客户端发生异常，则数据库会一直保持只读状态。这样会导致整个库长时间不可写的状态，风险极高。

全局锁的话如果客户端发生异常，那么MySQL就会自动释放这个全局锁，整个库回到正常的状态。

tip：主机只负责增删改，从库只做查询。

表级锁

定义

业务的更新不只是增删改数据（DML数据操纵语言)，还有可能是加字段等修改表结构的操作（DDL数据定义语言）。不论是哪种方法，一个库被全局锁上以后，你要对里面任何一个表做加字段操作，都是会被锁住的。即使没有被全局锁住，加字段也不是就能一帆风顺的，因为你还会碰到表级锁。

表级锁中又分为表锁和元数据锁(MDL metadata lock)。

表锁的语法是 lock t_user read/write 上锁，unlock tables 手动解锁。也可以在客户端断开的时候自动释放锁。表锁会限制别的线程的读写操作。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

再还没有出现更细粒度锁的时候，表锁是处理并发的常用方式。而对于innodb来说，表锁不是最好的处理方式。最好的 处理方式是行锁。下面我会一一介绍

元数据锁(MDL)不需要显式使用，在访问一个表的时候会自动加上。MDL的作用就是保存读写的一致性。用白话文来说就是，当一个线程正在遍历列表查询数据的时候，另一个线程插了过来，修改了一个字段。那么查询的结构肯定对不上了呀。所以MDL解决的就是这种问题。

MDL是MySQL5.5的时候引入的，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

1. 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

2. 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行

使用MDL锁时，有一个坑就是。虽然MDL是系统默认加的，但是你不能忽略的一个机制。我们将举例说明如下图

当sessionA发起一个查询请求的时候会先加一个读锁

sessionB发起一个查询请求也会加一个读锁，读锁之前是不互斥的，所以不会互相影响

sessionC发起一个修改请求会加一个写锁，写锁之前需要获得一个读锁，而读锁在sessionA与sessionB都没有释放。这个时候sessionC就堵塞了，这一个用户堵塞还不要紧，最要命的话所有用户都堵塞。

sessionD发起一个查询请求。那么查询请求之前要加一个读锁，读锁跟sessionC的写锁互斥导致读写争持被死锁。

如果某个表查询请求频繁，而客户端又有重连机制的话，那么线程，内存很快就被刷满了。而且在执行事务的时候不是立马加锁，而是语句执行时再加锁，语句结束后并不会立马释放锁，而是要等事务提交之后才会释放锁。

那么问题来了，如何安全的给表加字段呢？

1. 首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

2. 如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？这时候 kill 可能未必管用，因为新的请求马上就来了。
3. 比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

行锁

定义：

介绍完表锁我们来聊一下行锁。行锁是表锁的更小粒度的锁。行锁是在MySQL引擎层由各个引擎实现的。这并不意味着所有的引擎都支持，比如myisam就不支持行锁。同时也就意味着myisam在处理并发需求的时候只能通过表锁来实现相应的需求。这也是MySQL默认引擎选择innodb的原则之一。

行锁是针对数据表中的单个记录的锁。比如事务A更新了ID=10这条数据，这个时候事务B也更新了ID=10这条数据。那么事务B想正常执行，就必须等待事务A提交事务之后才可以。也就可以完美的解决并发情况下的需求。

举一个容易出错的锁：两阶段锁

通过如下图我们继续分析。事务A更新的是ID为1,2这两条数据，而事务B是更新ID=1这条数据。那么两条数据都在修改ID=1的这条数据。这种执行流程就需要有一个先后顺序，必须事务A执行完之后事务B才可以执行。

tip：修改完成之后，不释放锁，只有等事务A提交之后才释放锁。这就是两阶段锁协议

应用场景：

接下我们介绍一下使用手法问题，如何提交系统的并发处理能力？

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。我给你举个例子

这个例子是我设计过的一个项目，电影院交易系统，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：

1. 从顾客 A 账户余额中扣除电影票价；
2. 给影院 B 的账户余额增加这张电影票价；
3. 记录一条交易日志。

也就是说，要完成这个交易，我们需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务中的顺序呢？

试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

如果这个影院做活动，可以低价预售一年内所有的电影票，而且这个活动只做一天。于是在活动时间开始的时候，你的 MySQL 就挂了。你登上服务器一看，CPU 消耗接近 100%，但整个数据库每秒就执行不到 100 个事务。这是什么原因呢？这里，我就要说到死锁和死锁检测了。

死锁，死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。这里我用数据库中的行锁举个例子。

事务A要修改ID=1和ID=2这两条数据，事务B在要修改ID=2和ID=1这两条数据，

事务A在等事务B释放ID=2这条数据，事务B在等待事务A释放ID=1这条数据的锁。双方都在等那么就进入了僵持阶段，导致死锁。

这种问题有两种解决方案

1. 通过设置 innodb_lock_wait_timeout这个参数来决定超时机制，自动释放。
2. 发现死锁后，通过回滚一条事务，使另一条事务能执行下去，也就是放行的意思。通过把innodb_deadlock_detect 参数设置为 on（死锁检测）

我们来一一分析两种方案的可行性。

1. 通过超时机制的话，系统默认的超市机制是50秒，也就意味着50秒后才会进入超时机制。对于线上业务来说，等待50秒这显然是一件不现实的事情。而如果设置过短的话，有可能真正超时的时候，会有很多情况处理异常，会有误伤的情况。
2. 回滚事务目前是最好的解决方案。innodb_deadlock_detect 的默认值本身就是 on。死锁检测是立即生效的。

每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

那如果是我们上面说到的所有事务都要更新同一行的场景呢？

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

解决方案： 我们从设计上入手。你可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。这个方案看上去是无损的，但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成 0 的时候，代码要有特殊处理。

结尾