当前位置：首页 > news >正文

MySQL-事物隔离级别

news 2024/10/23 7:29:39

1. MySQL事物的四种隔离级别

1.1 读未提交（READ UNCOMMITTED）

READ UNCOMMITED提供了事物之间最小限度的隔离，除了幻读和不可重复读取的操作外，处于这个隔离级别的事务可以读到其它事务还未提交的数据。

1.2 读已提交（READ COMMITTED）

处于READ COMMITTED隔离级别的事务可以读取到其它事务对数据的修改。也就是说，在处理事务期间，如果其他事务修改了相应的表（已提交），那么同一个事务的多次SELECT执行可能返回不同的结果。

1.3 可重复读（REPEATABLE READ）

可重复读隔离级别上，当前正在执行事务的变化不能被其它事务看到，也就是说，同一个事务的多次SELECT执行结果是相同的。

1.4 串行化（SERIALIZABLE）

串行化，事务之间一个接一个的执行，这种隔离级别提供了事物之间最大限度的隔离。

2. 通过例子理解脏读、不可重复读、幻读

2.1 读未提交隔离级别下脏读、不可重复读、幻读

2.1.1 准备测试数据

设置事务隔离级别为READ UNCOMMITTED

-- 在DBeaver打开2个SQL编辑窗口（不同的session），各执行如下语句。
-- 将两个SESSION的事务隔离级别设置为读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

创建一张用户表t_user，预插入两条数据，如表格：

2.1.2 脏读

脏读，在一个事务中可以读取到另一个事务还未提交的数据。

窗口A

--事务A
line1: START TRANSACTION;
line2: INSERT INTO t_user(id, user_name, balance) VALUES (3, '王五', 1000);
line3: COMMIT;

窗口B

-- 事务B
line1: START TRANSACTION;
line2: SELECT * FROM t_user;
line3: COMMIT;

测试步骤：

a) 窗口A执行line1, line2, 插入用户“王五”，先不执行提交

b) 窗口B执行line1,line2时，可以看到用户“王五”，而此时窗口A尚未提交事务，产生了脏读

2.1.3 不可重复读

不可重复读，在一个事务中多次执行SELECT语句，返回的结果不相同。

A窗口

-- 事务A
line1: START TRANSACTION;
line2: INSERT INTO t_user(id, user_name, balance) VALUES (3, '王五', 1000);
line3: INSERT INTO t_user(id, user_name, balance) VALUES (4, '赵六', 1000);
line4: COMMIT;

B窗口

-- 事务B
line1: START TRANSACTION;
line2: SELECT * FROM t_user;
line3: SELECT * FROM t_user;
line4: COMMIT;

执行步骤

a) A窗口执行line1, line2语句，B窗口执行line1, line2语句，此时B窗口可以读取到“王五”。

b) A窗口接着执行line3, B窗口执行line3，此时B窗口可以读取到“王五”和“赵六”

两次SELECT查询结果不一致，所以是不可重复读

2.1.4 幻读

幻读与不可重复读比较像。事务比做人的话，就是当前事务产生幻觉了。

窗口A

--事务A
line1: START TRANSACTION;
line2: INSERT INTO t_user(id, user_name, balance) VALUES (3, '王五', 1000);
line3: COMMIT;

窗口B

-- 事务B
line1: START TRANSACTION;
line2: SELECT * FROM t_user;
line3: DELETE FROM t_user WHERE id = 3;
line4: COMMIT;

执行步骤

a) A窗口执行line1, line2，B窗口执行line1, line2 , 可以读取到“王五”

b) B窗口继续执行line3，发现执行删除操作一直在等待，无法完成删除。原因是因为id=3的记录在窗口A尚未提交，脏读导致可以被窗口B看到，所以是无法删除的。此时就产生了幻觉，明明有id=3的记录，却无法删除。

2.2 读已提交隔离级别下脏读、不可重复读、幻读

2.2.1 设置事务隔离级别为READ COMMITTED

-- 在DBeaver打开2个SQL编辑窗口（不同的session），各执行如下语句。
-- 将两个SESSION的事务隔离级别设置为读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2.2.2 脏读

窗口A

--事务A
line1: START TRANSACTION;
line2: INSERT INTO t_user(id, user_name, balance) VALUES (3, '王五', 1000);
line3: COMMIT;

窗口B

-- 事务B
line1: START TRANSACTION;
line2: SELECT * FROM t_user;
line3: COMMIT;

执行步骤：

a) 窗口A执行line1, line2 ,窗口B执行line1, line2看不到用户“王五”

b) 窗口A执行line3, 窗口B再次执行line2，此时可以看到用户“王五”，所以不存在脏读问题

2.2.3 不可重复读

窗口A

--事务A
line1: START TRANSACTION;
line2: INSERT INTO t_user(id, user_name, balance) VALUES (3, '王五', 1000);
line3: COMMIT;

窗口B

-- 事务B
line1: START TRANSACTION;
line2: SELECT * FROM t_user;
line3: COMMIT;

执行步骤：

a) 窗口B执行line1, line2，此时看不到用户“王五”

b) 窗口A执行line1, line2, line3

c) 窗口B再次执行line2，此时可以看到用户“王五”，两次SELECT的结果不一致，所以是不可重复读

2.2.4 幻读

窗口A

--事务A
line1: START TRANSACTION;
line2: INSERT INTO t_user(id, user_name, balance) VALUES (3, '王五', 1000);
line3: COMMIT;

窗口B

-- 事务B
line1: START TRANSACTION;
line2: SELECT * FROM t_user;
line3: INSERT INTO t_user(id, user_name, balance) VALUES (3, '王五', 1000);
line4: COMMIT;

执行步骤：

a) 窗口B执行line1，line2，此时没有看到用户“王五”

b) 窗口A执行line1, line2, 插入用户“王五”

c) 窗口B执行line3时会一直等待窗口A的事务提交或回滚。在这里窗口B产生了幻觉，明明执行SELECT没有看到用户“王五”，但是插入不成功。

2.3 REPEATABLE READ

和READ COMMITTED相比，REPEATTED READ进一步解决了不可重复读问题，但是幻读则未能解决。REPEATABLE READ中关于幻读的测试与上一小节基本一致，不同的是在步骤b中执行到line3提交事务。由于REPEATABLE READ已经解决了不可重复读，因此步骤b即使提交了事务，SELECT也查不出已经提交的数据。继续插入会报错。