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

On Fri, Jan 10, 2014 at 1:25 PM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> 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.

I understand and accept that as long as we're intent on locking more
than one row per transaction, that action could deadlock with another
session doing something similar. Actually, I've even encountered
people giving advice in relation to proprietary systems along the
lines of: "if your big SQL MERGE statement is deadlocking excessively,
you might try hinting to make sure a nested loop join is used". I
think that this kind of ugly compromise is unavoidable in those
scenarios (in reality the most popular strategy is probably "cross
your fingers"). But as everyone agrees, the common case where an xact
only upserts one row should never deadlock with another, similar xact.
So *that* isn't a problem I have with making row locking work for
exclusion constraints.

My problem is that in general I'm not sold on the actual utility of
making this kind of row locking work with exclusion constraints. I'm
sincerely having a hard time thinking of a practical use-case
(although, as I've said, I want to make it work with IGNORE). Even if
you work all this row locking stuff out, and the spill-to-disk aspect
out, the interface is still wrong, because you need to figure out a
way to project more than one reject per slot. Maybe I lack imagination
around how to make that work, but there are a lot of "ifs" and "buts"
either way.

--
Peter Geoghegan