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

On Fri, Jan 10, 2014 at 4:09 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> Well, the usual example for exclusion constraints is resource scheduling
> (ie: scheduling what room a class will be held in). In that context is it
> hard to believe that you might want to MERGE a set of new classroom
> assignments in?

So you schedule a class that clashes with 3 other classes, and you
want to update all 3 rows/classes with details from your one row
proposed for insertion? That makes no sense, unless the classes were
in fixed time slots, in which case you could use a unique constraint
to begin with. You can't change the rows to have the same time range
for all 3. So you have to delete two first, and update the range of
one. Which two? And you can't really rely on having locked existing
rows operating as a kind of "persistent value lock", as I do, because
you've locked a row with a different range to the one you care about -
someone can still insert another row that doesn't block on that one
but blocks on your range. So you really do need a sophisticated, fully
formed value locking infrastructure to make it work, for a feature of
marginal utility at best. I'm having a hard time imagining any user
actually wanting to do any of this, and I'm having a harder time still
imagining anyone putting in the work to make it possible, if indeed it
is possible.

No one has ever implemented fully formed predicate locking in a
commercial database system, because it's an NP-complete problem [1],
[2]. Only very limited special cases are practicable, and I'm pretty
sure this isn't one of them.

[1] http://library.riphah.edu.pk/acm/disk_1/text/1-2/SIGMOD79/P127.PDF

[2] http://books.google.com/books?id=wV5Ran71zNoC&pg=PA284&lpg=PA284&dq=predicate+locking+np+complete&source=bl&ots=PgNJ5H3L8V&sig=fOZ2Wr4fIxj0eFQD0tCGPLTsfY0&hl=en&sa=X&ei=PpTQUquoBMfFsATtw4CADA&ved=0CDIQ6AEwAQ#v=onepage&q=predicate%20locking%20np%20complete&f=false
--
Peter Geoghegan