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@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 en=
d
> >up with a timing in which both clients are trying to do the DELETE.  Onl=
y
> >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=3D1.
> >
> >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 "F=
OR 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 b=
e 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 de=
leted 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.
=E2=80=8B