Обсуждение: BUG #16072: Two transaction to delete all data, The result is not hopeful

Поиск
Список
Период
Сортировка

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

От
PG Bug reporting form
Дата:
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:   "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.

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

От
Guillaume Lelarge
Дата:
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.