【计算机网络安全】湖北大学-mysql事务隔离性实验

参考数据库实验：并发控制实验（MySQL）-CSDN博客，大佬写的很好

实验环境

事务的隔离级别

1. 读未提交

2. 读已提交

3. 可重复读

4. 序列化

三种要解决的并发问题

1. 脏读（Dirty Read）

2. 不可重复读（Non-Repeatable Read）

3. 幻读（Phantom Read）

实验

插入实验数据

实验步骤：

取消MySQL的自动提交功能

读未提交：

1. 脏读

2. 不可重复读

3. 幻读

读已提交(Read Committed)

1.脏读

2. 不可重复读

3. 幻读

可重复读(Repeated Read)

1.脏读

2.不可重复读

3.幻读

串行化(Serializable)

1. 脏读，不可重复读，串行化

总结

实验环境

实验需要mysql环境，如果我们本机有mysql客户端，或者安装过phpstudy都可以直接用，Kali似乎也有。

本机启动phpstudy，然后打开mysql命令行

输入密码进入

如果进不去，输入mysql -uroot -proot,其中root填你的用户名密码，phpstudy中可以重置

事务的隔离级别

名字	隔离级别	脏读	不可重复读	幻读
读未提交	read uncommitted	是	是	是
读已提交	read committed	否	是	是
可重复读	repeatable read	否	否	否
串行化	serializable	否	否	否

1. 读未提交

最低的隔离级别，允许一个事务读取另一个事务未提交的数据。

2. 读已提交

事务只能读取到已提交的事务所做的更改。

3. 可重复读

在同一事务内多次读取相同的数据会得到相同的结果。

4. 序列化

最严格的隔离级别，确保事务串行执行，完全避免了所有并发问题。

三种要解决的并发问题

1. 脏读（Dirty Read）

定义：
脏读是指一个事务能够读取到另一个事务未提交的数据。这种情况可能导致读取的数据不一致，因为未提交的事务可能会被回滚。

示例：

事务 A 更新了某条记录但尚未提交。
事务 B 读取了事务 A 更新后的数据。
如果事务 A 随后回滚，事务 B 读取的结果就变得无效，因为它读取了未提交的数据。

2. 不可重复读（Non-Repeatable Read）

定义：
不可重复读是指在同一事务中，多次读取相同的数据时，读取的结果可能不同。这通常发生在另一个事务在中间修改并提交了数据。

示例：

事务 A 第一次读取某条记录的值。
事务 B 在事务 A 进行时更新了同一条记录并提交。
事务 A 再次读取同一条记录的值，发现与第一次读取的值不同。

3. 幻读（Phantom Read）

定义：
幻读是指在同一事务中，两次查询返回的结果集不同，通常是因为其他事务插入了新的记录。这种现象在第二次查询时会出现“幻影”记录，即在第一次查询中不存在，但在第二次查询中出现。

示例：

事务 A 运行查询，返回符合条件的记录（例如，所有年龄大于 20 的用户）。
事务 B 插入了一条新的记录（例如，年龄为 22 的用户）。
事务 A 再次运行相同的查询，发现多了一条新记录。

实验

我们首先查询一下查询事务的隔离级别

select @@transaction_isolation;           5.7版本以下
select @@tx_isolation；            5.7版本以上

我们将事务的隔离级别设置为读未提交

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

插入实验数据

创建测试数据表：

Create database test_db;

进入对应数据库：

Use test_db;

创建测试数据库：

由于用老师的代码复制全部报错，于是我用ai改了改。

CREATE TABLE `user` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,`age` tinyint(3) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_abc` (`name`, `age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

插入测试数据

INSERT INTO user (`name`, `age`) VALUES ('zhangsan', 23);

实验步骤：

设置不同的隔离级别，进行实验验证：在不同的隔离级别下，多个事务对相同数据的操作，所看到的结果不一样,（脏读、不可重复读、幻读等情况）。

在读已提交和可重复读的隔离级别下，使用mvcc的read view进行可见性算法的推导，然后使用实验进行结果验证。

我们开启两个客户端，那么就有两个线程，从而我们可以把它当作事务A和事务B。

取消MySQL的自动提交功能

【不推荐】设置完不自动提交后（set autocommit = 0;），通过begin开启事务，写完SQL语句，需要进行commit或者rollback处理

set autocommit = 0;
begin;
commit; or rollback

【推荐】或者采用事务的方式，输入“start transaction”来开始事务，最后进行commit或者rollback。

start transaction;
commit; or rollback

注意每次commit或者rollback都要重新start transaction;！！！！！！

读未提交：

设置隔离级别并查看，将A、B线程的隔离级别均设置为读未提交。经验证，当前隔离级别无法解决脏读、不可重复读、幻读。

set @@session.tx_isolation='READ-UNCOMMITTED'; #设置当前线程隔离级别
show variables like 'tx_isolation'; #查看隔离级别

我们先查询一下表中的数据

1. 脏读

我们在A事务中将age改成18，但是不提交，在B事务中查询age的值，在A中再回滚，看是否发生脏数据。

如下图，A事务对age字段由23修改为18时，但是未提交。B事务读取的age字段为18，为A事务未提交的数据。即读到了并不一定最终存在的数据，就是脏读。本次实验中，A事务最终回滚，导致B读取到的数据与数据库中的真实数据36不一致。

2. 不可重复读

由下图，由于收到B事务对数据的修改操作，A事务连续两次对数据的读取出现了不一致现象，说明对于当前的隔离级别，无法避免不可重复读问题。

3. 幻读

在A事务中查询user中所有数据，共1条，然后在B事务中插入一条数据，此时，在 A 中按照相同条件查询，查询到的结果多了一条，产生了幻读现象。因此在当前隔离级别下，无法避免幻读问题。

插入数据

INSERT INTO user (`name`, `age`) VALUES ('liu', 24);

读已提交(Read Committed)

设置隔离级别并查看，将A、B线程的隔离级别均设置为读已提交(Read Committed)。

set @@session.tx_isolation='READ-COMMITTED';
show variables like 'tx_isolation';

经验证，当前隔离级别可以解决脏读，但无法避免不可重复读、幻读。

1.脏读

可以发现解决了脏读的问题

2. 不可重复读

经过实验不能避免不可重复读，B读取的两次数据不一样

3. 幻读

在A事务中查询user中所有数据，共3条，然后在B事务中插入一条数据，此时，在 A 中按照相同条件查询，查询到的结果多了一条，产生了幻读现象。因此在当前隔离级别下，无法避免幻读问题。

可重复读(Repeated Read)

设置隔离级别并查看，将A、B线程的隔离级别均设置为可重复读(Repeated Read)。

set @@session.tx_isolation='REPEATABLE-READ';
show variables like 'tx_isolation';

1.脏读

可以避免重复读，A更新提交，B还是不变

2.不可重复读

B读到了A更新前的数据，

3.幻读

实验过程中，在A事务中查询user中所有数据，共4条，然后在B事务中插入一条数据，并提交事务。此时，在 A 中按照相同条件查询，查询到的结果仍然为4条，没有产生幻读现象。当A提交事务时，查询到了新增的记录。

查看别人的博客，这也是幻读的一种，但是还不理解。

串行化(Serializable)

设置隔离级别并查看，将A、B线程的隔离级别均设置为串行化(Serializable)。

set @@session.tx_isolation='SERIALIZABLE'; #设置当前线程隔离级别，GLOBAL全局
show variables like 'tx_isolation'; #查看隔离级别

或者设置全局隔离级别：

set global transaction isolation level SERIALIZABLE;

经验证，当前隔离级别可以解决脏读、不可重复读、幻读，验证过程见下文。

1. 脏读，不可重复读，串行化

串行化是最严格的隔离级别，要求事务按顺序执行.B事务在未提交的时候，A事务会一直在阻塞状态，事务本质上进入了串行状态，所以有效的避免了这三种情况。

总结

通过这次实验深入了解了四种事务隔离机制的原理。在数据库管理系统中，并发控制是确保多个事务能够安全、有效地并发执行的重要机制。MySQL 提供了多种事务隔离级别，以应对不同的并发场景。遇到了很多问题，最后也得以解决。