Re: Promise index tuples for UPSERT

On Tue, 2014-10-07 at 13:33 +0100, Simon Riggs wrote:
> Is there a way of detecting that we are updating a unique constraint
> column and then applying the HW locking only in that case? Or can we
> only apply locking when we have multiple unique constraints on a
> table?

What is the use case of doing an UPSERT into a table with multiple
unique constraints?

Consider table user with unique columns name and email and a non-unique
column age. If it has data

Jack | jack(at)example(dot)com |33
Tom | tom(at)example(dot)com | 35

And the user does UPSERT values (Jack, tom(at)example(dot)com, 34). The
proposed specification will pick random unique index (either name or
email index) and do an update of that row.

First, this will cause unique violation, second, doing the UPSERT on
random index seems confusing.

The MySQL documentation says that "you should try to avoid using an ON
DUPLICATE KEY UPDATE clause on tables with multiple unique indexes"[1].
The proposed feature's documentation has the same suggestion[2]. Still,
the feature defaults to this behavior. Why is the default something the
documentation says you shouldn't do?

Going a bit further, I am wondering what is the use case of doing an
UPSERT against multiple unique indexes? If multiple unique indexes
UPSERT could be dropped that might allow for faster or cleaner index
locking techniques.

- Anssi

1: http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html
2: http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-insert.html