BUG #13723: "duplicate key" error occurs when update delete and insert the same row concurrently

On Sunday, October 25, 2015, <chjischj@163.com> wrote:

The following bug has been logged on the website:

Bug reference: 13723
Logged by: ChenHuajun
Email address: chjischj@163.com <javascript:;>
PostgreSQL version: 9.4.5
Operating system: CentOS 6
Description:

When i run sysbench's complex test with PostgreSQL, the following error
always occured.
duplicate key value violates unique constraint "%s"

It seems to be a bug which occurs when executing update,delete and
insert(within one transaction) the same row concurrently.

While I probably cannot explain this adequately it is your understanding of
concurrency and the various transaction isolation levels that is wrong; not
PostgreSQL.

David J.

chjischj@163.com writes:

When i run sysbench's complex test with PostgreSQL, the following error
always occured.
duplicate key value violates unique constraint "%s"

It seems to be a bug which occurs when executing update,delete and
insert(within one transaction) the same row concurrently.

I see no bug here; you're just making a mistaken assumption about how
cross-transaction serialization works. At some point you're going to end
up with a timing in which both clients are trying to do the DELETE. Only
one does it; the other waits for that row change to commit, sees it's
done, and concludes that there's nothing for it to do. (In particular,
it will not see the row that was inserted later in the other transaction,
because that's too new.) Now the second one's INSERT fails because
there's already a row with id=1.

If you want this sort of coding to execute stably, you could consider
taking out a table-level lock, or some other way of preventing clients
from concurrently deleting+inserting the same key. Or, just don't do
that in the first place.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

At 2015-10-25 23:38:23, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

I see no bug here; you're just making a mistaken assumption about how
cross-transaction serialization works. At some point you're going to end
up with a timing in which both clients are trying to do the DELETE. Only
one does it; the other waits for that row change to commit, sees it's
done, and concludes that there's nothing for it to do. (In particular,
it will not see the row that was inserted later in the other transaction,
because that's too new.) Now the second one's INSERT fails because
there's already a row with id=1.

If you want this sort of coding to execute stably, you could consider
taking out a table-level lock, or some other way of preventing clients
from concurrently deleting+inserting the same key. Or, just don't do
that in the first place.

regards, tom lane

In my opinion, the first update sql in the transaction should obtain a "FOR NO KEY UPDATE" Row-level Lock,
And "FOR NO KEY UPDATE" Row-level Lock is conflicting with each other,
So, the rest two sqls(delete and insert) in the two transactions should be executed sequentially instead of simultaneously.

http://www.postgresql.org/docs/9.4/static/explicit-locking.html#ROW-LOCK-COMPATIBILITY
--------------------------------------------------
FOR UPDATE
FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends.
...

FOR NO KEY UPDATE
...
This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.
--------------------------------------------------

Is my understand wrong?

BTW:the similar problem(deadlock)
when i removed the primary key constraint from tb1 and run the above test again,the error became "deadlock".
postgres=# \d tb1
Table "public.tb1"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
c | integer |

The error log:
------------------------------
2015-10-25 18:50:09.678 EDT 57676 0 LOG: statement: begin;
2015-10-25 18:50:09.678 EDT 57676 0 LOG: statement: update tb1 set c=2 where id=1
2015-10-25 18:50:09.679 EDT 57677 0 LOG: statement: begin;
2015-10-25 18:50:09.679 EDT 57677 0 LOG: statement: update tb1 set c=2 where id=1
2015-10-25 18:50:09.679 EDT 57676 39855 LOG: statement: delete from tb1 where id=1;
2015-10-25 18:50:10.680 EDT 57677 39856 ERROR: deadlock detected
2015-10-25 18:50:10.680 EDT 57677 39856 DETAIL: Process 57677 waits for ShareLock on transaction 39855; blocked by process 57676.
Process 57676 waits for ShareLock on transaction 39856; blocked by process 57677.
Process 57677: update tb1 set c=2 where id=1
Process 57676: delete from tb1 where id=1;
2015-10-25 18:50:10.680 EDT 57677 39856 HINT: See server log for query details.
2015-10-25 18:50:10.680 EDT 57677 39856 CONTEXT: while updating tuple (0,205) in relation "tb1"
2015-10-25 18:50:10.680 EDT 57677 39856 STATEMENT: update tb1 set c=2 where id=1
2015-10-25 18:50:10.680 EDT 57676 39855 LOG: statement: insert into tb1 values(1,2);
2015-10-25 18:50:10.681 EDT 57676 39855 LOG: statement: commit;

And if separated deleting and inserting to two transactions, "deadlock" error still ocurrs.
[postgres@localhost ~]$ cat test3.sql
begin;
update tb1 set c=2 where id=1
delete from tb1 where id=1;
commit;
insert into tb1 values(1,2);
[postgres@localhost ~]$ pgbench -n -f test3.sql -c 2 -j 2 -t 200
client 0 aborted in state 1: ERROR: deadlock detected
DETAIL: Process 58002 waits for ShareLock on transaction 72640; blocked by process 58003.
Process 58003 waits for ShareLock on transaction 72641; blocked by process 58002.
HINT: See server log for query details.
CONTEXT: while updating tuple (7,4) in relation "tb1"
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 2
number of transactions per client: 200
number of transactions actually processed: 203/400
latency average: 0.000 ms
tps = 128.549274 (including connections establishing)
tps = 128.917578 (excluding connections establishing)

Best Regards,
Chen Huajun

On Sun, Oct 25, 2015 at 1:28 PM, chenhj <chjischj@163.com> wrote:

At 2015-10-25 23:38:23, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

I see no bug here; you're just making a mistaken assumption about how
cross-transaction serialization works. At some point you're going to end
up with a timing in which both clients are trying to do the DELETE. Only
one does it; the other waits for that row change to commit, sees it's
done, and concludes that there's nothing for it to do. (In particular,
it will not see the row that was inserted later in the other transaction,
because that's too new.) Now the second one's INSERT fails because
there's already a row with id=1.

If you want this sort of coding to execute stably, you could consider
taking out a table-level lock, or some other way of preventing clients
from concurrently deleting+inserting the same key. Or, just don't do
that in the first place.

regards, tom lane

In my opinion, the first update sql in the transaction should obtain a "FOR NO KEY UPDATE" Row-level Lock,
And "FOR NO KEY UPDATE" Row-level Lock is conflicting with each other,
So, the rest two sqls(delete and insert) in the two transactions should be executed sequentially instead of simultaneously.

http://www.postgresql.org/docs/9.4/static/explicit-locking.html#ROW-LOCK-COMPATIBILITY
--------------------------------------------------
FOR UPDATE
FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends.
...

FOR NO KEY UPDATE
...
This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.
--------------------------------------------------

Is my understand wrong?

Yes.

Those locks you refer to are "EXPLICIT" locks. If you want to take one you
have to write it into your query.

David J.
​

Does not the following statements in manual means any UPDATE should automatically acquire ether a FOR UPDATE or a FOR NO KEY UPDATE Row-level Lock?

http://www.postgresql.org/docs/9.4/static/explicit-locking.html#LOCKING-ROWS

-----------------------------------------------
In addition to table-level locks,there are row-level locks,which are listed as below with the contexts in which they are used automatically by PostgreSQL....

Row-level Lock Modes

FOR UPDATE

...

The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values on certain columns. ...

...

FOR NO KEY UPDATE

...

This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.

-----------------------------------------------

Best Regarts,
Chen Huajun

On 2015-10-26 05:37 , David G. Johnston Wrote:

On Sun, Oct 25, 2015 at 1:28 PM, chenhj <chjischj@163.com> wrote:

At 2015-10-25 23:38:23, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

I see no bug here; you're just making a mistaken assumption about how
cross-transaction serialization works. At some point you're going to end
up with a timing in which both clients are trying to do the DELETE. Only
one does it; the other waits for that row change to commit, sees it's
done, and concludes that there's nothing for it to do. (In particular,
it will not see the row that was inserted later in the other transaction,
because that's too new.) Now the second one's INSERT fails because
there's already a row with id=1.

If you want this sort of coding to execute stably, you could consider
taking out a table-level lock, or some other way of preventing clients
from concurrently deleting+inserting the same key. Or, just don't do
that in the first place.

regards, tom lane

In my opinion, the first update sql in the transaction should obtain a "FOR NO KEY UPDATE" Row-level Lock,
And "FOR NO KEY UPDATE" Row-level Lock is conflicting with each other,
So, the rest two sqls(delete and insert) in the two transactions should be executed sequentially instead of simultaneously.

http://www.postgresql.org/docs/9.4/static/explicit-locking.html#ROW-LOCK-COMPATIBILITY
--------------------------------------------------
FOR UPDATE
FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends.
...

FOR NO KEY UPDATE
...
This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.
--------------------------------------------------

Is my understand wrong?
Yes.

Those locks you refer to are "EXPLICIT" locks. If you want to take one you have to write it into your query.

David J.
​

On Sun, Oct 25, 2015 at 10:28 AM, chenhj <chjischj@163.com> wrote:

At 2015-10-25 23:38:23, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

I see no bug here; you're just making a mistaken assumption about how
cross-transaction serialization works. At some point you're going to end
up with a timing in which both clients are trying to do the DELETE. Only
one does it; the other waits for that row change to commit, sees it's
done, and concludes that there's nothing for it to do. (In particular,
it will not see the row that was inserted later in the other transaction,
because that's too new.) Now the second one's INSERT fails because
there's already a row with id=1.

If you want this sort of coding to execute stably, you could consider
taking out a table-level lock, or some other way of preventing clients
from concurrently deleting+inserting the same key. Or, just don't do
that in the first place.

regards, tom lane

In my opinion, the first update sql in the transaction should obtain a

"FOR

NO KEY UPDATE" Row-level Lock,
And "FOR NO KEY UPDATE" Row-level Lock is conflicting with each other,
So, the rest two sqls(delete and insert) in the two transactions should be
executed sequentially instead of simultaneously.

That is true only if the UPDATE actually updates the row. If you log the
query completion tags, I think you will
find the error is preceded by an UPDATE of zero rows. Now you could argue
that this itself is a violation: that if a single statement sees that a row
was deleted it is obliged to also see the row that was inserted in the same
transaction as the deletion. But this is documented, and is unlikely to
change.

From http://www.postgresql.org/docs/current/static/transaction-iso.html

Because of the above rule, it is possible for an updating command to see an

inconsistent snapshot: it can see the effects of concurrent updating
commands on the same rows it is trying to update, but it does not see
effects of those commands on other rows in the database. This behavior
makes Read Committed mode unsuitable for commands that involve complex
search conditions;

Can you reproduce the problem under a higher transaction isolation level?

Cheers,

Jeff

Can you reproduce the problem under a higher transaction isolation level?

Cheers,

Jeff

Under higher transaction isolation level(REPEATABLE READ or SERIALIZABLE),
only one transaction's UPDATE could be executed, the second transaction's UPDATE will be blocked util the first transaction committed and then throw "could not serialize access due to concurrent update" error.

[postgres@localhost ~]$ cat test5.sql
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
update tb1 set c=2 where id=1
delete from tb1 where id=1;
insert into tb1 values(1,2);
commit;
[postgres@localhost ~]$ pgbench -n -c 2 -j 2 -t 2 -f test5.sql
client 0 aborted in state 1: ERROR: could not serialize access due to concurrent update
...
---------------------------
2015-10-25 22:55:25.717 EDT 2203 0 LOG: connection received: host=[local]
2015-10-25 22:55:25.718 EDT 2203 0 LOG: connection authorized: user=postgres database=postgres
2015-10-25 22:55:25.722 EDT 2205 0 LOG: connection received: host=[local]
2015-10-25 22:55:25.723 EDT 2206 0 LOG: connection received: host=[local]
2015-10-25 22:55:25.723 EDT 2205 0 LOG: connection authorized: user=postgres database=postgres
2015-10-25 22:55:25.724 EDT 2206 0 LOG: connection authorized: user=postgres database=postgres
2015-10-25 22:55:25.726 EDT 2205 0 LOG: statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2015-10-25 22:55:25.726 EDT 2206 0 LOG: statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2015-10-25 22:55:25.726 EDT 2206 0 LOG: statement: update tb1 set c=2 where id=1
2015-10-25 22:55:25.727 EDT 2205 0 LOG: statement: update tb1 set c=2 where id=1
2015-10-25 22:55:25.728 EDT 2206 79520 LOG: statement: delete from tb1 where id=1;
2015-10-25 22:55:25.728 EDT 2206 79520 LOG: statement: insert into tb1 values(1,2);
2015-10-25 22:55:25.728 EDT 2206 79520 LOG: statement: commit;
2015-10-25 22:55:25.729 EDT 2206 0 LOG: statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2015-10-25 22:55:25.729 EDT 2205 79521 ERROR: could not serialize access due to concurrent update
2015-10-25 22:55:25.729 EDT 2205 79521 STATEMENT: update tb1 set c=2 where id=1
2015-10-25 22:55:25.730 EDT 2206 0 LOG: statement: update tb1 set c=2 where id=1
2015-10-25 22:55:25.730 EDT 2206 79522 LOG: statement: delete from tb1 where id=1;
2015-10-25 22:55:25.730 EDT 2206 79522 LOG: statement: insert into tb1 values(1,2);
2015-10-25 22:55:25.730 EDT 2206 79522 LOG: statement: commit;

Best Regards,
Chen Huajun

On Mon, Oct 26, 2015 at 10:47 AM, chenhj <chjischj@163.com> wrote:

Can you reproduce the problem under a higher transaction isolation level?

Cheers,

Jeff

Under higher transaction isolation level(REPEATABLE READ or SERIALIZABLE),
only one transaction's UPDATE could be executed, the second transaction's
UPDATE will be blocked util the first transaction committed and then
throw "could not serialize access due to concurrent update" error.

Yes, this is the correct behavior. It can't serialize, so it gives an
error which the user can then decide how to handle (simply retry the same
transaction would probably be best, in this case).

It would be nice if pgbench had a mode where it would do the retry for you
upon retryable errors, but right now it doesn't.

Cheers,

Jeff

Hi,Jeff

That is true only if the UPDATE actually updates the row. If you log the query completion tags, I think you will
find the error is preceded by an UPDATE of zero rows. Now you could argue that this itself is a violation:
that if a single statement sees that a row was deleted it is obliged to also see the row that was inserted
in the same transaction as the deletion. But this is documented, and is unlikely to change.

My test result is just as you said, as the following:

test9.sql:
---------------------------------------
begin;
select * from tb1 where id=1;
update tb1 set c=2 where id=1;
delete from tb1 where id=1;
insert into tb1 values(1,2);
commit;
begin;
select * from tb1 where id=1;
update tb1 set c=2 where id=1;
delete from tb1 where id=1;
insert into tb1 values(1,2);
commit;
...
---------------------------------------

[postgres@localhost ~]$ psql -f test9.sql >a1.log 2>&1 &
[postgres@localhost ~]$ psql -f test9.sql >a2.log 2>&1 &

a2.log:
---------------------------------------
...
BEGIN
id | c
----+---
1 | 2
(1 row)

UPDATE 0
DELETE 0
psql:test9.sql:17: ERROR: duplicate key value violates unique constraint "tb1_pkey"
DETAIL: Key (id)=(1) already exists.
ROLLBACK
...
---------------------------------------

Even "EXPLICITLY" add "select ... for update" at first, "duplicate key " error still ocurrs.

test10.sql:
---------------------------------------
begin;
select * from tb1 where id=1 for update;
update tb1 set c=2 where id=1;
delete from tb1 where id=1;
insert into tb1 values(1,2);
commit;
begin;
select * from tb1 where id=1 for update;
update tb1 set c=2 where id=1;
delete from tb1 where id=1;
insert into tb1 values(1,2);
commit;
...
---------------------------------------

[postgres@localhost ~]$ psql -f test10.sql >b1.log 2>&1 &
[postgres@localhost ~]$ psql -f test10.sql >b2.log 2>&1 &

b1.log:
---------------------------------------
...
BEGIN
id | c
----+---
(0 rows)

UPDATE 0
DELETE 0
psql:test10.sql:29: ERROR: duplicate key value violates unique constraint "tb1_pkey"
DETAIL: Key (id)=(1) already exists.
ROLLBACK
...
---------------------------------------

So, "select * from tb1 where id=1 for update" also could see an inconsistent snapshot (sort of "Dirty Read").

Best Regard,
Chen Huajun