MySQL - 锁和可重复读的隔离级别结合起来的一个示例(来自MySQL45讲第8章)

奇怪的现象(来自MySQL45讲第8章思考题)

当前隔离级别为可重复读

复现过程:
有A、B两个事务
B先执行了语句,并且马上commit

begin;
update t set c = 0 where id = c;
commit;

A执行了语句,但是没有commit

begin;
select * from t;
update t set c = 0 where id = c;    // 奇怪的现象就在这,0行被改变
select * from t;    // 但读出来仍是旧数据

表结构

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, c) values(1,1),(2,2),(3,3),(4,4);

原因

当前读的定义:不管在哪个隔离级别下,总是读取已经提交完成的最新版本。update语句,都是先执行当前读,再去更新的。
但是由于是可重复读隔离界别,查询只承认在事务启动前就已经commit的数据,所以普通的select查到了begin前已经commit的数据。

解决方法

一个事务的更新操作被另外一个事务的更新操作覆盖。在RR状态下,普通select的时候是会获得旧版本数据的,但是update的时候就检索到最新的数据。
解决方法:在读取的过程中设置一个排他锁,在 begin 事务里, select 语句中增加 for update(增加写锁),或者lock in share mode(增加读锁)后缀,这样可以保证别的事务在此事务完成commit前无法操作记录。

如果在加锁前,B事务已经update commit,则select for update 或则select lock in share mode也会读最新的数据,并且加锁。

如果在加锁后,B事务才去update,则这个事务则会变成LOCK WAIT状态,直到A事务commit释放锁或者B事务语句阻塞超时。

posted @ 2021-12-17 15:00  Rocin  阅读(46)  评论(0编辑  收藏  举报