MySQL事务隔离级别及锁的试验

最新推荐文章于 2023-02-18 08:25:56 发布

慕紫羽

最新推荐文章于 2023-02-18 08:25:56 发布

阅读量487

点赞数 2

分类专栏：归档文章标签：事务隔离级别 mysql 锁

本文链接：https://blog.csdn.net/muziyu2015/article/details/88672503

版权

归档专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一.事务ACID

在这里插入图片描述

二.MySQL四种隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted(读未提交)	是	是	是
Read committed(读已提交)	否	是	是
Repeatable read(可重复读)	否	否	是
Serializable(串行化)	否	否	否

事务的隔离级别基本是为了解决读一致性的问题。
下面我会通过一个经典的银行账户余额变更的例子来依次阐述。

三.环境搭建

1.表创建

创建表account，并插入两条数据。使用navicat打开

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) DEFAULT NULL,
  `account` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

在这里插入图片描述

2.关闭自动提交

SHOW VARIABLES LIKE 'AUTOCOMMIT';
SET AUTOCOMMIT = 'off';

在这里插入图片描述
MYSQL默认是自动提交事务的，其实不关闭也没问题，因为我们会使用SQL语句显式的开启一个事务。

3.打开两个会话

我分别使用navicat和sqlyog来表示两个客户端的请求。
在这里插入图片描述

四.读未提交

1.分别设置Mysql的会话级别隔离级别为读未提交

set session transaction isolation level read uncommitted;

在navicat及sqlyog的会话窗口先执行此sql，修改事务的隔离级别为读未提交。
以下navicat的会话sql当做A，sqlyog的会话当做B。

2.A中开启事务并执行更新操作

BEGIN;
update account set account = account + 200 where id = 1;

注意此时A事务并没有提交！

3.B中开启事务，并执行查询

BEGIN;
SELECT * FROM account;

在这里插入图片描述
我们看到在B事务中，张三的账户余额变为了1200。也就是说我们读到了A事务没有提交的数据，也就是说发生了脏读。假设此时我们将A事务回滚（执行CALLBACK），张三的账户余额会变回1000，那么B读取到的数据1200就是脏数据。

4.总结

读未提交是最低的隔离级别，没有解决任何一个事务并发问题。发生脏读时B事务并不知道A事务是否提交会回滚，所以拿到的数据是很不安全的。

五.读已提交

1.分别设置Mysql的会话级别隔离级别为读已提交

set session transaction isolation level read committed;

在navicat及sqlyog的会话窗口先执行此sql，修改事务的隔离级别为读已提交。

2.A中开启事务并执行更新操作

BEGIN;
update account set account = account + 200 where id = 1;

注意此时A事务并没有提交！

3.B中开启事务，并执行查询

BEGIN;
SELECT * FROM account;

在这里插入图片描述
我们可以看到此时B事务并没有读取到A事务已变化的内容，我们并没有看到张三的account由1000变为1200。这一切似乎已经很完美了，解决了脏读的问题，我们已经读取不到没提交的事务所修改的数据了。

但是请注意：
当A事务提交后，B事务再次查询会看到account变为了1200。也就是说B事务两次查询的结果是不一致的，此时发生了不可重复读现象。不可重复读的意思就是在一个事务中相同的查询条件下却读取到不同的结果。

此时我们需要将mysql的隔离级别提高到可重复读
Repeatable read(可重复读)，同时也是MySQL InnoDB引擎的默认事务隔离级别。

4.总结

虽然读已提交解决了脏读的问题，但是不可重复读的问题却没能解决，此时需要提高隔离级别到Repeatable read(可重复读)

六.可重复读

1.分别设置Mysql的会话级别隔离级别为可重复读

set session transaction isolation level repeatable read;

在navicat及sqlyog的会话窗口先执行此sql，修改事务的隔离级别为可重复读。简称RR隔离级别。

2.A中开启事务并执行更新操作

BEGIN;
update account set account = account + 200 where id = 1;

注意此时A事务并没有提交！

3.B中开启事务，并执行查询

BEGIN;
SELECT * FROM account;

在这里插入图片描述
可以看到此时我们读取的是A事务没变更前的数据，即张三的account还是1000，首先解决了脏读的问题。其次我们对A事务进行提交。

在B事务中（B还未提交）再次查询结果。在这里插入图片描述
发现B事务读取的仍然是1000。说明即便A事务提交了记录，B事务多次读取依旧能够读取到相同的结果，解决了不可重复读的问题。

此时我们提交B事务。再次执行查询
在这里插入图片描述
可以看到在A和B事务都提交后，可以查询出A事务中执行的修改内容，此时张三的account变为了1200。

4.总结

Repeatable read(可重复读)解决了脏读，不可重复读的问题，是MySQL的InnoDB引擎默认的事务隔离级别，可以解决大部分的读一致性问题。
但是Repeatable read(可重复读)还有一个问题没能解决，那就是幻读。
幻读和不可重复读容易混淆，在我理解看来。

不可重复读主要针对update的操作，表现为一个事务多次读取另一个事务中修改后的内容，读取到的结果不一致，大部分是值的变化（举个例子，金额）。
幻读可能在insert，delete中发生比较频繁。比如A事务中进行了insert或者delete操作，导致B事务在原先的范围条件读取时，结果变多了或者变少了。（举个例子，在A事务中添加一条account记录，id为3，username为王五。B事务使用where id > 1查询，原先可能查询只有一条记录，id=2的李四记录。但此时却查询到了两条记录，即id = 3的记录，就像是发生了幻觉一样，也就是幻读了。）

七.串行化

set session transaction isolation level serializable;

串行化是事务隔离级别中最高的一级，可以解决脏读、不可重复读、幻读的问题。但同时也是效率最低、并发度最差的一种，原因是串行化每次读写操作都会锁表，必须等待锁释放下一个事务才能进行相关的操作。所以一般很少用到，这里不再试验，下面将对幻读进行测试。

八.幻读测试1

在Innodb的Repeatable read(可重复读)隔离级别下，幻读的问题其实已经被InnoDB解决了。所以按照下面的测试步骤，是看不到幻读的现象的。

1.在A中执行

set session transaction isolation level repeatable read;

BEGIN;
#插入一条id为3的记录
insert into account value(3,'王五',1000);

2.在B中执行

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
SELECT * FROM account WHERE id > 1;

3.A事务没提交的查询结果

在这里插入图片描述
如图，在A事务没提交的情况下，我们看到只查到一条数据，这也很合理。那么接下来我们提交A事务呢？

4.A事务提交的查询结果

提交A事务

COMMIT;

同时在B事务中再次查询

SELECT * FROM account WHERE id > 1;

在这里插入图片描述
结果仍然一样。

5.B事务提交再次查询

在这里插入图片描述
此时终于看到了A事务中插入的记录。
这么看来，我们所说的幻读现象并没有出现，那么怎么才能测试出来呢？

九.幻读测试2

1.A中执行测试1中相同的内容

set session transaction isolation level repeatable read;

BEGIN;
#插入一条id为3，姓名为王五的记录
insert into account value(3,'王五',1000);

2.B中执行插入操作

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
#插入一条id为3,姓名为赵六的记录
INSERT INTO account VALUE(3,'赵六',1000);

在这里插入图片描述
会发现B事务并不能插入，一直处于操作状态。等一小段时间过后，我们会在B事务的控制台看到报错信息

这说明我们当前的两个事务中存在着锁的冲突，为什么会这样呢？
因为我们操作A事务，执行insert id=3的操作，B事务同样操作insert id=3的操作，他们都是对id = 3这一行进行操作，也就是说A事务执行insert id =3时发生了行锁，将该行锁住，所以B事务对id=3这行进行操作时，是不能成功的，因为A事务并没有释放锁。

3.如果提交A事务

我们会立马看到下面的错误信息
在这里插入图片描述
A事务提交后，id = 3的记录被持久化到数据库中。所以B事务再插入id = 3的就报错了，因为id是主键。

在发生锁冲突时，可以执行show status like ‘innodb_row_lock%’;
查看锁的相关信息

十.补充

1.InnoDB怎么解决幻读？

Innodb实现了MVCC（多版本并发控制），使用next-key锁。

Select操作不会更新版本号，是快照读
Insert/Update/Delete操作会更新版本号，是当前读

下面推荐两篇文章来了解学习。
Innodb中的MVCC
轻松理解MYSQL MVCC 实现机制

2.关于快照读和当前读

MySQL 在InnoDB引擎下有当前读和快照读两种模式。当前读即加锁读，读取记录的最新版本号，会加锁保证其他并发事务不能修改当前记录，直至释放锁。插入/更新/删除操作默认使用当前读，显示的为select语句加lock in share mode或for update的查询也采用当前读模式。 快照读：不加锁，读取记录的快照版本，而非最新版本，使用MVCC机制，最大的好处是读取不需要加锁，读写不冲突，用于读操作多于写操作的应用，因此在不显示加[lock in share mode]/[for update]的select语句，即普通的一条select语句默认都是使用快照读MVCC实现模式。
------引自CSDN用户Aubade2017对博客的评论

很显然，快照读和当前读是两种读取机制。快照读使用了MVCC，读取不用加锁。
主要实现是通过在事务操作的当前行的记录上加上 DATA_TRX_ID 作为当前事务版本号(可以理解为创建时间)，DATA_ROLL_PTR (可以理解为删除时间)作为回滚指针指向undo log的row_id。（指向当前记录项的rollback segment的undo log记录，找之前版本的数据就是通过这个指针）

3.update的事务过程

begin->用排他锁锁定该行->记录redo log->记录undo log->修改当前行的值，记录事务编号，回滚指针指向undo log中的修改前的行

注意：

insert的事务过程和update基本一致，只是insert时undo log是不能指向原始记录的，因为原始记录就不存在。所以insert操作的回滚，需要丢弃undo log。

4.select的原则及疑问

Innodb检查每行数据，确保他们符合两个标准：
1、InnoDB只查找版本早于当前事务版本的数据行(也就是数据行的版本必须小于等于事务的版本)，这确保当前事务读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行
2、行的删除操作的版本一定是未定义的或者大于当前事务的版本号，确定了当前事务开始之前，行没有被删除
符合了以上两点则返回查询结果。

但我之前一直有个疑问。比如A事务先开启，执行一个insert操作，假如此时的事务版本号是1，那么insert的新记录的DATA_TRX_ID就是1。此时开启B事务（B事务假如编号为2），在B事务中查询，为什么没查询到小于当前事务版本号（2）的记录呢？

A事物插入数据是要加入排它锁的，B通过一致性非锁定读的时候不会读到这个锁定的数据，会去读这个数据的快照，即通过undo log来读取，然而**这条数据是新插入的所以undo log不存在所以读不到**，这就不会导致脏读。至于幻读innoDB是通过next key 锁来避免的，一致性非锁定读同样读取的是undo log当中的快照
-----引自CSDN用户wulei93对博客的评论

上面的这段评论解决了我的疑问。

5.mysql的间隙锁

推荐下面这篇文章
mysql锁　innodb下的记录锁，间隙锁，next-key锁

6.结语

感谢阅读，有理解有误的地方希望积极指正，谢谢。在后续深入学习中有心得会再分享

慕紫羽

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL事务隔离级别及锁的试验

一.事务ACID二.MySQL四种隔离级别隔离级别脏读不可重复读幻读Read uncommitted(读未提交)是是是Read committed(读已提交)否是是Repeatable read(可重复读)否否是Serializable(串行化)否否否事务的隔离级别基本是为了解决读一致性的问题。下面我会通过一个经典的银...
复制链接

扫一扫