mysql的事务隔离深入剖析

杭城我最帅

已于 2023-06-29 19:31:42 修改

阅读量99

点赞数 1

分类专栏： undolog redolog 文章标签： mysql 数据库 java

于 2023-06-24 21:29:58 首次发布

本文链接：https://blog.csdn.net/zxcz123123421/article/details/131362824

版权

undolog 同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

redolog

1 篇文章 0 订阅

订阅专栏

在mysql中，只有innodb引擎支持事务，所以才引出事务隔离级别。

[怄火]为什么要有事务隔离级别？

因为并发场景下数据库的数据会被多个客户端同时操作，可能会出现脏写、脏读、不可重复读、幻读等问题。通过事务隔离级别定义的不同隔离程度，满足不同应用场景下对数据一致性和并发性的要求。

[怄火]什么是脏读、幻读、不可重复读？如何解决？
假定应用程序中有两个事务分别为A事务和B事务。
脏读是指A事务修改了某条记录后，还未提交事务，B事务就读取到了更新后的值。一旦A事务发生回滚，B事务读取到的数据就是脏数据，称为脏读。
幻读是指在一个事务过程中，多次查询，得到的记录数不同，此为幻读。
不可重复读是指在一个事务过程中，多次查询某条记录的数据不一致。
解决方案：加锁、事务隔离、mvcc
加锁：
1.脏读：在修改时加独占锁，直到事务提交才释放。读取时加共享锁，读完释放锁。
2.不可重复读：读数据时加共享锁，写数据时加独占锁。
3.幻读：加范围锁。

[怄火]事务隔离级别有几种？

事务隔离级别有4种：
读未提交ru（read-uncommitted）：
该事务级别解决了更新丢失的问题，即A事务在更新c数据时，B事务只能读取，不允许进行更新c数据的操作，但是A事务对c数据进行修改，还未提交事务，B事务读取该记录会读取到A修改后的值，一旦A事务回滚，B事务读取到的数据就是脏数据。因此存在脏读。

读已提交rc（read-committed）：
该事务级别解决了上一个事务级别的脏读问题，即A事务在更新数据后，只有提交事务后，B事务才会读取到更新后的数据。否则还是原来的数据。但是A事务在b事务两次查询之间对记录做了修改并提交事务，b第二次读到的是a事务修改后的数据。因此存在不可重复读。

可重复读rr（repeatable-Read）：
该事务级别解决了不可重复读的问题，即b事务开启后，不管A事务如何修改数据，B事务还是取开启事务时的值。但是如果A事务新增记录并提交事务，B事务对该记录进行更新后，在查询列表的时候会查询到，因此存在幻读问题。

第一步：开启一个RR事务隔离级别为事务A，查询全表
set tx_isolation='repeatable-Read';
begin;
select * from account;

在这里插入图片描述

第二步：开启事务B，对数据进行更新，并提交事务。
set tx_isolation='repeatable-Read';
begin;
update account set balance=700 where id=1;
select * from account;
commit;

在这里插入图片描述

第三步：事务A，验证查询结果是否一致。
验证结果与步骤（一）查询一致，没有出现不可重复读的问题。

在这里插入图片描述

在A事务内更新id为1的数据，结果是在B事务修改的基础上继续修改。符合预期。

在这里插入图片描述

验证幻读问题

通过B事务添加数据**并提交事务**[必须要提交事务，不然A事务在更新时会阻塞等待]

在这里插入图片描述

以下是A事务列表没查询到B事务新增的记录，但可以更新，再次查询列表时展示。

在这里插入图片描述

串行化（Serializable）：解决上面所有问题，这种隔离级别并发很低，开发中很少使用，性能太差。

[怄火]读未提交ru事务是如何解决更新丢失的问题？

通过共享锁（读锁）【lock in share mode】和独占锁（写锁）【for update】，在A事务更新c数据时，通过加独占锁，防止其他事务同时修改，如此就避免出现数据被覆盖的问题。

[怄火]读已提交rc（read-committed）事务隔离级别，B事务为什么会读到A事务提交的数据？

通过mvcc的可见性算法，rc每次查询，都会生成最近的readview。

[怄火] 可重复读rr（repeatable Read）事务隔离级别解决不可重复读的实现原理是什么？即第一次开启事务，读取某条数据后，不管这条数据被其他事务如何修改，当前事务读取还是原来的值。

通过mvcc的可见性算法，RR在第一次查询时会生成readview，后面查询都按首次生成的readview进行版本链对比。但是如果是本事务修改了记录，则记录（row）的trx_id就是当前自己的事务，那么是可见的。

[怄火]众所周知，mysql的默认事务隔离级别是RR，通过什么语句可以查看mysql的事务隔离级别呢？

SHOW VARIABLES LIKE ‘tx_isolation’;

[怄火]什么是mvcc机制？

mvcc是指多版本并发控制，即开启事务后，会对当前数据进行“快照”（readview），然后只要有更新就通过undo log进行版本记录，形成版本链。

[怄火]一条更新语句，SQL的执行过程是怎样的？

在这里插入图片描述

[怄火]innodb是如何实现事务的?

innodb 通过buffer pool，redo log buffer， redo log，undo log来实现事务，以一个更新语句为例：
1.mysql接收到更新语句，会通过server层的执行器，调用innodb引擎接口。
2.innodb引擎根据更新语句的条件找到数据所在的页，并将该页缓存到buffer pool中。
3.在更新内存数据前，在undolog内写入更新数据的旧值，便于回滚。
4.更新buffer pool缓冲池里的值。
5.再写redo日志，先写入到redo log buffer，后台线程每隔一秒，就把redo log buffer内的redo日志，调用os的write函数写入到page cache内，再通过fsync顺序写入到磁盘，等到binlog日志也写完之后，redo日志提交事务。

[怄火] innodb引擎写完undolog后，还没来得及更新内存数据，mysql就宕机了，undolog会怎样？

在mysql重启的时候，会把undolog中未提交事务的记录进行事务回滚，然后抹除相应的undo log记录，释放存储空间。

[怄火] undolog在mysql中是用来干嘛的？

undolog是innodb引擎特有的日志，是用于保证事务的原子性。
在innodb引擎中，在事务内执行更新语句的顺序是，
1.将磁盘（ibd）读取到记录的整页存储到buffer pool缓存池，
2.innodb引擎把更新数据的旧值写入到undo log，便于事务提交失败，进行回滚。
3.执行器更新buffer pool的内存数据

[怄火]事务还有哪些属性？

ACID
A：原子性：
概念：事务是不可分割的，要么完全成功，要么完全失败。
作用：通过undolog保证事务的原子性，undolog记录数据的旧值，当事务未提交，事务会回落恢复
C：一致性：
概念：事务无论是成功还是失败，都要保持事务内操作的一致性。
作用：是使用事务的目的，AID是手段。
I：隔离性：
概念：多个事务操作一个数据时，将每个事务进行隔离，互相不干扰。
作用：通过mvcc机制，对事务进行隔离
D：持久性：
概念：事务开始就不会终止。结果不受其他外在因素的影响。
作用：通过redo log buffer+redolog来保证持久性，redo log buffer 每隔一秒，就调用os的write函数把redo日志写入到os的page cache，在通过os的fsync函数顺序写入到磁盘。当redo写入commit标识，说明binlog日志写入成功。

[怄火]查询操作方法需要使用事务吗？

如果是单条sql查询，那加不加事务都一样。
如果是多条sql查询，那么为了保证同一时间数据的一致性，需要加RR事务。比如报表业务。

[怄火]bufferpool缓存与redo日志是如何提升事务性能的，从事务底层实现解释Mysql为什么不会丢数据

1.因为如果没有redo日志，bufferpool把数据持久化需要随机写入磁盘文件ibd，而随机写入的效率慢，redo日志是顺序写入磁盘，并提交事务。

2.如果这时候mysql宕机，虽然bufferpool里面没有数据，但是可以通过redo日志更新ibd。

[怄火]如何排查数据库的事务问题

-- 查看当前有多少个正在执行事务的表
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;
-- 查看锁等待详细信息
show engine innodb status;