mysql如何解决脏读幻读不可重复读 mysql 解决脏读

转载

编程思想者 2023-09-30 21:23:12

文章标签 mysql如何解决脏读幻读不可重复读 mysql 数据库 sql 数据 文章分类 MySQL 数据库

要想在mysql命令行中实现脏读，不可重复读和幻读的问题，必须弄明白 autocommit和start transaction以及begin/commit之间的区别，这样实现起来更快，在本次实验中，推荐使用的是begin/commit模式，可以参考本人的另一篇博客

至于数据库的隔离级别以及事务的特性，这些都是基础知识，网上有一大堆资料，

1 脏读

1.1 说明

在两个事务中，一个事务读到了另一个事务未提交的数据（即事务A读取到事务B修改数据，当事务B未提交数据到数据库或出错时，事务A再进行读取发现数据已修改。

1.2 mysql命令行模拟脏读

1.2.1 在 “读未提交” 的隔离级别下测试

创建数据库test以及表rooms

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for rooms
-- ----------------------------
DROP TABLE IF EXISTS `rooms`;
CREATE TABLE `rooms`  (
  `id` int(3) NOT NULL,
  `tenant_id` int(3) DEFAULT NULL,
  `rent` int(5) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of rooms
-- ----------------------------
INSERT INTO `rooms` VALUES (120, 1, 1000);
INSERT INTO `rooms` VALUES (123, 2, 222);

SET FOREIGN_KEY_CHECKS = 1;

先开一个mysql客户端（也可以说是命令行），使用use test;启用test数据库，再使用set session transaction isolation level read COMMITTED;
使用这个数据库,再设置事务的隔离级别为读未提交，最后使用select语句进行一次查询操作，方便后面的对照

(1)使用这个数据库

use test;

(2)设置事务的隔离级别
set session transaction isolation level read UNCOMMITTED;

查看数据库的隔离级别

select @@session.tx_isolation;

结果:

+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED         |
+------------------------+

(3)执行select * from rooms;操作

查询结果

+-----+-----------+------+
| id  | tenant_id | rent |
+-----+-----------+------+
| 120 |         1 | 1000 |
| 123 |         2 |  222 |
+-----+-----------+------+

开启另一个mysql命令行，尝试更新一下数据（但不提交，如果要提交最后一行加入 commit指令）

begin; // 这个语句开启一个事务
update rooms set id = 1 where id = 120;

在update所在的事务没有提交（使用commit指令提交）的情况下使用select语句

select * from rooms

结果（因为事务的隔离级别为读未提交，所以读取到了update事务未提交的内容）：

+-----+-----------+------+
| id  | tenant_id | rent |
+-----+-----------+------+
| 1   |         1 | 1000 |
| 123 |         2 |  222 |
+-----+-----------+------+

1.2.2 在 “读已提交” 的隔离级别下测试（脏读的解决方案）

创建数据test和rooms表格
同 1.2中的第一步
使用这个数据库并且使用select语句进行一次查询操作，方便后面的对照
同 1.2中的第二步
先将数据库的隔离级别设置为 “读已提交 ”

set session transaction isolation level read COMMITTED;

查看数据库的隔离级别

select @@session.tx_isolation;

结果:

+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+

关闭掉数据库自动提交事务的特性

set autocommit = 0;

开启另一个mysql命令行，尝试更新一下数据，将id为1的数据行的id更改为120（但不提交，如果要提交最后一行加入 commited指令）

update rooms set id = 1 where id = 120;

在update所在的事务没有提交的情况下使用select语句

select * from rooms

结果（因为开启了事务并且隔离级别为读已提交，所以并没有读取到update事务未提交的内容）：

+-----+-----------+------+
| id  | tenant_id | rent |
+-----+-----------+------+
| 120 |         1 | 1000 |
| 123 |         2 |  222 |
+-----+-----------+------+

2 不可重复读

##2.1 定义
是指在一个事务内多次读取同一集合的数据，但是多次读到的数据是不一样的，这就违反了数据库事务的一致性的原则。但是，这跟脏读还是有区别的，脏读的数据是没有提交的，但是不可重复读的数据是已经提交的数据。

2.2 测试不可重复读（在数据库的读已提交隔离级别下）

在继续沿用rooms表的情况下，

先开一个mysql客户端（也可以说是命令行），使用use test;启用test数据库，再使用set session transaction isolation level read COMMITTED; 命令设置数据库事务的隔离级
别为读已提交（因为数据库的默认隔离级别为可重复读）
开启事务，使用start transaction;或者begin;命令，
使用select * from rooms; 查询数据库中的数据

查询结果如下：

+-----+-----------+------+
| id  | tenant_id | rent |
+-----+-----------+------+
| 120 |         1 | 1000 |
| 123 |         2 |  222 |
+-----+-----------+------+

接着再开一个命令行，使用 update rooms set rent = 1100 where id = 120; 将房间号为120的房租改为1100；注意这个命令行中，我们不用设置手动开启或者关闭一个事务，mysql默认会为我们提交这个更新事务。
在第一个命令行中，使用select * from rooms; 查询数据库中的数据（注意这个时刻，第一个命令行中的事务还处于待提交状态，因为没有使用commit;命令）。

查询结果：查询到了update事务提交后的数据，由此造成了不可重复读的问题

+-----+-----------+------+
| id  | tenant_id | rent |
+-----+-----------+------+
| 120 |         1 | 1100 |
| 123 |         2 |  222 |
+-----+-----------+------+

2.3 不可重复读的解决方案（将数据库的隔离级别改为默认的隔离级别 - 可重复读即可）

在继续沿用rooms表的情况下，

先开一个mysql客户端（也可以说是命令行），使用set session transaction isolation level repeatable read; 命令设置数据库事务的隔离级
别为读已提交（因为数据库的默认隔离级别为可重复读）
开启事务，使用start transaction;或者begin;命令，
使用select * from rooms; 查询数据库中的数据

查询结果如下：

+-----+-----------+------+
| id  | tenant_id | rent |
+-----+-----------+------+
| 120 |         1 | 1100 |
| 123 |         2 |  222 |
+-----+-----------+------+

接着再开一个命令行，使用 update rooms set rent = 555 where id = 120; 将房间号为120的房租改为555；注意这个命令行中，我们不用设置手动开启或者关闭一个事务，mysql默认会为我们提交这个更新事务。
在第一个命令行中，使用select * from rooms; 查询数据库中的数据（注意这个时刻，第一个命令行中的事务还处于待提交状态，因为没有使用commit;命令）。

查询结果：可以看到其在事务被提交之前，每一次select都读取的相同，即达到了可重复读的目的。

+-----+-----------+------+
| id  | tenant_id | rent |
+-----+-----------+------+
| 120 |         1 | 1100 |
| 123 |         2 |  222 |
+-----+-----------+------+

2.4 一致性非锁定读（解决方案）

是多版本并发控制（MVCC）来实现的一种场景，MVCC是在读已提交、可重复读两种隔离级别下作用，当开始事务后，执行select时，会生成快照数据（read view）。（而两个隔离级别的区别是，读已提交每次select都生成最新的read view，而可重复读，只有第一次会生成最新的readview，后续再select同一个记录，则直接读取之前生成的readview数据，这也是可重复读解决不可重复读的办法。）

3 幻读

3.1 定义

幻读：事务A 按照一定条件进行数据读取，期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据称为幻读
那么可重复读情况下通过生成、读取readview数据，会保证查到快照读的数据，不会查到最新数据，按理说是不会出现幻读的呀。

这里有两点问题，

虽然快照读查不到最新数据，但是若使用当前读，就能查到最新数据，出现幻读；
即使在快照读的情况下，read view查不到最新的数据，但是若事务B最新插入的数据满足事务A后续需要修改语句的条件，执行修改操作会将未看到的数据修改了，这也是幻读带来的问题。故不可重复读没有完全解决幻读的问题。

3.2 幻读的模拟过程

开启第一个mysql客户端，启用test数据库，使用数据库默认的隔离级别（可重复读）。代码可参考【1】和【2】
在第一个客户端中使用begin;命令开启事务，并且执行select * from rooms; 操作,但不提交
结果如下

+-----+-----------+------+
| id  | tenant_id | rent |
+-----+-----------+------+
| 120 |         1 | 1133 |
| 123 |         2 |  222 |
+-----+-----------+------+

开启第二个mysql客户端，在第二个客户端中执行insert into rooms (id, tenant_id, rent) values (124, 5, 600);语句
在第一个客户端中再次执行 select * from rooms; 操作，查到的结果和第2步中的一样，因为在 "可重复读"的隔离级别下，使用的是快照读，读取的都是第一次select的数据

+-----+-----------+------+
| id  | tenant_id | rent |
+-----+-----------+------+
| 120 |         1 | 1133 |
| 123 |         2 |  222 |
+-----+-----------+------+

这里如果我们把快照读改为当前读，即执行select * from rooms for update;每次读取的都是最近更新的数据，

+-----+-----------+------+
| id  | tenant_id | rent |
+-----+-----------+------+
| 120 |         1 | 1133 |
| 123 |         2 |  222 |
| 124 |         5 |  600 |
+-----+-----------+------+

即便此时我们不使用当前读，而使用快照读，直接执行更新操作，更新sql会将符合条件的由其他事务提交的最新数据都会更新，这也是幻读带来的影响（因为更新事务会先扫描全表查询数据，然后将得到的数据拿回内存，对内存中的数据进行修改）。

（1）在快照读的情况下执行更新操作

update rooms set rent = rent - 200 where rent > 200 and id < 125;

结果
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0

（2）执行快照读select 操作，

+-----+-----------+------+
| id  | tenant_id | rent |
+-----+-----------+------+
| 120 |         1 |  933 |
| 123 |         2 |   22 |
| 124 |         5 |  400 |
+-----+-----------+------+

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。