mysql常见问题系列-并发读写

背景

REPEATABLE-READ在事务开启时建立read view
READ-COMMITED在每条语句执行时建立read view
导致了READ-COMMITED可能会欢幻读而REPEATABLE-READ不会

但是对于写操作REPEATABLE-READ任然需要锁机制来解决并发的问题

准备

create database isotest;
create table user(id bigint not null auto_increment primary key,name varchar(100) not null default "",amount int not null default 0);
insert into user(name,amount) values("demo1",1),("demo1",2);

开启两个session

session one

set transaction_isolation = 'REPEATABLE-READ';
begin
select * from user where name = 'demo1';

发现有上面两条

session two

set transaction_isolation = 'REPEATABLE-READ';
begin
insert into user(name,amount) values("demo1",3),("demo1",4);

session one

select * from user where name = 'demo1';

发现仍然是初始的两条

session one

update user set amount = 5 where name = 'demo1';

得到如下提示

Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

显然是不符合预期的

解决方式

1 对事务后续意图时使用select for update
2 对所有查询使用select for share
3 查询出来id,上层通过id再去执行更新

总结

其实就是竞态资源的操作必须有并发控制,不然就会出现读到的数据和实际写入的有出入