MySQL的事务、锁和MVCC机制

更新丢失或脏写：当两个或多个事务选择同一行数据修改，可能发生更新丢失问题，即后面的更新覆盖了前面所做的更新（事务A的更新覆盖事务B的更新，但业务上需要A的更新发生在前）；
脏读：事务A读取到了事务B已经修改但未提交的数据；
不可重复读：事务A内部的相同查询语句在不同时刻读出的结果不一致；
幻读：事务A读取到了事务B提交的新增数据。

1.4 锁与MVCC机制

读锁（共享锁、S锁）：读锁是共享的，多个事务可以同时读取同一个资源，但不允许其他事务修改。select加读锁。
写锁（排他所、X锁）：写锁是排他的，会阻塞其他的写锁和读锁，update、delete、insert都会加写锁。

MVCC（Multi-Version Concurrency Control）多版本并发控制：可以做到读写不阻塞，且避免了类似脏读这样的问题，主要通过undo日志链来实现。

1.5 快照读和当前读

select操作的是快照读（历史版本）

insert、update、delete是当前读（当前版本），加的是行锁

read commit（读已提交）：语句级快照

repeatable read（可重复读）：事务级快照

1.6 事务的持久性

Mysql引入了redo log，数据库执行操作的时候，先操作Buffer Pool内存，然后会写一份redo log，这份redo log记载着这次在某个页上做了什么操作。即使Mysql在中途挂了，我们仍然可以根据redo log来对数据进行恢复。

undo log：回滚日志是Innodb引擎特有的日志，记录操作执行前的数据状态，负责回滚。

Buffer Pool：数据库的增删改查都是直接操作buffer pool（缓存池），而不是直接操作磁盘，buffer pool一般设置为机器内存的60%左右。

redo log是顺序写的，写入速度很快。并且它记录的是物理修改（xxx页做了xxx操作），文件的体积很小，恢复速度也很快。

补充：Kafka、Rokect MQ都是用磁盘顺序写。（重点）

1.7 一条SQL语句的执行过程

SQL语句：update t set name = 'zhuge666' where id = 1;

Client客户端执行该SQL语句，首先经过MySQL的Server层，依次经过连接器（管理连接与权限检验）、分析器（语法词法分析）、优化器（执行计划生成索引选择），最终进入执行引擎，来执行这条SQL语句。

执行过程：

1.加载缓存数据：加载id为1的记录所在的整页数据到Buffer Pool中

2.写入更新数据的旧值到undo log中，便于回滚。

如果事务提交失败要回滚数据，可以用undo日志中的数据恢复buffer pool里的缓存数据。

3.更新内存数据。（即更新Buffer Pool中的数据）

4.写redo log Buffer，向Redo Log Buffer中写redo log日志。

5.将Redo Log Buffer中的redo log日志顺序写入磁盘，准备提交事务（阶段1）。

6.准备提交事务，binlog日志写入磁盘，binlog主要用来恢复数据库磁盘里的数据。

7.写入commit标记到redo日志文件里，提交事务完成。该标记为了保证事务提交后redo与binlog数据一致。（阶段2）

8.在磁盘空闲时，将缓存池中修改后的数据随机写入磁盘，以page为单位写入，这步做完，磁盘的文件才完成修改。

关于redo log：

1.如果事务提交成功，buffer pool里的数据还没来得及写入磁盘，此时系统宕机了，可以用redo log中的数据恢复磁盘ibd文件里的数据；

2.redo日志文件顺序写：redo log是一个或几个预先分配好磁盘空间的文件，写入永远都是在文件末尾追加；

3.WAL机制：（日志先行，数据异步）先写redo log到磁盘（顺序写），后刷新数据表文件到磁盘（随机写）的机制（Write Ahead Logging）。主要是因为随机写需要的时间较长，顺序写追加即可，随机写可能需要先找合适的位置，然后再惊醒写入。所以，MySQL采用WAL机制，降低数据的写入延迟，优化client体验。

1.8 大事务的影响

1.并发情况下，数据库连接池容易被撑爆；

2.大事务可能会锁定太多的数据，可能会造成大量的阻塞和锁超时；

3.执行时间长，容易造成主从延迟；

4.回滚所需要的时间长；

5.undo log膨胀；

6.容易导致死锁。

1.9 事务优化的原则

1.将查询等数据准备操作放到事务外，具体也要看隔离级别，RC隔离级别就可以放到外面；

2.事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久；

3.事务中避免一次性处理太多数据，可以拆分成多个事务分次处理；（业务允许的情况下）

4.更新等涉及加锁的操作尽可能放在事务靠后的位置；

5.能异步处理的尽量异步处理；

6.应用端（业务代码）来保证数据的一致性，减少事务的使用

2.锁

锁是计算机中协调多个进程或线程并发访问某一资源的机制。

2.1 锁分类

从性能上分为乐观锁和悲观锁，乐观锁适合读操作较多的场景，悲观锁适合写操作较多的场景

（通俗来讲，乐观锁就是，客户一开始使用时，客户认为资源没有加锁，直接使用资源，如果需要修改资源，这时候再比较这个资源是否有锁（或状态是否被修改）。属于先拿后比较的策略，具体实现比如CAS机制）

（悲观锁在一开始就去比较资源是否有锁，若有锁，则等待阻塞）

从对数据操作的粒度分，分为表锁、页锁、行锁
从对数据库操作的类型分，分为读锁和写锁（都属于悲观锁），还有意向锁

2.2 读锁、写锁、意向锁

1.读锁（S锁）：针对同一份数据，多个读操作可以同时进行而不会相互影响

2.写锁（X锁）：当前写操作没有完成前，它会则断其他写锁和读锁，数据修改操作都会加写锁（当然，查询select也可以通过for update加写锁，从而阻塞其他的读写）

3.意向锁（Itention Lock）：又称I锁，主要是为了提高加表锁的效率。如果当前有事务给数据行加了共享锁或者排他锁，mysql会给表设置一个标识，代表该表已经有行锁了，其他事务如果想要对表加表锁，就不必逐行判断有没有行锁与其冲突。这个锁就是意向锁。

意向锁分为：意向共享锁（IS锁）和意向排他锁（IX锁）

2.3 表锁

每次操作锁住整张表。一般用在整表数据迁移的场景。

优点：开销小，加锁快；不会出现死锁；

缺点：锁的粒度大，发生锁冲突的概率最高，并发度低。

2.4 页锁

只有BDB存储引擎支持页锁，页锁就是在页的粒度上进行锁定，锁定的资源比行锁要多。

页锁的开销介于表锁和行锁之间，会出现死锁。

2.5 行锁（锁升级）

直接锁定被操作的数据行。用于保护单个记录，可以是共享锁或排他锁。

缺点：开销大，加锁慢，加锁的时候，需要先找到这行，然后才能加锁。

注意，InnoDB的行锁是针对索引加的锁（在索引对应的索引项上做标记）。如果索引不存在，会从行锁升级为表锁（RR级别会升级为表锁，RC级别不会升级为表锁）

补充，RR级别行锁升级为表锁的原因：因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚簇索引记录时，为了防止扫描过的索引被其他事务修改（不可重复读问题）或间隙被其它事务插入记录（幻读问题），从而导致数据不一致。所以，Mysql的解决方案就是把所有扫描过的索引记录和间隙都锁上。注意：并不是直接将整张表都加表锁，因为不一定能加上表锁，可能会有其它事务锁住了表里的其他行记录。

2.6 间隙锁（Gap Lock）

间隙锁，锁的就是两个值之间的空隙，间隙锁在可重复读隔离级别（RR）下才会生效。通过加间隙锁，可以解决幻读问题。注意：间隙锁不锁边界记录。

2.7 临建锁（Next-key Locks）

临建锁是行锁与间隙锁的组合，可以锁住边界。示例如下：

select * from a where id > 3 And id <= 10;

2.8 InnoDB与MyISAM加锁的时机

MyISAM在执行查询语句SELECT前，会自动给涉及到的所有表加读锁，在执行update、insert、delete操作会自动给涉及到的表加写锁。

InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

注意，读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

2.9 死锁问题

大多数情况下，MySQL可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁，这种情况我们可以通过日志分析找到对应事务的线程id，然后通过kill杀掉。

2.10 锁优化实践

尽可能让所有数据检索都通过索引来完成，避免无索引的行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
尽可能用低的事务隔离级别

3.MVCC机制（详细介绍版本链对比规则）

MySQL在RC（读已提交）和RR（可重复读）隔离级别下都实现了MVCC机制。

在MySQL中，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性的，避免频繁加锁互斥。在串行化隔离级别，为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。

3.1 undo log日志版本链

undo log日志版本链是指表中一行数据被多个事务依次修改后，Mysql会依次保留修改前的数据到undo回滚日志中，并且用两个隐藏字段trx_id(事务id)和roll_pointer（回滚指针）把这些undo日志串联起来形成一个历史记录版本链。

3.2 read view

在RR级别下，当事务开启后，执行最前面一条SQL查询时会生成当前事务的一致性视图read-view，该视图在事务结束之前永远都不会改变；如果是RC级别，在每次执行select时都会重新生成read-view。

read view由执行查询时所有未提交事务id数组（数组中最小的id记为min_id）和 已创建的最大事务（记为max_id）组成。

事务里的任何SQL查询需要从对应版本链里的最新数据开始逐条跟read-view对比，从而得到查询结果。

3.3 版本链对比规则

版本链比对规则：

1. 如果 row（从undo log的头部行开始）的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的（undo log中的这一行数据是可见的）；

2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）；

3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况

a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，该行不可见(若 row 的 trx_id 就是当前自己的事务是可见的)；

b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，该行可见。

例如：（按照下面的版本链比对规则）分析#select 1的第一次查询结果为500

上图中的未提交与已提交事务是关键区间

1.当前readview是[100,200] 300，[100,200]是未提交的事务id，300是当前最大的事务id

2.从undo log的第一行开始按照版本链规则比较，第一行row的trx_id=80，80落在绿色部分，所以该行数据可见，balance=0可以被查到；

3.undo log的第二行的trx_id=300，300落在未提交与已提交事务（深黄色）区间，按照规则3，300不在视图数组[100,200]内，所以该行数据可见，balance=500可以被查到；

4.undo log的第三行的trx_id=100,100落在未提交与已提交事务（深黄色）区间，按照规则3，100在视图数组[100,200]内，所以该行数据不可见，也就是balance=800不能被查到。故结果为步骤3的500。

说明：undo log在上面。大家可以尝试分析一下其他查询的步骤。

3.4 对于Delete操作

对于删除的情况，可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的 trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。