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

On Sun, Oct 25, 2015 at 10:28 AM, 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.

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