Thread: Concurrent delete and insert on same key, potentially a bug

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
transactionisolation 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

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