Concurrent delete and insert on same key, potentially a bug
От | Sasa Vilic |
---|---|
Тема | Concurrent delete and insert on same key, potentially a bug |
Дата | |
Msg-id | CAOJhpYd_K6EdTs_3LAa0RMna59cC256cZerXAE9nfBKVDTFGjQ@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Concurrent delete and insert on same key, potentially a bug
Re: Concurrent delete and insert on same key, potentially a bug |
Список | pgsql-general |
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:**
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)
id
----
1
(1 row)
**TERMINAL 2:**
sasa=# begin;
BEGIN
sasa=# select * from tst1 where id = 1;
id
----
1
(1 row)
BEGIN
sasa=# select * from tst1 where id = 1;
id
----
1
(1 row)
**TERMINAL 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
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.
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.
Thanks in advance!
Kind Regards
Sasa Vilic
В списке pgsql-general по дате отправления: