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

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(dot)klaver(at)aklaver(dot)com