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

大纲

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

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

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

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

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

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

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

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，禁止让数据库刷邻近的缓存页。从而让数据库能尽快将缓存页刷进磁盘，及减少每次要刷缓存页的数量，最终将缓存页刷入磁盘的性能提到最高。