事务隔离级别实验--SQL在等待锁结束之后，是否会再次获取快照

事务隔离级别实验--SQL在等待锁结束之后，是否会再次获取快照

跟同事讨论一个删除的诡异操作，在Oracle中一个事务中删除再插入，另外一个事务也进行删除，被夯住，当阻塞结束之后，Oracle会将新插入的这条删除，而PG不会，于是进行一下深挖：

测试表结构及数据：

drop table employea;

create table employea(id int, name varchar(128), salary int);

insert into employea select id, 'zhang_'||id, id*1000 from generate_series(1, 10) id;

1）删除直接指定salary < 5000:

A：

	T1			T2
1	begin;
2				begin;
3				delete from employea where salary < 5000;
4				insert into employea values(3, 'zhang_3', 3000);
5	delete from employea where salary < 5000;
6				end;
7	DELETE 0

B：

	T1			T2
1	begin;
2				begin;
3				delete from employea where salary < 5000;
4	delete from employea where salary < 5000;		
5				insert into employea values(3, 'zhang_3', 3000);
6				end;
7	DELETE 0

C：

	T1			T2	
1				begin;
2				delete from employea where salary < 5000;
3				insert into employea values(3, 'zhang_3', 3000);
4	begin;
5	delete from employea where salary < 5000;		
6				end;
７	DELETE 0

上述三种情况，T1中的delete都在T2的delete之后，会hang住。那么在hang之前的插入、之后的插入都不会被hang住的delete SQL获取到，进而hang结束之后，delete为0条。

能否说明：hang住的SQL，在SQL执行的时候，获取了快照；hang结束之后，可以执行时，并没有再获取快照。

原因：因为A中可以看到，新插入的那条记录，如果hang结束了再次获取快照，应该是可以获取到，并进行删除。

D：

	T1			T2	
1				begin;
2				update employea set salary = salary + 2000 where id = 3;
3	begin;
4	delete from employea where salary < 5000;		
5				end;
6	DELETE 3

where条件只是删除了当前刚刚好满足salary < 5000的3行，id = 3这行已经不满足这个条件了。是delete结束之后，重新获取了快照吗？

2）删除直接指定ID=3：

	T1			T2
1	begin;
2				begin;
3				delete from employea where id = 3;
4				insert into employea values(3, 'zhang_3', 3000);
5	delete from employea where id = 3;
6				end;
7	DELETE 0

F：

step	T1			T2
1	begin;
2				begin;
3				delete from employea where id = 3;
4	delete from employea where id = 3;
5				insert into employea values(3, 'zhang_3', 3000);
6				end;
7	DELETE 0

表现和1）相同。

Oracle中，会将新插入的一条记录给删除。也即是hang结束了，会重新获取一次快照。

3）update场景：

G：

step	T1			T2
1	begin;
2				begin;
3				update employea set salary = salary + 2000 where id = 3;
4	update employea set salary = salary + 1000 where salary < 5000;
5				end;
6	UPDATE 3

H：

step	T1			T2
1	begin;
2				begin;
3				update employea set salary = salary + 1000 where id = 3;
4	update employea set salary = salary + 1000 where salary < 5000;
5				end;
6	UPDATE 4

pithe=*# select * from employea where id = 3;
 id |  name   | salary
----+---------+--------
  3 | zhang_3 |   5000
(1 row)

--G情况下，id = 3的行被更新了两次！本来只给涨1000的，结果给他涨了2000。 --要避免这种情况，就要用可重复读级别。

这里T1并不是去修改事务开始状态时的id =3这行的老数据：3000，而是T2的修改结果进行修改。那么相当于是获取了新的快照，新快照已经能看到T2的修改。！！！

正常情况下，update employea set salary = salary + 1000 where salary < 5000;会更新4条记录。

在本次测试场景下：

很担心，如果像上面delete场景：如果hang住的SQL，在hang状态结束之后没有再次获取快照，会不会对id=3的记录再次加1000，实际测试是没有的。

实际情况：在hang结束之后，update语句salary < 5000;筛选掉了刚刚提交的T2改变的记录（T2将id=3的salary加到了5000，就不满足T1中hang住update的条件了），只更新了3条，没有再次更新id=3的记录。

那么说明：update结束hang状态了之后，是获取过一次快照的。

那么为什么delete和update有不同的表现呢？

题外话：

可重复度的级别下，hang结束之后，发现要更新的行变化了，会报错，避免出现同一行数据，在准备修改时，其他事务已经修改了。虽然读已提交也不会对同一条记录。

step	T1			T2
1	begin isolation level repeatable read  ;
2				begin isolation level repeatable read;
3				update employea set salary = salary + 2000 where id = 3;
4	update employea set salary = salary + 1000 where salary < 5000;
5				end;
6	ERROR:  could not serialize access due to concurrent update

T1里面hang的时候，salary < 5000的条件中，id = 3这行是满足的，但当T2提交之后，id= 3就不属于salary < 5000的条件了，但是这里仍然报错，说明hang完之后，没有再获取新快照？

那么结合3）update场景测试，怎么解释，看来只能看源码了~

4）最终原因：hang结束之后，只对where条件进行再次筛选，没有再次获取最新快照。

两个事物并发修改同一条记录，导致后更新SQL被hang住，当持有锁的事务提交后，hang结束；如果事务隔离级别是read committed，则对更新的对象行再次进行where条件筛选，如果原来满足条件的行还是满足，才会被更新。

需要注意的是，where条件的再评估是针对初始检索筛选出的行而不是对整个表重新执行检索，所以如果这期间有insert过来的新行也满足where条件，或者某个被更新的行从原来不满足where条件变成了满足where条件，是不会被处理的。

存在数据库不一致的问题？？？

“--G情况下，id = 3的行被更新了两次！本来只给涨1000的，结果给他涨了2000。 --要避免这种情况，就要用可重复读级别。

这里T1并不是去修改事务开始状态时的id =3这行的老数据：3000，而是T2的修改结果进行修改。那么相当于是获取了新的快照，新快照已经能看到T2的修改。！！！”

违反一致性：那么在这里没有获取到T2已提交的快照情况下，访问到了T2修改的数据。原因：

在pg中update行不会删除原来行，而是通过ctid指向新行：

pithe=# select * from heap_page_items(get_raw_page('employea', 0)) where t_xmax = 8878957 or t_xmin =8878957;
 lp | lp_off | lp_flags | lp_len | t_xmin  | t_xmax  | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |               t_data
----+--------+----------+--------+---------+---------+----------+--------+-------------+------------+--------+--------+-------+------------------------------------
  3 |   8072 |        1 |     40 | 8878956 | 8878957 |        0 | (0,11) |       16387 |        258 |     24 |        |       | \x03000000117a68616e675f33b80b0000
 11 |   7744 |        1 |     40 | 8878957 |       0 |        0 | (0,11) |       32771 |      10242 |     24 |        |       | \x03000000117a68616e675f3388130000
(2 rows)

而当T2提交之后，T1的id=3这行，虽然还是老的快照的行，但是在读取的时候，会通过ctid获取到新行，进行where条件判断。进而进行更新、删除更改，相当于在旧快照里面访问到了不该访问的数据！（这个跟获取新快照的效果一样，相当于优化了？，那么delete这里就表现不一样了）

	T1			T2	
1				begin;
2				update employea set salary = salary + 1000 where id = 3;
3	begin;
4	delete from employea where salary < 5000;		
5				end;
6	DELETE 4


	T1			T2	
1				begin;
2				update employea set salary = salary + 1000 where id = 3;
3				insert into employea values(3, 'zhang_3_insert', 3000);
4	begin;
5	delete from employea where salary < 5000;		
6				end;
DELETE 4
pithe=*# select * from employea where salary < 5000;
 id |      name      | salary
----+----------------+--------
  3 | zhang_3_insert |   3000
(1 row)

最好的办法是在阻塞结束之后，重新获取一次快照，这样就能统一delete、update、insert的新行的影响。

那么如果我们不允许去读取T2更新的行，更新、删除在老行进行操作：

更新：会把老行进行更新？那么出现两个新行：T1、T2的，怎么统一？？？

也无法不统一，直接报错，那么这就是可重复读的控制逻辑了。

删除：D这种情况举例，即使已经更新了，不满足删除的条件，但是还是用老的快照的话，应该继续进行删除老行。那么老行已经经过update删除过了，新行继续保留？跟现在的最终结果是一样的。

后续：

我们都知道PG的几种隔离级别是解决什么问题的，但是在具体业务中，有什么需求的情况下才需要REPEATABLE READ、SERIALIZABLE？

posted @ 2021-03-12 15:41 狂神314 阅读(145) 评论(0) 编辑收藏举报

刷新页面返回顶部

狂神314

事务隔离级别实验--SQL在等待锁结束之后，是否会再次获取快照

公告