Thread

1. Concurrent delete and insert on same key, potentially a bug

   Sasa Vilic <sasavilic@gmail.com> — 2025-03-28T19:38:50Z

   Hello,
   
   Please excuse my ignorance, because I might be missing something obvious,
   but how is this not a bug?
   
   I simply open 2 terminals and connect to the same database.
   
   **TERMINAL 1:**
   
   sasa=# create table tst1(id int primary key);
   CREATE TABLE
   sasa=# insert into tst1(id) values(1);
   INSERT 0 1
   sasa=# begin;
   BEGIN
   sasa=# select * from tst1 where id = 1;
    id
   ----
     1
   (1 row)
   
   **TERMINAL 2:**
   
   sasa=# begin;
   BEGIN
   sasa=# select * from tst1 where id = 1;
    id
   ----
     1
   (1 row)
   
   **TERMINAL 1:**
   
   sasa=# delete from tst1 where id = 1;
   DELETE 1
   sasa=# insert into tst1(id) values(1);
   INSERT 0 1
   
   **TERMINAL 2:**
   
   sasa=# delete from tst1 where id = 1;
   // client is blocked due to row lock/index lock
   
   **TERMINAL 1:**
   
   sasa=# commit;
   COMMIT
   
   **TERMINAL 2:**
   // resumes because terminal 1 committed transaction
   DELETE 0
   sasa=# insert into tst1(id) values(1);
   ERROR:  duplicate key value violates unique constraint "tst1_pkey"
   DETAIL:  Key (id)=(1) already exists.
   
   To my understanding, the second client (terminal 2) should already see
   changes from the first client, because the transaction isolation level is
   READ COMMITTED and the first client did commit its transaction.
   
   Thanks in advance!
   
   Kind Regards
   Sasa Vilic
   

2. Re: Concurrent delete and insert on same key, potentially a bug

   Gurjeet Singh <gurjeet@singh.im> — 2025-03-28T20:52:18Z

   On Fri, Mar 28, 2025 at 12:39 PM Sasa Vilic <sasavilic@gmail.com> wrote:
   >
   > Hello,
   >
   > Please excuse my ignorance, because I might be missing something obvious, but how is this not a bug?
   >
   > I simply open 2 terminals and connect to the same database.
   >
   > **TERMINAL 1:**
   >
   > sasa=# create table tst1(id int primary key);
   > CREATE TABLE
   > sasa=# insert into tst1(id) values(1);
   > INSERT 0 1
   > sasa=# begin;
   > BEGIN
   > sasa=# select * from tst1 where id = 1;
   >  id
   > ----
   >   1
   > (1 row)
   >
   > **TERMINAL 2:**
   >
   > sasa=# begin;
   > BEGIN
   > sasa=# select * from tst1 where id = 1;
   >  id
   > ----
   >   1
   > (1 row)
   >
   > **TERMINAL 1:**
   >
   > sasa=# delete from tst1 where id = 1;
   > DELETE 1
   > sasa=# insert into tst1(id) values(1);
   > INSERT 0 1
   >
   > **TERMINAL 2:**
   >
   > sasa=# delete from tst1 where id = 1;
   > // client is blocked due to row lock/index lock
   >
   > **TERMINAL 1:**
   >
   > sasa=# commit;
   > COMMIT
   >
   > **TERMINAL 2:**
   > // resumes because terminal 1 committed transaction
   > DELETE 0
   > sasa=# insert into tst1(id) values(1);
   > ERROR:  duplicate key value violates unique constraint "tst1_pkey"
   > DETAIL:  Key (id)=(1) already exists.
   >
   > To my understanding, the second client (terminal 2) should already see changes from the first client, because the transaction isolation level is READ COMMITTED and the first client did commit its transaction.
   >
   
   The session/transaction interactions you show are exactly how I'd
   expect the to interact. I don't see any buggy behaviour here.
   
   It would help others if you pointed at specific lines in the text
   above and share what you would expect instead to happen at that step.
   I think your concern is about the point in time where the second
   session prints DELETE 0, but can't say for sure.
   
   Best regards,
   Gurjeet
   http://Gurje.et
   
   
   
   

3. Re: Concurrent delete and insert on same key, potentially a bug

   Adrian Klaver <adrian.klaver@aklaver.com> — 2025-03-28T20:57:36Z

   On 3/28/25 12:38, Sasa Vilic wrote:
   > Hello,
   > 
   > Please excuse my ignorance, because I might be missing something 
   > obvious, but how is this not a bug?
   > 
   > I simply open 2 terminals and connect to the same database.
   > 
   > **TERMINAL 1:**
   > 
   > sasa=# create table tst1(id int primary key);
   > CREATE TABLE
   > sasa=# insert into tst1(id) values(1);
   > INSERT 0 1
   > sasa=# begin;
   > BEGIN
   > sasa=# select * from tst1 where id = 1;
   >   id
   > ----
   >    1
   > (1 row)
   > 
   > **TERMINAL 2:**
   > 
   > sasa=# begin;
   > BEGIN
   > sasa=# select * from tst1 where id = 1;
   >   id
   > ----
   >    1
   > (1 row)
   > 
   > **TERMINAL 1:**
   > 
   > sasa=# delete from tst1 where id = 1;
   > DELETE 1
   > sasa=# insert into tst1(id) values(1);
   > INSERT 0 1
   > 
   > **TERMINAL 2:**
   > 
   > sasa=# delete from tst1 where id = 1;
   > // client is blocked due to row lock/index lock
   > 
   > **TERMINAL 1:**
   > 
   > sasa=# commit;
   > COMMIT
   > 
   > **TERMINAL 2:**
   > // resumes because terminal 1 committed transaction
   > DELETE 0
   > sasa=# insert into tst1(id) values(1);
   > ERROR:  duplicate key value violates unique constraint "tst1_pkey"
   > DETAIL:  Key (id)=(1) already exists.
   > 
   > To my understanding, the second client (terminal 2) should already see 
   > changes from the first client, because the transaction isolation level 
   > is READ COMMITTED and the first client did commit its transaction.
   
    From here:
   
   https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED
   
   13.2.1. Read Committed Isolation Level
   
   
   "UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave 
   the same as SELECT in terms of searching for target rows: they will only 
   find target rows that were committed as of the command start time. 
   However, such a target row might have already been updated (or deleted 
   or locked) by another concurrent transaction by the time it is found. In 
   this case, the would-be updater will wait for the first updating 
   transaction to commit or roll back (if it is still in progress). If the 
   first updater rolls back, then its effects are negated and the second 
   updater can proceed with updating the originally found row. If the first 
   updater commits, the second updater will ignore the row if the first 
   updater deleted it, otherwise it will attempt to apply its operation to 
   the updated version of the row. The search condition of the command (the 
   WHERE clause) is re-evaluated to see if the updated version of the row 
   still matches the search condition. If so, the second updater proceeds 
   with its operation using the updated version of the row. In the case of 
   SELECT FOR UPDATE and SELECT FOR SHARE, this means it is the updated 
   version of the row that is locked and returned to the client."
   
   The:
   
   delete from tst1 where id = 1;
   
   started before you did the COMMIT in terminal 1 so the DELETE query ends 
   up doing:
   
   "[...]  If the first updater commits, the second updater will ignore the 
   row if the first updater deleted it, [...]"
   
   Hence the DELETE 0 and the PK violation because the INSERT in terminal 1 
   already created a row with id=1.
   
   
   > 
   > Thanks in advance!
   > 
   > Kind Regards
   > Sasa Vilic
   > 
   > 
   > 
   
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com