PostgreSQL CTID 物理行号在并发DML时的隔离性问题

作者

digoal

日期

2022-04-07

标签

PostgreSQL , dml , ctid

问题:
通常出现在使用ctid实现模拟update,delete limit的场景. 哪怕是子查询也存在并发隔离性问题, 怎么解决?

• 《在PostgreSQL中实现update | delete limit - CTID扫描实践 (高效阅后即焚)》

create table tbl (id int, c1 int, c2 int);       
create index idx_tbl_1 on tbl (id);      
insert into tbl values (1,1,1);    
set enable_indexonlyscan =off;      
set enable_bitmapscan =off;      
set enable_indexscan =on;      
set enable_seqscan=off;      
alter function pg_sleep(double precision) cost 100000;  -- 把代价调大, 优先执行select, 然后sleep, 然后再delete.       

并发问题复现:

session1:  update tbl set id = 3 where ctid in (select ctid FROM TBL where id=1 and exists (select 1 from pg_sleep(30)));   --  进行到一半，只进行了子查询结果为（0，1）       
session2:  update tbl set id=2 where id = 1;  -- 返回更新了一行      
session1:  按子查询的ctid 做变更，会找到当前id已经是2的那一行, 将那一行的id改成3 返回更新成功，修改了一行 . 因为这里存在HOT, ctid(0,1) 链到 ctid(0,2) 再到tuple2的page offset.     

解决这个问题, 可以使用recheck, 或者RR模式隔离级别(相当于加锁或者抛出错误), 或者for update进行锁定 .

1、recheck

session1:  update tbl set id = 3 where ctid in (select ctid FROM TBL where id=1 and exists (select 1 from pg_sleep(30))) and id=1;   --  recheck       
session2:  update tbl set id=2 where id = 1;  -- 返回更新了一行      
session1:  按子查询的ctid 做变更，会找到当前id已经是2的那一行, 由于加了 recheck id=1, 所以更新记录为0     

2、RR模式

session1:  begin ISOLATION LEVEL REPEATABLE READ; update tbl set id = 3 where ctid in (select ctid FROM TBL where id=1 and exists (select 1 from pg_sleep(30))) ;   --  rr模式    
session2:  update tbl set id=2 where id = 1;  -- 返回更新了一行       
session1:  按子查询的ctid 做变更，会找到当前id已经是2的那一行, 但是使用了RR模式, 发现记录已被更新, 所以抛出异常.     
ERROR:  could not serialize access due to concurrent update     

3、for update锁定limit的行

session1:  update tbl set id = 3 where ctid = any(array(select ctid from (select ctid FROM TBL where id=1 limit 1 for update SKIP LOCKED) t union all select null from pg_sleep(30)));   --  for update 锁定     
session2:  update tbl set id=2 where id = 1;  -- 等待, 因为session 1 锁定了   id = 1的行     
session1:  按子查询的ctid 做变更，正常.    
session2:  更新不到ID=1的行, 因为id=3了.     

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.