tx1 | tx2 |
---|---|
set session transaction isolation level read uncommitted; | |
start transaction; | |
select * from account;/两个账户都为 1000/ | |
start transaction; | |
update account set balance = 2000 where accountNo=1; | |
select * from account;/1号账户2000, 2号账户1000/ |
tx2 未提交的情况下,tx1 仍然读取到了它的更改,此时与第一次读取的数据不一致,则是产生了脏读
tx1 | tx2 |
---|---|
set session transaction isolation level read committed; | |
start transaction; | |
select * from account; /两个账户都为 1000/ | |
update account set balance = 2000 where accountNo=1; | |
select * from account; /1号账户2000, 2号账户1000/ |
tx1 在同一事务内,两次读取的结果不一致,当然,此时 tx2 的事务已提交
tx1 | tx2 |
---|---|
set session transaction isolation level repeatable read; | |
start transaction; | |
select * from account; /存在 1,2 两个账户/ | |
insert into account values(3, 1000); | |
select * from account; /发现还是只有 1,2 两个账户/ | |
insert into account values(3, 5000); /* ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' */ |
tx1 查询时并没有发现 3 号账户,执行插入时却发现主键冲突异常,就好像出现了幻觉一样
tx1 | tx2 |
---|---|
set session transaction isolation level repeatable read; | |
start transaction; | |
select * from account; /存在 1,2 两个账户/ | |
select * from account where accountNo=3 for update; | |
insert into account values(3, 1000); /* 阻塞 */ | |
insert into account values(3, 5000); |
tx1 | tx2 |
---|---|
set session transaction isolation level serializable; | |
start transaction; | |
select * from account; /* 存在 1,2 两个账户 */ | |
insert into account values(3, 1000); /* 阻塞 */ | |
insert into account values(3, 5000); |
串行读隔离级别下,普通的 select 也会加共享读锁,其它事务的查询可以并发,但增删改就只能阻塞了
如果觉得内容不错的话,希望大家可以帮忙点赞转发一波,这是对我最大的鼓励,感谢🙏🏻
END