Isolation of PostgreSQL CTID Physical Line Numbers in Concurrent DML Operations

This short article discusses CTID isolation problems and solutions.

By digoal

Background

Question:

The isolation problems usually occur in scenarios where CTID is used to simulate scenarios like update and delete limit. Even subqueries have concurrent isolation problems, how can we solve these problems?

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;  -- Increase the cost, execute select first, then sleep, and then delete.

Concurrency problem recurrence:

session1:  update tbl set id = 3 where ctid in (select ctid FROM TBL where id=1 and exists (select 1 from pg_sleep(30)));   --  Halfway through, only subqueries are performed. The result is (0,1)       
  
session2:  update tbl set id=2 where id = 1;  -- An updated row is returned.
  
Session1: Make changes according to the ctid of the subquery, and you will find the line whose current id is already 2. Change the id of that line to 3 to return the update success, and then you modify one line successfully. Because there is a page offset from HOT, ctid(0,1) to ctid(0,2) and then to tuple2.

You can use recheck, RR isolation level (equivalent to locking or throwing an error), or use 'for update' to lock to lock.

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;  -- An updated row is returned.
  
Session1: Make changes according to the ctid of the subquery, and you will find the row where the current id is already 2. Since recheck id=1 is added, the update record is 0

2. RR Mode

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 mode    
  
session2:  update tbl set id=2 where id = 1;  -- An updated row is returned.
  
Session1: Make changes according to the ctid of the subquery, and you will find the line where the current id is already 2, but the RR mode is used, and it is found that the record has been updated, so an exception is thrown.     
ERROR:  could not serialize access due to concurrent update

3. for update locks the limit line

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 lock     
    
session2:  update tbl set id=2 where id = 1; -- Wait, because in session 1, the row with id = 1 is locked     
    
session1: Make changes according to the ctid of the subquery, and the returned result is normal.    
  
Session2: The row with id=1 cannot be updated because its id is changed to 3.

0 0 0

Share on

: digoal

281 posts | 24 followers
Follow

PolarDB for PostgreSQL

Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.

Learn More