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

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(at)163(dot)com> wrote:

At 2015-10-25 23:38:23, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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.
​