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

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.
​