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

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