Friday, October 8, 2021

Select for update skip locked differences in PostgreSQL and Oracle

 With following setup, how to code the database logic such that concurrent sessions to reserve seats for themselves without blocking each other?

create table seats (id integer primary key, passenger varchar(10));

insert into seats (1,null);

insert into seats (2,null);

insert into seats (3,null);

insert into seats (4,null);

commit;



PostgreSQL: Using LIMIT1 + FOR UPDATE SKIP LOCKED


Session 1

Session 2

Begin

Begin

update seats set passenger='session1'

where id = (select id from seats 

    where passenger is null limit 1 for update skip locked);

 

 

update seats set passenger='session2'

where id = (select id from seats 

    where passenger is null limit 1 for update skip locked);

Commit;

Commit;

mytest=> select *from seats order by id;

 id | passenger 

----+-----------

  1 | session1

  2 | session2

  3 |

  4 | 

 


Oracle: Using Cursor + FOR UPDATE SKIP LOCKED

Oracle only lock the records when cursor is fetched. The "rownum=1" approaches doesn't work here as it includes both locked and unlocked rows.

Session 1

Session 2

declare 

  cursor c is select id from seats where passenger is null for update skip locked;

  v_id number;

begin

    open c;

    fetch c into v_id;

    close c;

    update seats set passenger = 'session1' where id = v_id;

end;

/

 

 

declare 

  cursor c is select id from seats where passenger is null for update skip locked;

  v_id number;

begin

    open c;

    fetch c into v_id;

    close c;

    update seats set passenger = 'session2' where id = v_id;

end;

/

Commit;

Commit;

SQL> select * from seats;

 

   ID    PASSENGER 

_____ ____________ 

    1 session1     

    2 session2     

    3              

    4  

 

No comments:

Post a Comment