MySQL优化三：MySQL事务隔离机制、锁机制、MVCC机制及日志机制详解

耗子的博客

已于 2023-01-18 20:17:35 修改

阅读量253

点赞数

分类专栏： MySQL专栏文章标签： mysql 数据库 sql Powered by 金山文档

于 2023-01-17 22:45:28 首次发布

本文链接：https://blog.csdn.net/zyh0110/article/details/128713138

版权

MySQL专栏专栏收录该内容

2 篇文章 0 订阅

订阅专栏

前言

我们的数据库一般都会并发执行多个事务，多个事务可能并发地对相同地一批数据进行增删改查操作，这样就会导致我们所说的脏写、脏读、不可重复读、幻读这些问题。
这些问题本质上都是数据库的多事务并发问题，为了解决这些问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制、日志机制一整套机制。
接下来，我们一起走进这些机制，理解数据库内部的执行原理。

一、事务原理

1、什么是事务

事务就是一组操作要么全部成功，要么全部失败，目的就是为了保证数据的最终一致性。

2、ACID属性

原子性(Atomicity)

简介

当前事务的操作要么同时成功，要么同时失败。

转账操作：A-1000和B+1000，这两件事要么一起成功，要么一起失败。

实现

通过undo-log来实现。日志机制后续会讲解！！

这里主要分析失败的情况，举例下单操作：
下单分为两步：添加订单(insert操作)+减库存(update操作):
当操作数据库执行insert命令时，mysql会生成这条sql相关的undo日志(delete 主键为xxx的记录)。
当操作数据库执行update命令时，mysql也会生成这条sql相关的undo日志(主键为xxx的记录，某列的值由xxx修改为xxx)。
这两条日志会通过指针按生成顺序(倒序)连接起来。
如果事务过程中业务代码出错那就需要事务回滚，以保证事务的原子性:
insert操作完成后业务代码出错需要回滚，就根据主键找到对应的undo日志，删除掉新增记录完成回滚。
update操作完成后业务代码出错需要回滚，就根据主键找到对应的undo日志(两个操作有两条undo日志)，先将修改后的值更新为修改前的值，再删除掉新增记录完成回滚。
以上undo日志保证事务原子性的底层原理远远不是如此简单，此处可以先简单理解，后续还会详细深入了解undo日志机制，以及相关的MVCC机制！！！

一致性(Consistency)

简介

使用事务的最终目的。

实现

由其它3个特性+业务代码逻辑正确来实现。

隔离性(Isolation)

简介

在事务并发执行时，它们内部的操作不能相互干扰。多事务并发问题主要是这个特性导致的。

实现

MySQL的读写锁+MVCC机制实现。

多事务并发问题

脏读

简介

事务A读取到了事务B已经修改但尚未提交的数据。

实例

按照图示顺序执行的话，事务A读取到了事务B修改后但未提交的数据950，可能会用这个值进行后续操作。此时如果事务B回滚的话，那么事务A的相关操作均会出问题，因为950已经成为了脏数据，数据库此时的真实值是450。

不可重复读

简介

事务A内部相同的查询语句在不同时刻查询结果不一致。

实例

按照图示顺序执行的话，事务A同一条sql前后两次查询出来的balance数值不同。

幻读

简介

事务A读取到了事务B提交的新增数据

实例

按照图示顺序执行的话，虽然③没有读取到事务B提交的新增数据，但是④却执行成功了，因此事务A还是有幻读问题出现的。

持久性(Durability)

简介

一旦提交了事务，它对数据库的改变应该是永久性的。

实现

通过redo-log实现。

mysql引入了redo-log，buffer pool的内存写完了，然后会写一份redo-log，这份redo-log记载着这次在某个页上做了什么修改。
即使mysql在中途挂了，我们可以根据redo-log来对数据进行恢复。
redo-log是顺序(追加)写入的，写入速度很快，并且记录的是物理修改(xxx页做了xxx修改)，文件的体积很小，恢复速度也很快。

3、事务隔离级别

脏读、不可重复读和幻读，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

脏写

简介

多事务并发执行时，对同一行数据进行修改，可能发生更新丢失问题。即：最后的更新覆盖了由其他事务所做的更新。

实例

按照图示顺序执行的话，事务A接下来将①中查询出的balance保存起来，再通过java代码将balance做更新操作。事务提交之后，这样的话事务A做的修改就覆盖了事务B所做的修改，事务B产生了更新丢失的问题。

解决方案(上图示例)

悲观锁

事务A在事务B提交之后要进行更新操作的话，弃用java代码方式，直接使用sql更新(加写锁)。例如：update account set balance = balance + 500 where id = 1;这样的话balance更新时用的实际上就是数据库里面的真实值。

乐观锁(读未提交+读已提交隔离级别下使用)

account表新增version字段，用以记录行记录的更新次数。

事务A开启后，java代码开始死循环，循环体中先执行①操作，①查询出来的那条记录version = 1说明这条记录insert过后没有发生过修改。

此时事务B开启，执行②操作并提交后，id = 1的记录version = 1+1 = 2。

接着事务A在循环体中①之后，使用java代码计算result = ①查询出来的balance + 500，得到结果result。

然后事务A在循环体最后执行update account set balance = result where id = 1 and version = ①查出来的version值;直到操作成功退出循环体即可。

事务并发问题与事务隔离级别关系

隔离级别	脏读	不可重复读	幻读	脏写
读未提交	❌	❌	❌	❌
读已提交	✔️	❌	❌	❌
可重复读	✔️	✔️	❌	❌
可串行化	✔️	✔️	✔️	✔️

可串行化是任何操作加排它锁(互斥)使得事务串行执行解决事务并发问题。
数据库的事务隔离级别越严格，并发副作用越小，但付出的代价也就越大。因为事务隔离本质上就是使事务在一定程度上"串行化"进行，这显然与"并发"是矛盾的。
查看当前数据库的事务隔离级别：show variables like 'tx_isolation';
设置事务隔离级别：set tx_isolation = 'REPEATABLE-READ';
MySQL默认的事务隔离级别是可重复读
select操作是快照读(历史版本)，insert、update、delete操作都是当前读(当前版本)
读已提交是语句级快照，可重复读是事务级快照。
可重复读隔离级别下，事务开启后的第一条select语句执行后，mysql内部会生成整个数据库的快照，后续所有的查询都从这个快照中获取，因此后续不管其他事务做了哪些修改，查询操作的结果仍是取自快照的值。
但是如果中途修改了某些记录(增删改)，那这些记录更新后的值也会被更新到快照中，因此后续查询这些记录时，查询到的是快照中修改后的记录。
而在读已提交隔离级别下事务中的每个select语句执行时都会更新事务开启时mysql内部生成的快照，因此读已提交是语句级快照，而可重复读是事务级快照。(暂时可先这么理解！！其实底层就是MVCC机制的实现)

4、MVCC多版本并发控制机制

MySQL在可重复读隔离级别下如何保证事务较高的隔离性？同样的sql查询语句在一个事务里多次执行查询结果相同，就算其他事务对数据有修改也不会影响当前事务sql语句的查询结果。
这个隔离性就是靠MVCC机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。

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

undo日志版本链

介绍：undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完成后，MySQL会保留修改之前的数据undo回滚日志，并使用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链。如下图蓝色框所示。

注意：begin/start transcation命令并不是一个事务的起点，在执行到它们之后的第一个修改操作或加排它锁操作的语句，事务才真正启动，才会向MySQL申请真正的事务id。MySQL内部是严格按照事务的启动顺序来分配事务id的。

read-view一致性视图

介绍：在可重复读隔离级别，当事务开启时，执行任何查询sql时会生成在当时数据库最新的一致性视图read-view，该视图在事务结束之前永远都不会发生变化(如果是读已提交隔离级别，在每次执行查询sql时都会重新生成read-view)

组成：这个视图由执行查询时所有未提交的事务id数组(数组里最小的id为min_id)和已创建的最大事务id(max_id)组成。如上图红色框所示，分为三个区域。

MVCC实现机制

事务里任何sql的查询结果都需要从对应undo日志版本链里的最新数据开始，逐条跟read-view做对比，从而得到最终的快照结果。

比对规则

row的trx_id落在绿色区域(trx_id < min_id)，表示这个版本是已提交的事务生成的，这个数据可见。

row的trx_id落在红色区域(trx_id > min_id)，表示这个版本是由将来启动的事务生成的，不可见。

row的trx_id落在黄色区域(min_id ≤ trx_id ≤ max_id)，此时包含两种情况：

row的trx_id在read-view视图数组中，表示这个版本是由还没提交的事务生成的，不可见。

row的trx_id不在read-view视图数组中，表示这个版本是已经提交了的事务生成的，可见。

特殊的，如果row的trx_id就是当前自己的事务，那么不管落在哪个区域，均是可见的(也就是说在自己的事务中修改操作之后的查询，结果是内存中已经修改完成的最新数据，不再从版本链取结果了。其余未修改的记录仍是按版本链对比规则从undo历史版本中取结果)。

5、相关问题解疑

只有查询操作的方法需要使用事务嘛？

只有一条查询语句，不需要使用事务。

多条查询语句

可重复读隔离级别下，某些情况可以加只读事务(readonly)。

可重复读隔离级别下，相同的查询语句前后查询结果与不加事务(读已提交)的情况下是不一样的。如果有相同的查询语句，是需要加事务来符合此隔离性的；如果没有但是是在报表查询情况下，我们应该尽可能查询出来的数据在时间上是一个维度的，这种情况下查出来的数据才是准确的，虽然查询出来的数据可能不是实时的，但是我们多次查询就能解决这种问题，因此我们可能也需要加事务处理。

对数据库性能要求较高的情况下，不是可重复读隔离级别，查询语句也没有相同的，这种情况下可以不加事务。

大(长)事务有什么影响？

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

锁定太多的数据，造成大量的阻塞和超时。

执行时间长，容易造成主从延迟。

回滚所需要的时间长。

undo-log膨胀

容易导致死锁。

大(长)事务怎么去优化呢？(业务允许的话)

将查询等数据准备操作放到事务外。

事务中避免使用远程调用，远程调用要设置超时，防止事务等待太久。

事务中避免一次性处理太多数据，可以拆分成多个事务分次处理。

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

能异步处理的尽量异步处理。

系统对数据库性能要求特别高的情况下，业务代码保证事务一致性，不使用事务。这种情况下开发非常复杂，且容易出bug，因此适用于业务非常简单的情况下。

二、锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除了传统的计算资源(如CPU、PAM、I/0等)的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

1、锁分类

性能上

乐观锁：用版本对比或CAS机制实现。适合读操作较多的场景，如在写操作较多的场景使用乐观锁会导致比对次数过多，影响性能。

悲观锁：读锁和写锁都属于悲观锁。适合写操作较多的场景。

数据操作粒度

表锁：每次操作锁住整张表

手动增加表锁：lock table 表名称 read(write);

查看表上加过的锁：show open tables;

删除表锁：unlock tables;

优点

开销小，加锁快。只需要找到表加锁即可，不需逐行查找记录。

不会出现死锁

缺点

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

使用场景：一般是用在整表数据迁移的场景。

注意

MyISAM在select前，会自动给涉及的表加读锁(不阻塞读，阻塞写)，在insert、update、delete前，会自动给涉及的表加写锁(阻塞读和写)。

页锁(了解即可)

只有BDB存储引擎支持页锁。页锁就是在页的粒度上进行锁定，锁定的资源比行锁要多，因为一个页中可以有多行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

行锁：每次操作锁住一行数据。

优点

锁定粒度最小，发生锁冲突的概率最低，并发度最高。

缺点

开销大，加锁慢。需要逐行查找需要上锁的那条记录。

会出现死锁。

注意

InnoDB在select时(非Serializable隔离级别)，不会自动加锁。但是insert、update、delete前，会自动给涉及的行记录加行写锁。

InnoDB的行锁实际上是针对索引加的锁(叶子节点对应的索引项上)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。(RR级别会升级为表锁，RC级别不会)。

RR级别行锁升级为表锁的原因分析：首先RR级别下，不论是非索引字段还是失效索引字段，操作它们都需要遍历扫描聚簇索引记录。其次此隔离级别下还需要解决不可重复读和幻读问题。基于两者，为了防止扫描过的索引被其他事务修改(会导致不可重复读)或间隙被其他事务插入(会导致幻读)，从而产生数据不一致问题，MySQL把所有扫描过的索引记录和间隙都锁上。

InnoDB相对于MyISAM的最大不同：InnoDB支持事务；InnoDB支持行级锁。

InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当时，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

数据库操作类型

读锁(共享锁，S锁)：针对同一份数据多个读操作可以同时进行而不会相互影响。

写锁(排它锁，X锁)：当前写操作没有完成前，它会阻断其他写锁和读锁。数据修改操作都会加写锁(自动加)，查询也可以通过for update加写锁。

意向锁(I锁)：针对表锁，主要是为了提高加表锁的效率，是mysql数据库自己加的。当有事务给表的数据行加了读锁或写锁，同时也会给表设置一个标识，代表该表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的效率很低。而这个标识就是意向锁。

额外的

间隙锁：在RR隔离级别下才生效，锁的就是两个值之间的空隙。

可以解决RR隔离级别下存在的数据幻读问题。

假设account表中数据如上图所示，那么间隙就有id为(3,10)，(10,20)，(20,正无穷)这三个区间。只要在间隙锁范围内锁了一条不存在的记录，那就会锁住整个间隙范围。注意：不锁边界记录。

如果事务A执行：select * from account where id = 25 for update; 则其他事务就没法在(20,正无穷)这个间隙范围内插入任何数据，这样就解决了幻读问题。特殊地，范围查询也会将查询范围内的所有间隙全都锁住，这样会形成多个间隙锁。

临键锁：行锁和间隙锁的组合。(间隙锁的范围边界加了行锁)

2、锁等待分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况。

show status like 'innodb_row_lock%';
-- 结果如下：
InnoDB_row_lock_current_waits -- 当前正在等待锁的数量
🔥InnoDB_row_lock_time        -- 从系统启动到现在锁定的总时间长度
🔥InnoDB_row_lock_time_avg    -- 每次等待所花的平均时间
InnoDB_row_lock_time_max      -- 从系统启动到现在等待最长的一次所花时间
🔥InnoDB_row_lock_waits       -- 系统启动后到现在总共等待锁(包括曾经等待)的数量

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁，8.0之后需要换成这张表data_locks
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看锁等待，8.0之后需要换成这张表data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id;

-- 查看锁等待详细信息(一般是DBA使用的)
show engine innodb status;

死锁

简介：指两个或多个事务已经锁定了一些数据资源，然后又都要尝试加锁已被其他事物锁定的资源，从而出现循环锁等待的现象。

实例

set tx_isolation = 'repeatable-read';
-- 事务A执行
select * from account where id = 1 for update;
-- 事务B执行
select * from account where id = 2 for update;
-- 事务A执行
select * from account where id = 2 for update;
-- 事务B执行
select * from account where id = 1 for update;

-- 事务A事务B之间形成死锁。