BUG #16072: Two transaction to delete all data, The result is not hopeful

Hello,

Le mer. 23 oct. 2019 à 08:29, PG Bug reporting form <noreply@postgresql.org>
a écrit :

The following bug has been logged on the website:

Bug reference: 16072
Logged by: qiangwei zhu
Email address: zhuqiangwei010@hotmail.com
PostgreSQL version: 10.5
Operating system: &quot;PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled
Description:

my test table struct is:
create table test(
f1 int
)

test table data is:
f1
---------------
1
1
1
1
1

I open two query analyzer,
trans A:

begin TRANSACTION;
delete from test;
INSERT into test(f1)
values(1)

trans B:
begin TRANSACTION;
delete from test;

then commit transA, last commit transB

why in test table has 1 rows at last?
sqlserver 、oracle、mysql database，use same test solution, there was no data
in test table at last.

I suppose you're in the isolation level "read commited" as it's the
default. With this level, the database snapshot is made when the query
starts its execution. When the DELETE started its execution on transaction
B, it didn't know about the value inserted during transaction A as the
latter wasn't commited. The DELETE was waiting for a lock on the rows to
delete, but the database snapshot already happened. So, when it finally got
the locks, it only tried to delete rows existing before the beginning of
transaction A, hence without the latest inserted row.

Doesn't look like a bug to me. Seems to be working as intended.

--
Guillaume.