Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On 01/10/2014 10:00 PM, Peter Geoghegan wrote:
> On Fri, Jan 10, 2014 at 11:28 AM, Heikki Linnakangas
> <hlinnakangas@vmware.com> wrote:
>> Why does it deadlock with the btreelock patch? I don't see why it should. If
>> you have two backends inserting a single tuple, and they conflict, one of
>> them should succeed to insert, and the other one should update.
>
> Are you sure that it doesn't make your patch deadlock too, with enough
> pressure? I've made that mistake myself.
>
> That test-case made my patch deadlock (in a detected fashion) when it
> used buffer locks as a value locking prototype - I say as much right
> there in the November mail you linked to. I think that's acceptable,
> because it's non-sensible use of the feature (my point was only that
> it shouldn't livelock). The test case is naively locking a row without
> knowing ahead of time (or pro-actively checking) if the conflict is on
> the first or second unique index. So before too long, you're updating
> the "wrong" row (no existing lock is really held), based on the 'a'
> column's projected value, when in actuality the conflict was on the
> 'b' column's projected value. Conditions are right for deadlock,
> because two rows are locked, not one.

I see. Yeah, I also get deadlocks when I change update statement to use 
"foo.b = rej.b" instead of "foo.a = rej.a". I think it's down to the 
indexes are processed, ie. which conflict you see first.

This is pretty much the same issue we discussed wrt. exclusion 
contraints. If the tuple being inserted conflicts with several existing 
tuples, what to do? I think the best answer would be to return and lock 
them all. It could still deadlock, but it's nevertheless less surprising 
behavior than returning one of the tuples in random. Actually, we could 
even avoid the deadlock by always locking the tuples in a certain order, 
although I'm not sure if it's worth the trouble.

- Heikki