mysql系列--锁和MVCC

锁

根据加锁的范围，MySQL ⾥⾯的锁⼤致可以分成全局锁、表级锁和⾏锁三类

全局锁

全局锁就是对整个数据库实例加锁。
MySQL 提供了⼀个加全局读锁的⽅法，命令是Flush tables with read lock (FTWRL)。让整个库处于只读状态。
全局锁的典型使⽤场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成⽂本。
以前做法是通过 FTWRL对整库加只读锁备份。
风险：
1.如果在主库备份，在备份期间不能更新，业务停摆
2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟（只读状态备份数据主备之间会有数据不一致问题）

可设置事物隔离级别为可重复读，使用mysql自带mysqldump工具
mysqldump 使⽤参数–single-transaction 的时候，导数据之前就会启动⼀个事务，来确保拿到⼀致性视图
格式：mysqldump –single-transaction -h 主机名 -P端口 -u 用户名 -p密码 --database 数据库名 > 文件名.sql

由于有些引擎如MyISAM不支持可重复读，故只能使用FTWRL备份，通过set global readonly=true设置库为只读状态也是同样效果，但是不建议，原因为：
⼀是，在有些系统中，readonly 的值会被⽤来做其他逻辑，⽐如⽤来判断⼀个库是主库还是备库。因此，修改 global 变量的⽅式影响⾯更⼤，我不建议你使⽤。
⼆是，在异常处理机制上有差异。FTWRL客户端异常自动释放锁，整库设置readonly发生异常锁不会释放，风险高。

表级锁

MySQL ⾥⾯表级别的锁有两种：⼀种是表锁，⼀种是元数据锁（meta data lock，MDL)。
表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以⽤ unlock tables 主动释放锁，也可以在客户端断开的时候⾃动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

举个例⼦, 如果在某个线程 A 中执⾏ lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执⾏ unlock tables 之前，也只能执⾏读 t1、读写 t2 的操作。连写 t1 都不允许，⾃然也不能访问其他表。
在还没有出现更细粒度的锁的时候，表锁是最常⽤的处理并发的⽅式。⽽对于 InnoDB 这种⽀持⾏锁的引擎，⼀般不使⽤ lock tables 命令来控制并发，毕竟锁住整个表的影响⾯还是太⼤。

另⼀类表级的锁是 MDL（metadata lock)。

MDL：不需要显式使用，在访问一个表的时候会被自动加上。
MDL的作用：保证读写的正确性。
在对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
读锁之间不互斥。读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。
MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。
所有对表的增删改查操作都需要先申请 MDL 读锁

如何安全地给表加字段？
1、解决长事务，在 MySQL 的 information_schema 库的 innodb_trx 表中查询有无长事物。若有要考虑先暂停 DDL，或者 kill 掉这个长事务。
2、如果要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
等待或者不等待写法：
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

行锁

在 InnoDB 事务中，⾏锁是在需要的时候才加上的，但并不是不需要了就⽴刻释放，⽽是要等到事务结束时才释放。这个就是两阶段锁协议。
如果你的事务中需要锁多个⾏，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
对于行锁是需要时才加上，事物开始未用到时未加锁，故冲突多的操作放在后面可减少锁等待时间。

死锁

事务 A 在等待事务 B 释放 id=2 的⾏锁，⽽事务 B 在等待事务 A 释放 id=1 的⾏锁。事务 A 和事务 B 在互相等待对⽅的资源释放，就是进⼊了死锁状态。

死锁解决：
1、直接进⼊等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout 来设置。
2、发起死锁检测，发现死锁后，主动回滚死锁链条中的某⼀个事务，让其他事务得以继续执⾏。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采⽤第⼀个策略，50s太长，设置太短会出现误伤正常的锁等待。
故一般采用死锁检测，出现死锁能够快速处理。
所有事务都要更新同⼀⾏的场景呢？每个新来的被堵住的线程，都要判断会不会由于⾃⼰的加⼊导致了死锁，1000个线程同时更新一行会自行100万次检测，消耗cpu过多，解决思路：
1、争取保证不出现死锁，关闭死锁检测；
2、控制并发，排队更新；

begin/start transaction 命令并不是⼀个事务的起点，在执⾏到它们之后的第⼀个操作 InnoDB表的语句，事务才真正启动。如果你想要⻢上启动⼀个事务，可以使⽤ start transaction withconsistent snapshot 这个命令。

MVCC

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。
每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

前面的文章不是说，语句更新会生成 undo log（回滚日志）吗？那么，undo log 在哪呢？实际上，图 2 中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的

如果是这个事务自己更新的数据，它自己还是要认的。在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。这个视图数组把所有的 row trx_id 分成了几种不同的情况。

一致性视图基于当前版本数据回找事物内数据时可见性为黄色及其前的数据

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：
1、版本未提交，不可见；
2、版本已提交，但是是在视图创建后提交的，不可见；
3、版本已提交，而且是在视图创建前提交的，可见。

当前读：
事物A创建后事物B创建，此时都未提交，数据相互不可见，此时k值为1，若同时更改了k值自增，按照之前逻辑更改后都为2，结果不对，更新为先查询后更新，此查询的值为当前读，即实际最新的数据
除了 update 语句外，select 语句如果加锁，也是当前读。
mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

假设k=1，事务 C’更新后并没有马上提交，在它提交前，事务 B 的更新语句先发起了。前面说过了，虽然事务 C’还没提交，但是k已经增加为了2并生成了版本，并且是当前的最新版本。那么，事务 B 的更新语句会怎么处理呢？这时候，我们在上一篇文章中提到的“两阶段锁协议”就要上场了。事务 C’没提交，也就是k=2这个版本上的写锁还没释放。而事务 B 是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务 C’释放这个锁，才能继续它的当前读。

可重复读的核心就是一致性读（consistent read）

Online DDL的过程是这样的：
1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL
4. 升级成MDL写锁
5. 释放MDL锁
1、2、4、5如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个表可以正常读写数据，是因此称为“online ”
其他步骤有冲突依然会阻塞

MDL作用是防止DDL和DML并发的冲突。

死锁检测说明：
1. 一致性读不会加锁，就不需要做死锁检测；
2. 并不是每次死锁检测都都要扫所有事务。比如某个时刻，事务等待状态是这样的：
B在等A，
D在等C，
现在来了一个E，发现E需要等D，那么E就判断跟D、C是否会形成死锁，这个检测不用管B和A

死锁检测发现后，reset_connection只是复位状态，恢复到连接和权限验证之后的状态，没有重连

怎么删除表的前 10000 行？
方式一、直接执行 delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。
方式二（推荐），在一个连接中循环执行 20 次 delete from T limit 500
方式三，（即：在 20 个连接中同时执行 delete from T limit 500），会人为造成锁冲突。

小惊喜

1、积少成多，下载高佣联盟，领取各大平台隐藏优惠券，每次购物省个十块八块不香吗，通过下方二维码注册的用户可添加微信liershuang123（微信号）领取价值千元海量学习视频。

为表诚意奉献部分资料：

软件电子书：链接：https://pan.baidu.com/s/1_cUtPtZZbtYTF7C_jwtxwQ 提取码：8ayn
架构师二期：链接：https://pan.baidu.com/s/1yMhDFVeGpTO8KTuRRL4ZsA 提取码：ui5v
架构师阶段课程：链接：https://pan.baidu.com/s/16xf1qVhoxQJVT_jL73gc3A 提取码：2k6j