MYSQL学习笔记28: 事务并发问题(脏读, 不可重复读, 幻读)

事务的四大特性ACID

---

并发事务问题

脏读

事务A语句1 update了id=1的数据, 但是还没有commit

事务B语句1 select读取到了事物A还没有提交的数据

不可重复读

事务A语句1, select

事务B语句1, update, commit

事务A语句3, select读取到了被事务B修改的数据, 两次读取的数据不同

幻读

解决不可重复读导致的

事务A语句1, select id=1, id=1不存在, 没有找到对应记录

事务B语句1, insert id=1, commit

事务A语句2, insert id=1, id=1已存在, 主键冲突

事务A语句3, select id=1,解决了不可重复读, id=1不存在, 没有找到对应记录

---

事务隔离级别

事务隔离级别越高, 数据越安全, 但是性能越低, 隔离级别越低反之

read uncommitted: 读取commit前的数据, 会出现所有并发问题

read committed: 读取commit后的数据, 解决了脏读问题, 会出现不可重复读和幻读问题

repeatable read: 数据存入缓存区中, 只从缓存区中读取数据, 而非从磁盘, 这个缓存区没有实时更新, 也就是只读取事务开始前的数据. 解决了脏读和不可重复读的问题, 会出现幻读问题

Seriallizable: 事务A操作表时, 事务B如果需要操作表会被阻塞(类似于锁), 只有事物A完成后, 事务B才会继续执行, 不会发生以上并发问题

oracle默认隔离级别 Read committed

MYSQL默认隔离级别 Repeatable Read

---

查看事务的隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

# 可选参数,session 仅设置当前会话的隔离级别 global,设置全局的隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE]

设置当前对话的隔离级别为read uncommitted

set session transaction isolation level read uncommitted;
# 查询隔离级别, 当前会话的隔离级别变为read uncommitted
SELECT @@TRANSACTION_ISOLATION;

---

read uncommitted

读取未提交数据, 会出现所有并发问题

修改当前会话(事务A)的隔离级别为read uncommitted

set session transaction isolation level read uncommitted;
#查询当前事物的隔离级别
select @@transaction_isolation;

开始事务A

start transaction;

事务A查询数据

select * from account;

开启事务B, 修改数据

#开启事务B
start transaction;
#修改数据, money+=100
update account set money = money+100;

事务A再次读取数据

此时事物A读取到了事务B没有commit的数据, 即脏读

select * from account;

---

read committed

读取提交后的数据,解决脏读问题(事务2读取到了事务1未提交的数据), 会出现不可重复读和幻读的并发问题.

#设置事务A的隔离级别为read committed
set session transaction isolation level read committed;
#开启事务A
start transaction;
#开启事务B
start transaction;
#事务A查询数据
select * from account;
#事务B修改数据, 未提交
update account set money = money+100;
#事务A再次查询数据
select * from account;

事务B未提交数据, 事务A两次查询的数据相同, 此时解决了脏读问题.

但此时如果事务B提交数据, 就会出现不可重复读的问题(事务A两次查询的数据不同)

#事务A查询数据
select * from account;
#事务B提交数据
commit;
#事务A查询数据,与第一次查询的结果不同
select * from account;

---

repeatable read

可重复读, 解决了脏读, 不可重复读, 会出现幻读的并发问题.
将事物开始前的数据存入缓冲区, 从缓冲区读取数据, 而非从磁盘中, 缓冲区的数据不是实时更新的, 解决脏读和不可重复读问题

#设置事务A的隔离级别为repeatable read
set session transaction isolation level repeatable read;
#开始事务A
start transaction;
#开始事务B
start transaction;
#事务A查询
select * from account;
#事务B修改,并提交
update account set money = money+100;
commit;
#事务A查询数据, 两次查询结果一致, 解决不可重复读问题
select * from account;

但是此时如果通过事务B插入新数据, 事务A查询并插入同主键新数据时, 会出现幻读

#事务A查询主键为3的数据, 没有查找到记录
select * from account where id=3;

#幻读1
#事务B插入数据,没有提交
insert into account values (3,'三号',2000);
#事务A再次查询主键为3的数据, 仍然没有查找到记录
select * from account where id=3;
#在事物B提交前,事务A插入同样的数据, 遭到阻塞(为什么?)(悲观锁)
insert into account values (3,'三号',2000);
#事务B提交后, 事务A语句继续执行,报错.
commit;

#幻读2
-- 开启事务A,B
#事务B插入数据, commit
insert into account values (3,'三号',2000);
commit;
#事务A查询id=3记录, 没有对应记录 
select * from account where id=3;
#事务A插入id=3记录, 主键冲突异常
insert into account values (3,'三号',2000);

---

Serializable

串行化, 解决了所有并发问题

表在被事务A操作时, 事务B操作表的行为会被阻塞, 直到事物A执行完毕, 事务B语句才会进行. 类似于多线程中的锁.

#设置当前会话隔离等级为serializable
set session transaction isolation level serializable;
#开启事务A, 开始事务B
start transaction;
#事务A查询id=4
select * from account where id=4;
#事务B尝试插入id=4记录, 被阻塞,因为事物A正在操作表account
insert into account values (4,'DDD',2000);
#事务A插入id=4记录, 并提交, 事务B插入语句开始执行, 报主键冲突异常
insert into account values (4,'DDD',2000);
commit;