MySQL原理简介—8.MySQL并发事务处理

大纲

1.简单总结增删改SQL语句的实现原理

2.多个事务同时执行的场景遇到的问题

3.多个事务并发更新或查询时可能出现的问题

4.SQL标准中对事务的4个隔离级别

5.MySQL是如何支持4种事务隔离级别的

6.Spring事务注解了如何设置隔离级别

7.uodo log多版本链介绍

8.基于undo log多版本链实现ReadView机制

9.RC隔离级别如何基于ReadView机制实现

10.RR隔离级别如何基于ReadView机制实现

11.多事务并发运行的隔离机制总结

12.多事务更新同一行数据如何加锁避免脏写

13.共享锁和独占锁是什么

14.什么操作会导致表级别加锁

15.表锁和行锁相互之间的关系以及互斥规则

16.数据库出现不确定的性能抖动的原因

17.如何优化数据库不确定性的性能抖动

1.简单总结增删改SQL语句的实现原理

其中会涉及MySQL的Buffer Pool机制、redo log机制和undo log机制。MySQL执行增删改时，首先从磁盘加载数据页到Buffer Pool的缓存页。然后更新缓存页，同时会记录undo log回滚日志和redo log重做日志。这两种日志分别用于事务提交后宕机恢复数据的场景及事务回滚的场景。

2.多个事务同时执行的场景遇到的问题

(1)一个事务会包含多条SQL语句

(2)业务系统多线程并发对MySQL执行事务

(3)并发执行多个MySQL事务可能遇到的问题

(1)一个事务会包含多条SQL语句

通常会在业务系统里开启事务来执行增删改操作。

@Transactional
public void doService() {
    //增加一条数据
    addUser();
    //修改一条数据
    updateUser();
    //删除一条数据
    deleteUser();
}

一般来说，业务系统会执行一个一个的事务。每个事务里可能是一条或者多条增删改的SQL语句。而事务的概念，就是一个事务里的SQL语句要不一起成功提交，要不只要一条SQL失败则事务就需回滚，撤销所有SQL所做的修改。

(2)业务系统多线程并发对MySQL执行事务

此外，业务系统不会是单线程系统，它一般会有很多线程。于是一个业务系统可能基于多线程并发对MySQL数据库执行多个事务。

那么每个事务里面的多个SQL语句都是如何执行的呢？其实和普通增删改SQL语句的执行原理一样，都是先从磁盘加载数据页到Buffer Pool的缓存页里，然后更新Buffer Pool里的缓存页，同时记录redo log和undo log。如下图所示：

(3)并发执行多个MySQL事务可能遇到的问题

问题一：

多个事务并发执行时，可能会同时对缓存页里的同一行数据进行更新。这里并发更新同一行数据的冲突如何处理，是否通过加锁进行处理。

问题二：

多个事务并发执行时，可能有的事务在进行更新，有的事务在进行查询。这里并发更新和查询同一行数据的冲突应该如何处理。

要解决这些问题，就涉及同时写和同时读写的并发冲突处理机制。其中就包括了MySQL事务的隔离级别、MVCC多版本隔离、锁机制等。

3.多个事务并发更新或查询时可能出现的问题

(1)脏写

(2)脏读

(3)不可重复读

(4)幻读

(1)脏写

脏写指的是，事务A和事务B同时在更新一条数据，事务A先把它更新为A值，事务B接着把它更新为B值：

可见，事务B是后更新那一行数据的，所以此时那一行数据的值是B值。事务A更新后会记录一条undo log，假设事务A更新前这行数据是NULL。那么事务A的undo log大概是：更新前这行数据的值为NULL，主键为xx。

事务B更新数据的值为B之后，事务A突然需要使用它的undo log来回滚。于是事务A只要一回滚就会直接把那一行数据的值更新回之前的NULL值：

这时对于事务B来说，值明明已经更新了，却被事务A回滚，这就是脏写。所谓的脏写就是明明写了一个数据值，结果过了一会儿这个数据值没了。脏写的定义就是事务B修改还没提交的事务A修改过的数据。因为事务A随时会回滚，所以会导致事务B修改的值也没了。

(2)脏读

假设事务A更新了一行数据的值由原来的NULL值变为A值，此时事务B去查询了一下这行数据的值，看到的就是A值。

于是业务系统的事务B拿到刚查出来的A值去进行一些业务处理，但是事务A突然回滚了事务，导致刚才更新的A值回滚成为NULL值。然后事务B此时再次查询那一行数据的值，看到的只能是NULL值。

脏读的定义就是事务B查询还没提交的事务A修改过的数据。因为事务A随时会回滚，可能导致事务B再次查询就读不到之前的数据。也就是导致事务B在前后时间点查询同一行数据时出现脏读。

(3)不可重复读(不可重复读到同一个值)

假设有一个事务A会多次对某一条数据进行重复查询，另外有两个事务B和C也会对该条数据进行更新。且事务A在事务B或事务C提交后才能读取到修改的数据，即不会脏读。但即使如此也会出现另外一个问题—不可重复读。

比如缓存页里有一条数据原来的值是A值。此时事务A开启后，第一次查询这条数据，查询到的是A值。接着事务B更新了这一条数据的值为B值，同时马上提交事务B。然后事务A此时还有逻辑要处理没提交，需要第二次去查询这一条数据。结果事务A第二次查询这一条数据时查到的是事务B修改过的B值。因为事务B已经提交了，所以这时事务A可以读到事务B更新后的值。紧接着事务C再次更新这一条数据为C值，并且也马上提交事务C。然后事务A此时还有逻辑要处理没提交，需要第三次去查询这一条数据。结果事务A第三次查询这一条数据时查到的值为事务C修改过的C值。

一.这种场景会有什么问题

要说没问题也可以是没问题的，毕竟事务B和事务C都提交后，事务A多次查询都查到它们修改后的值。

但是要说有问题也可以是有问题的，因为事务A第一次查询到的是A值，那么在事务A执行的期间，如果需要多次查询同一行数据，希望得到的都是同样的一个值，也就是希望这一条数据的A值在它事务里面是可以重复读取的，而在这种场景下，明显A值是不可重复读的。

二.是否可以重复读取决于想要的数据是什么样

事务A在执行期间：如果希望每次查询都能查到其他已提交事务修改过的值，是不可重复读。如果希望每次查询都能查到同样的值，不管其他事务提交，是可重复读。

所以如果期望的是可重复读，但是数据库表现的是不可重复读。让事务执行期间多次查到的值不同，都是其他已提交事务修改过的值。那么就可以认为数据库有问题，这个问题就是"不可重复读"问题。

三.总结

所谓不可重复读，就是事务A多次查询一条数据，每次读到的值不一样。这个过程中可能别的事务会修改这条数据，且修改后这些事务也提交了。在避免脏读的前提下，也导致事务A每次查询到的值都不一样。

所谓脏写，就是事务A和B没提交的情况下，都修改同一条数据。结果其中事务A回滚了，把另外一个事务B修改的值也给撤销了。所以脏写就是事务A和事务B在没提交的情况下修改同一个值。

所谓脏读，就是事务A修改了一条数据的值，结果还没提交。另外一个事务B就读到了事务A修改的值，然后事务A却又回滚了。那么事务B再次读该数据时就读不到刚才读到那个要修改的值了。所以脏读就是事务B读到了事务A修改某条数据后还没提交的值。

(4)幻读

事务A先发送一条SQL语句要查询一批数据出来。比如"select * from table where id > 10"，然后一开始查询出10条数据。接着别的事务B往表里插入了几条数据，而且事务B还提交了。然后事务A再次查询，按照相同的SQL语句去查，结果查询出12条数据。于是在事务A中就出现了一模一样的SQL语句，第一次查询是10条数据，第二次查询是12条数据，这就是幻读。

所谓幻读，指的是一个事务用一样的SQL多次查询一批数据，结果每次查询都会发现查到了一些之前没看到过的数据。

(5)总结

脏写、脏读、不可重复读、幻读，都是因为多线程并发执行事务。每个线程都开启一个事务，每个事务都会执行增删改查操作。然后数据库在并发执行多个事务时，多个事务可能会并发对缓存页里的同一批数据进行增删改查操作。于是并发增删改查同样数据，就会导致脏写、脏读、不可重复读、幻读。脏写、脏读、不可重复读、幻读的本质就是数据库的多事务并发问题。MySQL为了解决该问题使用了事务隔离、MVCC多版本隔离、锁等机制。

4.SQL标准中对事务的4个隔离级别

SQL标准的事务隔离级别，并不是MySQL的事务隔离级别。MySQL在具体实现事务隔离级别时会有点差别。SQL标准规定了4种事务隔离级别。规定多个事务并发运行时互相是如何隔离的，从而避免事务并发问题。

这4中级别包括：Read Uncommitted(读未提交)、Read Committed(读已提交)、Repeatable Read(可重复读)、Serializable(串行化)。不同的隔离级别可以避免不同的事务并发问题。

(1)Read Uncommitted隔离级别—读未提交

不允许发生脏写，可以去读一些事务里未提交的数据。这种隔离级别下，两个事务不能改未提交。但是可能发生脏读、不可重复读、幻读。一般来说，不会把事务隔离级别设置为读未提交。

(2)Read Committed隔离级别—读已提交

不会发生脏写和脏读，只能读事务中已提交的数据。这种隔离级别下，是无法读取事务在没提交情况下修改的值。但是可能发生不可重复读、幻读，该隔离级别的简称是RC。把事务隔离级别设置成RC指的就是设置读已提交级别。

(3)Repeatable Read隔离级别—可重复读

对同一行数据，在事务中随时可重复读出同样的值。这种隔离级别下，不会发生脏写、脏读和不可重复读，但可能发生幻读。该隔离级别简称RR，把事务隔离级别设置成RR指的是设置可重复读。RR隔离级别，只保证对同一行数据的多次查询不会被读到不一样的值。

(4)Serializable隔离级别—串行化执行

不允许多个事务并发执行。这种隔离级别下，多个事务只能串行起来执行。所以不会出现脏写、脏读、不可重复读、幻读的问题。一般来说，也不会把事务隔离级别设置为串行化级别。

5.MySQL是如何支持4种事务隔离级别的

SQL标准下的4种事务隔离级别，平时用的比较多的是RC和RR两种级别。在MySQL中也支持这4中事务隔离级别。MySQL默认的事务隔离级别是RR级别，且MySQL的RR级别可避免幻读。SQL标准里的RR级别是会发生幻读的，但MySQL的RR级别避免了幻读。所以MySQL事务默认不会发生脏写、脏读、不可重复读和幻读的问题。

MySQL事务的执行都是并行的，各个事务互相不影响。事务A执行中出现事务B，事务A不会读到事务B未提交的修改值。即使事务B提交了修改值事务A也不会读到。即使事务B提交了插入的一行值事务A也依然不会读到。

MySQL为了实现这种事务之间互不影响的效果，使用的是MVCC机制—多版本并发控制隔离机制。依托MVCC机制，MySQL就可以让RR级别避免不可重复读和幻读的问题。MySQL的默认事务隔离级别是RR，一般不需修改。

6.Spring事务注解如何设置隔离级别

假设在开发业务系统时用Spring的@Transactional注解来做事务处理，如果要设置事务处理成RC级别，那么可通过isolation参数进行设置。具体的设置方式如下：

@Transactional(isolation=Isolation.DEFAULT) ===> @Transactional(isolation=Isolation.READ_COMMITTED)

当设置为Isolation.DEFAULT时，就是MySQL默认事务隔离级别RR级别。不过需要注意的是，其实默认的RR隔离机制就挺好的，没必要去修改。除非事务执行期间多次查询时，业务必须要查到已提交事务修改过的值。那么此时可设为Isolation.READ_COMMITTED级别。

7.uodo log多版本链介绍

MySQL默认的RR隔离级别，不会出现脏写、脏读、不可重复读、幻读。每个事务执行时，跟别的事务是没有关系的。不管别的事务怎么更新和插入，查到的值都是不变、都是一致的。而这就是由经典的MVCC多版本并发控制机制实现的。

MySQL每条数据都有两个隐藏字段：一个是trx_id，一个是roll_pointer。trx_id就是最近一次更新这条数据的事务id。roll_pointer就是指向更新这个事务之前生成的undo log。

假设有一个事务A(id=50)插入了一条数据，则此时这条数据的隐藏字段及指向的undo log如下图示。插入的这条数据值是A，因为事务A的id是50，故这条数据的trx_id=50。因为之前没有这条数据，所以roll_pointer没有指向任何执行的undo log。

接着假设有一个事务B修改这条数据，把值改成了值B，事务B的id是58。那么此时更新之前会生成一个undo log记录之前的值，然后会让roll_pointer指向这个实际的undo log回滚日志。事务B修改完成后，此时表里这行的数据值是B。trx_id是事务B的id=58，roll_pointer指向了undo log。指向的undo log记录的是更新之前的数据值。

接着继续假设事务C又来修改一下这个值为值C，它的事务id是69。此时会把数据行里的trx_id改成69，然后生成一条undo log。该undo log记录之前事务B修改的那个值，如下图示：

综上可知，多个事务串行执行时：每个事务的修改，都会更新隐藏字段trx_id和roll_pointer。同时多个数据快照对应的undo log，会通过roll_pointer指针串联起来。从而形成一个重要的undo log版本链。

8.基于undo log多版本链实现ReadView机制

(1)ReadView的关键内容

(2)基于undo log多版本链实现ReadView机制

(1)ReadView的关键内容

MySQL执行一个事务时会生成一个ReadView，里面关键的内容有4个：

一.m_ids，表示此时有哪些事务在执行但还没提交的，即活跃的事务；

二.min_trx_id，表示m_ids里最小的值；

三.max_trx_id，表示MySQL下一个要生成的事务ID，最大事务ID；

四.creator_trx_id，表示当前这个事务自己的id；

(2)基于undo log多版本链实现ReadView机制

一.时间点一

假设原来数据库有一行数据，一开始插入这行数据的事务id是32；

接着有两个事务并发执行，一是事务A(id=50)，二是事务B(id=59)。事务A是去读取这一行的数据值的，事务B是去更新这一行的数据值的。

二.时间点二

现在事务A创建一个ReadView，这个ReadView里的m_ids就包含了事务A和事务B的两个id：50和59。然后min_trx_id是50，max_trx_id是60，creator_trx_id是50(事务A的id)。这时事务A第一次查询这行数据会进行一个判断：看当前这行数据的trx_id是否小于ReadView中的min_trx_id。

此时发现trx_id=32，小于ReadView里的min_trx_id=50。这说明事务A开启事务前，修改这行数据的事务已提交，可查这行数据。

三.时间点三

接着事务B开启，把这行数据值修改为B，然后这行数据的trx_id设置为事务B的id=59。同时roll_pointer执行了修改之前生成的undo log，之后事务B进行了提交。

这个时候事务A再次查询时，会发现数据行里的trx_id=59。大于ReadView里的min_trx_id=50，小于ReadView里的max_trx_id=60。这说明更新这条数据的事务，很可能是和事务A自己差不多同时开启的。于是会看一下这个trx_id=59，是否在ReadView的m_ids列表里。

最后发现在ReadView的m_ids列表里，有50和59这两个事务id。这就证明了事务B是在事务A开启事务时并发执行提交的，所以这行修改后的数据对事务A来说是不能查的：

既然trx_id=59的这一行数据不能查询，那么可以从这条数据的roll_pointer顺着undo log日志链往下找。于是就会找到最近的一条undo log，该undo log的trx_id=32。

此时发现trx_id=32，是小于ReadView里的min_trx_id=50的。说明这个undo log版本必然是在事务A开启之前就执行且提交的。

这就是undo log多版本链条的作用。它可以保存一个快照链条，让事务可以读到之前的快照值。

四.时间点四

接着假设事务A更新了这行数据的值为A，然后这行数据的trx_id修改为50，同时保存之前事务B修改的值的快照。如下图示：

此后事务A来查询这行数据的值，发现trx_id=50。和自己ReadView里的creator_trx_id=50是一样的，这说明这行数据是自己修改的，因此可以读取它的值A。

五.时间点五

接着在事务A执行的过程中，突然开启一个事务C，这个事务的id=78。然后事务C更新了这行数据的值为C，并且进行了提交。

这时事务A再去查询这行数据，会发现当前数据的trx_id=78，大于自己ReadView中的max_trx_id=60，这说明事务C是事务A开启后提交更新数据的。此时事务C更新的数据对于事务A而言是不能读取的，于是事务A顺着undo log多版本链条继续往下找，自然就会找到事务A之前修改过的那个版本trx_id=50进行读取。

(3)总结

通过undo log多版本链条，加上事务开启时创建的一个ReadView。当有查询时，事务就能根据ReadVIew机制判断应读取哪个版本的数据，这个ReadVIew机制可以确保一个事务只能读到：它自己开启前其他事务进行更新并已经提交的值，以及它自己更新的值。

假如事务开启前，有其他的事务已经正在运行，那么当事务开启后，其他事务更新了值并已提交，这时该事务读取不到那些事务更新的值。

假如事务开启后，才有其他事务开启并更新了值以及进行了提交，那么这时该事务也是读取不到那些事务更新的值。

通过这个ReadView机制就可以实现多个事务并发执行时的数据隔离。

9.RC隔离级别如何基于ReadView机制实现

(1)RC隔离级别与ReadView机制

(2)基于ReadView机制实现RC隔离级别

(1)RC隔离级别与ReadView机制

一.RC隔离级别就是Read Committed读已提交的隔离级别

指的是一个事务在运行期间，只要别的事务修改数据并且提交了。那么这个事务就可以读取到别的事务修改的数据，所以RC隔离是会发生不可重复读、幻读的问题。

二.ReadView机制是基于undo log版本链条实现的一套读视图机制

指的是事务开启时生成一个ReadVIew：如果值是事务本身更新的，是可以读取到的；如果值是在事务生成ReadView之前提交的事务修改的，也可以读取；如果值是生成ReadView后再开启事务修改并提交的，则是读取不到的。

三.基于ReadView机制来实现RC隔离级别的核心

设置RC隔离级别的一个事务，每次发起查询都重新生成一个ReadView。

(2)基于ReadView机制实现RC隔离级别

一.时间点一

假设数据库里有一行数据，插入时事务id=50，现在活跃着两个事务：事务A和事务B，其id分别是60和70，活跃是指它们都还没提交。

二.时间点二

现在事务B发起一次更新操作，更新了这一行数据的值为B。这行数据的trx_id变为事务B的id=70，同时生成一条undo log。然后这行数据的roll_pointer会指向这新生成的undo log。

这时事务A要发起一次查询操作，就会生成一个ReadView。该ReadView的min_trx_id=60，max_trx_id=71，creator_trx_id=60。

事务A发起查询，发现这一行数据当前的trx_id=70，而trx_id=70属于ReadView的活跃事务id范围之间。这说明事务A在生成其ReadView之前就已存在id=70的活跃事务B。然后这个id=70的事务B修改了这条数据的值，但此时事务B还没提交。因为在事务A的ReadView的m_ids活跃事务列表里，有[60,70]两个id。所以根据ReadView机制，此时事务A无法查到事务B修改的值B。

接着事务A就顺着undo log版本链条往下查找。于是会找到一个原始值，发现它的trx_id=50，小于min_trx_id=60。说明这个原始值在事务A生成ReadView之前，就由某事务插入并提交，因此事务A是可以查询到这个原始值的。

三.时间点三

接着假设事务B此时提交了，事务一旦提交就不会活跃于数据库了。按照RC定义，事务B一旦提交，事务A再次查询就能读到事务B修改的值。为了让事务A能够读到提交的事务B修改过的值，可以让事务A发起查询时，再次生成一个ReadView。比如事务B提交后事务A再次发起查询，数据库内活跃的事务只有事务A。因此min_trx_id=60，max_trx_id=71，m_ids=[60]。

此时事务A再次基于这个新ReadView去查询，会发现数据的trx_id=70。由于70在min_trx_id=60和max_trx_id=71范围，但不在m_ids=[60]内。所以说明事务B在事务A生成这个新ReadView之前就已经提交了，因此事务A这次查询可以查到事务B修改过的这个值。

(3)总结

实现RC隔离级别的关键点在于事务每次查询时都生成新的ReadView。如果一个事务在这次查询之前，有其他事务修改了数据而且还提交了。那么其生成ReadView的m_ids列表，当然就不包含这个已提交的事务。既然不包含已提交的事务，那么就可以读取到已提交事务修改过的值，以上就是基于ReadView实现的RC隔离级别的原理。

基于undo log多版本链条 + ReadView机制实现的RC + RR隔离级别，就是数据库MVCC多版本并发控制机制。

10.RR隔离级别如何基于ReadView机制实现

(1)MySQL的RR隔离级别避免不可重复读+幻读

(2)基于ReadView机制实现避免不可重复读

(3)基于ReadView机制实现避免幻读

(1)MySQL的RR隔离级别避免不可重复读+幻读

MySQL的RR级别下，一个事务读一条数据，无论读多少次都是一个值。其他事务修改数据后哪怕提交了，该事务也无法看到其他事务修改的值。同时如果其他事务插入了一些新的数据，该事务也是读取不到。这样就分别避免了出现不可重复读的问题，以及避免了出现幻读的问题。

(2)基于ReadView机制实现避免不可重复读

一.时间点一

假设有一条数据是事务id=50插入的，此时有事务A和事务B同时运行，事务A的id=60，事务B的id=70。这时事务A发起一个查询，第一次查询会生成一个ReadView。此时ReadView如下图示：

这时事务A基于这个ReadView去查这条数据，会发现这条数据的trx_id=50，小于ReadView里的min_trx_id。说明在事务A发起查询之前，早就有其他事务插入这条数据并且提交了。所以事务A此时是可以查到这条数据的原始值的。

二.时间点二

接着事务B更新这条数据的值为B和trx_id=70，同时生成一个undo log。而且事务B也进行提交了，所以此时事务B已结束。

在RR隔离级别下，这时事务A的ReadView中的m_ids还是60和70。因为RR隔离级别下，ReadView一旦生成了就不会改变了。于是事务A继续去查询这条数据的值，会发现数据的trx_id=70。虽然70在ReadView的min_trx_id和max_trx_id的范围，但也在m_ids中。这就说明事务A开启时，id为70的这个事务B还在运行的。然后由id为70的事务B更新了数据，所以此时事务A不能读取更新的值。

接着事务A顺着这条数据的roll_pointer指针找到下面一条数据，发现该数据的trx_id=50，小于事务A的ReadView的min_trx_id。说明事务A开启之前，已提交该id为50的事务，所以可读取其值。

三.基于ReadView机制实现避免不可重复读的总结

事务A多次读同一个数据，每次读到的都是一样的值。除非是它自己修改了值，否则读到的都是一样的值。不管别的事务如何修改数据，事务A的ReadView始终是不变的。所以事务A基于这个ReadView，可以读取到的值始终是一样的。

(3)基于ReadView机制实现避免幻读

一.时间点一

假设事务A继续用"select * from x where id > 10"来查询，此时查到的只有一条数据，而且读到的是这条数据的原始值版本。

二.时间点二

现在有一个事务C插入了一条数据，然后提交了。接着事务A再次查询，会发现符合条件的有2条数据。一条是原始值那个数据，一条是事务C插入的那条数据。事务C插入的数据的trx_id=80，大于事务A的ReadView的max_trx_id。说明事务C是事务A开启后才启动的，所以这条数据不能查询。

因此事务A本次查询，还是只能查到原始值这条数据。由此可见事务A不会发生幻读，因为它根据条件范围查询的时候。每次读到的数据都是一样的，不会读到其他事务新增的数据。

11.多事务并发运行的隔离机制总结

(1)多个事务并发运行读写同一数据时的问题

(2)事务隔离级别：RU、RC、RR和串行化

(3)MySQL的MVCC机制

(1)多个事务并发运行读写同一数据时的问题

一.脏写

事务A和B更新同一数据，

事务A回滚时把事务B的更新也回滚了；

二.脏读

事务A先读事务B没提交时修改的数据，

然后事务B回滚后再读就读不到；

三.不可重复读

事务A多次读同一数据，

其他事务修改数据并提交，于是读到不同的值；

四.幻读

每次范围查询查到的数据不同，

其他事务插入新值，就会读到更多数据；

(2)事务隔离级别：RU、RC、RR和串行化

一.RU隔离级别

可以读到其他事务未提交的修改数据，

只能避免脏写；

二.RC隔离级别

可以读到其他事务已提交的修改数据，

可以避免脏写和脏读；

三.RR隔离级别

不会读到其他事务已提交的修改数据，

可以避免脏写、脏读和不可重复读；

四.串行隔离级别

指的是让事务都串行执行，

可以避免所有问题；

(3)MySQL的MVCC机制

MySQL中多事务并发运行的隔离原理，就是MVCC机制——multi version concurrent control，专门控制多个事务并发运行时，互相之间会如何影响。

基于undo log多版本链条 + ReadView机制，可实现MySQL的MVCC。MySQL默认的RR隔离级别，就是基于这套机制来实现的。除了避免脏写、脏读、不可重复读，还能避免幻读问题。因此一般来说，MySQL使用默认的RR隔离级别即可。

12.多事务更新同一行数据如何加锁避免脏写

依靠锁机制让多个事务更新一行数据时串行化，避免同时更新一行数据。

一.时间点一

在MySQL里，假设有一行数据暂时没有被任何事务处理。此时有一个事务A要来更新这行数据，首先会看这行数据是否被加上锁。该事务发现这一行数据并没有加锁，于是就会创建一个锁。这个锁包含了这个事务的trx_id=50和等待状态=false。该事务A创建完一个锁之后，会将锁和这行数据关联在一起。

由于更新一行数据要把对应数据页从磁盘文件读取到缓存页才能更新，所以此时这一行数据和关联的锁数据结构，都是在内存里的。

二.时间点二

因为事务A给这行数据加了锁，此时就不能被其他事务访问了。这时有另外一个事务B过来了，这个事务B也想更新这一行数据。此时事务B会先检查一下，当前这行数据是否被加锁了。

结果事务B发现这行数据已被加锁，于是会生成一个锁数据结构进行排队。这个锁的数据结构会有事务B的trx_id=60和等待状态=true。

三.时间点三

接着事务A这时更新完数据，就会把自己的锁给释放掉。锁释放掉后MySQL就会找其他对这行数据加锁的事务，于是找到事务B。事务B就会把锁里的等待状态修改为false，然后唤醒事务B继续执行。此时事务B就获取到锁了：

13.共享锁和独占锁是什么

(1)独占锁让多事务对同一行数据写写操作互斥

(2)MVCC机制避免对同一行数据读写操作加锁

(3)共享锁让多事务对同一行数据读写操作互斥

(4)查询时加独占锁

(1)独占锁让多事务对同一行数据写写操作互斥

多个事务同时更新一行数据时，每个事务都会加锁，然后都会排队等待。必须等事务执行完毕提交了并释放了锁，才能唤醒其他事务继续执行。这个过程中，多个事务更新时所加的锁，就是独占锁——X锁。

(2)MVCC机制避免对同一行数据读写操作加锁

此外当有事务更新数据时，其他的事务是可以读取该数据的。但读取该数据的事务是不用加锁的，只有更新该数据的事务才需要加锁。默认情况会开启MVCC机制，让事务更新数据时其他事务能读取该数据。所以一行数据的读和写两个操作，默认不会加锁互斥。

MySQL通过MVCC机制来实现避免频繁加锁互斥。一个事务读取数据时，完全可以根据该事务创建的ReadView内容，去undo log版本链找一个能读取的版本，不用考虑其他事务的并发修改。

(3)共享锁让多事务对同一行数据读写操作互斥

如果希望事务在执行查询操作的时候也加锁，那么MySQL也可以支持。MySQL支持一种共享锁——S锁，这个共享锁的语法如下：

mysql> select * from table lock in share mode;

只需要在一个查询语句后面加上lock in share mode，就表示查询时需要对一行数据加共享锁。

需要注意的是，共享锁和独占锁是互斥的。也就是说，如果有事务正在更新一行数据，已经加了独占锁，就不能对查询这行数据的事务也加共享锁。同样道理，如果有事务对一行数据先加了共享锁，其他事务也不能来更新加独占锁。

需要注意的是，共享锁和共享锁是不会互斥的。如果一个事务给一行数据加了共享锁，其他事务也可以对该行数据继续加共享锁。

(4)查询时加独占锁

MySQL的查询操作还可以加互斥锁，语法如下是：

mysql> select * from table for update;

这样查询时就会加上独占锁，直到事务提交后，其他事务才能更新数据。

(5)总结

更新数据时必然加独占锁，独占锁和独占锁是互斥的，此时其他事务不能更新。

进行查询时默认是不加锁的，会通过MVCC机制读快照版本，但查询可以手动加共享锁和独占锁。

共享锁和独占锁是互斥的，但共享锁和共享锁不互斥。查询时手动加共享锁：select * from table lock in share mode；查询时手动加独占锁：select * from table for update；

一般开发业务系统时，其实很少会在查询时主动加共享锁。通常基于Redis和Zookeeper的分布式锁来控制业务系统的锁逻辑。

14.DDL操作是否会导致表级别加锁

(1)行锁中的独占锁和共享锁总结

(2)DDL操作通过元数据锁实现类似表锁的效果

(1)行锁中的独占锁和共享锁总结

在多个事务并发更新数据时，都是在行级别加独占锁的，这就是行锁。独占锁都是互斥的，所以不可能发生脏写问题。一个事务提交了才会释放自己的独占锁，并唤醒下一个事务执行。

如果有事务去读取别的事务正在更新的数据，有两种可能：第一种可能是基于MVCC机制进行事务隔离，读取快照版本，比较常见。第二种可能是查询的同时基于特殊语法去加独占锁或者共享锁。

如果查询时加独占锁，那么会和其他更新数据的事务加的独占锁互斥。

如果查询时加共享锁，那么不会和其他查询加的共享锁互斥。但和其他更新数据的事务加的独占锁互斥，也和其他查询数据的事务加的独占锁互斥。

一般而言，不建议在数据库粒度去通过行锁实现复杂的业务锁机制。而应该通过Redis、Zookeeper使用分布式锁来实现复杂业务的锁机制。

默认情况下，多个事务并发运行更新一条数据，是加独占锁的。而其他事务读取数据则基于MVCC机制进行快照版本读，实现事务隔离。

(2)DDL操作通过元数据锁实现类似表锁的效果

在数据库里，不仅可以通过查询中的特殊语法加行锁。比如lock in share mode、for update等，还可以通过一些方法在表级别上加锁。

如下说法有一定道理：执行增删改时默认加行锁，执行DDL语句时默认在表级别加锁。因为执行DDL时会阻塞所有增删改操作，而执行增删改操作时会阻塞DDL操作。

但实际上这是通过MySQL的通用元数据锁来实现的，也就是Metadata Locks，但这不是表锁的概念。因为表锁是InnoDB存储引擎的概念，InnoDB提供了自己的表级锁。InnoDB的表级锁和DDL语句里的元数据锁不是一个概念，只不过DDL语句和增删改操作，确实是互斥的。

15.表锁和行锁相互之间的关系以及互斥规则

(1)表锁分为表级共享锁、表级独占锁

(2)表级的意向锁分为意向独占锁、意向共享锁

(1)表锁分为表级共享锁、表级独占锁

MySQL的表锁，其实是相当鸡肋，几乎很少会用到。表锁分为两种，一种是表锁，另一种是表级的意向锁。

可以使用如下语法来加：

LOCK TABLES xxx READ：加表级共享锁；

LOCK TABLES xxx WRITE：加表级独占锁；

一般来说，几乎不会用这两个语法去加表锁。

(2)表级的意向锁分为意向独占锁、意向共享锁

在下面这两种情况下会加表级意向锁：

事务执行增删改操作，除了在行级加独占锁之外，还会在表级加一个意向独占锁；

事务执行查询操作，会在表级加一个意向共享锁；

平时数据库操作中，比较常见的两种表锁：就是更新和查询操作时自动加的意向独占锁和意向共享锁，但这两种意向锁之间是不会互斥的。

但更新数据时自动加的表级意向独占锁，会和"LOCK TABLES xxx WRITE"手动加的表级独占锁互斥，也和"LOCK TABLES xxx READ"手动加的表级共享锁互斥。

查询数据时自动加的表级意向共享锁，只会和"LOCK TABLES xxx WRITE"手动加的表级独占锁互斥。

一般来说，根本就不会手动加表级锁。读写操作时自动加的表级意向锁相互之间是不互斥的。

对同一行数据的更新操作加的行级独占锁，和读操作是不互斥的。因为读操作不加锁，默认是通过MVCC机制来读取快照版本的。

16.数据库出现不确定的性能抖动的原因

(1)数据库出现周期性抖动的问题

(2)数据库执行更新语句的流程分析

(3)性能抖动的可能情况一

(4)性能抖动可能的情况二

(5)线上数据库性能抖动原因总结

(1)数据库出现周期性抖动的问题

线上数据库时不时莫名其妙的来一次性能抖动，而且造成性能抖动的不是数据库锂电池充放电的问题。

(2)数据库执行更新语句的流程分析

数据库执行更新语句时，都是先从磁盘上加载数据页到内存的缓存页里。然后会更新缓存页，同时写对应的redo log日志到Redo Log Buffer中。

既然更新了Buffer Pool里的缓存页，缓存页就会变成脏页。因为此时缓存页里的数据和磁盘文件里的数据页的数据不一样。对于脏页，需要有一个合适的时机把数据刷入到磁盘里，数据库会维护一个LRU链表和一个flush链表来实现。

如果加载磁盘文件的数据页到Buffer Pool时发现并没有空闲的缓存页，此时就必须把部分脏页刷入到磁盘文件里。于是MySQL会根据LRU链表寻找最近最少被访问的缓存页刷入磁盘，当然MySQL在不那么繁忙时也会从flush链表将一部分脏页刷入磁盘。

(3)性能抖动的可能情况一

要执行的一个查询语句需要查询大量数据页并加载到缓存页里，此时就可能导致内存里大量的脏页需要淘汰出去然后刷入磁盘，这样才能腾出足够的内存空间来执行这条查询语句。

在这种情况下，可能就会出现数据库在执行某个查询语句时性能抖动。平时只有几十毫秒的查询，这次需要几秒。就是因为要等待大量脏页刷入到磁盘，才能加载查询出的大量数据页，然后SQL语句才能执行，所以才会导致耗时突增。

(4)性能抖动可能的情况二

执行大量更新写满所有redo日志文件，且还不能覆盖第一个日志文件。因为往redo日志文件写入redo log太快了，都追上checkpoint检查点了。此时只能让脏页刷盘，让一些redo log失效来腾出redo日志文件的空间。

一.redo log刷盘的时机

Redo Log Buffer里的数据超过容量的一半 + 提交事务。这两种情况都会强制将Redo Log Buffer里的Redo Log Block刷入磁盘上的redo日志文件中。

二.脏页刷盘的时机

所有redo日志文件都被写满时，会触发一次脏页的刷盘。磁盘上会有多个redo日志文件，这些redo日志文件会循环不停地写入。如果所有redo日志文件都写满了，此时会回到第一个redo日志文件写入。

三.所有redo日志文件写满时要判断第一个redo日志文件能否被覆盖

如果第一个redo日志文件里靠前的一些redo日志，所对应Buffer Pool缓存页的数据，还没有被刷新到磁盘文件的数据页中。一旦把这个redo日志文件里的redo日志进行覆盖，此时数据库却崩溃了，那么被覆盖的redo日志和它对应的被更新过的缓存页数据就彻底丢失了。

所以当所有redo日志文件写满，需要从第一个redo日志文件开始写时，就会判断第一个日志文件里靠前的redo log对应的缓存页是否已刷盘。如果是，则要把要被覆盖的redo log对应的缓存页马上刷入磁盘。

四.写满redo log的所有日志文件时发现不能覆盖第一个redo日志文件

此时就需要把第一个redo日志文件里靠前的一些redo log，所对应Buffer Pool中没被刷入磁盘的缓存页(脏页)，都刷入到磁盘。从而导致数据库无法处理任何更新请求，因为更新请求需要写redo log。而此时还在等待脏页被刷新到磁盘，才能有可以覆盖的redo日志文件。之后才能执行更新语句，才能把新的redo log写入第一个redo日志文件。

五.如果某时刻MySQL在执行大量的更新语句

那么可能会发现数据库的很多更新语句突然短时间内性能抖动了，可能很多几毫秒就执行完的更新语句，这时却要等1s才能执行完毕。

其中的原因大概率就是，所有redo日志文件写满了。必须要等第一个redo日志文件里部分redo log对应的脏页都刷入磁盘，才能继续执行更新语句，让其redo日志能覆盖到第一个redo日志文件中，从而导致此时执行的更新语句性能很差。

(5)线上数据库性能抖动原因总结

导致数据库的更新语句突然出现性能抖动，很可能是以下两种情况。

情况一：要执行的一个查询语句需要查询大量数据页并加载到缓存页里，由于没有足够的空闲缓存页，需要等大量脏页刷盘才能继续加载数据页。

情况二：执行大量更新语句导致所有redo日志文件写满且还不能覆盖第一个文件，要等第一个文件里部分redo log对应的脏页刷盘才能继续执行更新语句。

17.如何优化数据库不确定性的性能抖动

(1)查询和更新时出现性能抖动的可能原因

(2)如何优化参数减少脏页刷盘带来的性能抖动

(3)如何减少脏页刷盘的时间

(1)查询和更新时出现性能抖动的可能原因

上面分析了有时在数据库执行查询或者更新语句时，可能SQL语句性能会出现不正常的莫名奇妙的抖动，可能平时只需要几十毫秒执行完成的却居然需要几秒钟才能完成。这种莫名奇妙的性能抖动，在分析过底层原理后，根本原因就两个。

原因一：执行查询时Buffer Pool的缓存页满了

当执行一个需要查询很多数据的SQL时，需要把很多缓存页刷入磁盘。由于脏页刷磁盘太慢了，于是就会导致查询语句执行得很慢。因为要等很多缓存页都刷盘，才能把查询需要的数据页加载到缓存页中。

原因二：执行更新时磁盘上的所有redo日志满了

此时需要回到第一个redo log日志文件尝试进行覆盖写，这又涉及第一个redo log日志文件里很多redo log对应的缓存页还没刷盘。所以此时就必须把那些缓存页刷入到磁盘，才能执行后续的更新语句，于是就会导致执行的更新语句很慢了。

(2)如何优化参数减少脏页刷盘带来的性能抖动

优化一：尽量减少脏页刷盘的频率

给数据库采用大内存机器，给Buffer Pool分配更大的内存空间。那么也只能让缓存页被填满的速度低一些，降低出现这种情况的频率。

优化二：尽量提升脏页刷盘的速度

假设现在要执行一个SQL查询语句，此时要等待刷入一批缓存页到磁盘，接着才能加载数据到缓存页。

如果把那批缓存页刷入磁盘需要1s，然后查询语句执行的时间是200ms，此时这条SQL执行完毕的总时间就需要1.2s。如果把那批缓存页刷入到磁盘的时间优化到100ms，然后再加上200ms，这条SQL执行完毕的总时间只要300ms，性能提升了。

所以关键点在于，尽可能将缓存页刷入到磁盘的时间开销减到最小。

(3)如何减少脏页刷盘的时间

一.采用SSD固态硬盘而不要使用机械硬盘

因为SSD固态硬盘最强大的地方，就是它的随机IO性能非常高。而把缓存页刷入到磁盘，就是典型的随机IO，需要在磁盘上找到各个缓存页所在的随机位置，把数据写入到磁盘里去。所以如果采用SSD固态硬盘，那么缓存页刷盘的性能就会提高不少。

二.设置以最大随机IO速率刷盘

除了SSD外，还得设置一个关键的参数，就是innodb_io_capacity。这个参数告诉数据库采用多大的IO速率把缓存页刷入到磁盘。

如果SSD能承载每秒600次随机IO，但innodb_io_capacity只设置300。也就是把缓存页刷入到磁盘时，每秒最多执行300次随机IO。那么这样就根本无法把SSD固态硬盘的随机IO性能发挥出来。

所以通常建议对机器的SSD固态硬盘承载的最大随机IO速率进行测试。可以使用fio工具来测试，测出磁盘最大的随机IO速率。测出SSD固态硬盘的最大随机IO速率后，就设置给innodb_io_capacity。这样就可以尽可能让数据库用最大的速率去把缓存页刷入到磁盘。

三.设置禁止刷入邻近的缓存页

还有一个关键参数，就是innodb_flush_neighbors。这个参数可以控制缓存页刷盘时，临近的其他缓存页是否也刷入到磁盘。如果该参数设置为1，那么就会导致每次刷入磁盘的缓存页太多了。

所以如果使用了SSD固态硬盘，并没必要让数据库同时刷邻近的缓存页。可将该参数设置为0，禁止刷邻近缓存页，减少每次刷新缓存页的数量。

(4)总结

针对MySQl性能随机抖动的问题：最核心的就是把innodb_io_capacity设置为SSD固态硬盘的IOPS。同时设置innodb_flush_neighbors为0，禁止让数据库刷邻近的缓存页。从而让数据库能尽快将缓存页刷进磁盘，及减少每次要刷缓存页的数量，最终将缓存页刷入磁盘的性能提到最高。