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

On Wed, Oct 9, 2013 at 4:11 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> * The lock starvation hazards around going from value locking to row
> locking, and retrying if it doesn't work out (i.e. if the row and its
> descendant rows cannot be locked without what would ordinarily
> necessitate using EvalPlanQual()). I don't see what we could do about
> those, other than checking for changes in the rows unique index
> values, which would be complex. I understand the temptation to do
> that, but the fact is that that isn't going to work all the time -
> some unique index value may well change every time. By doing that
> you've already accepted whatever hazard may exist, and it becomes a
> question of degree. Which is fine, but I don't see that the current
> degree is actually much of problem in the real world.

Some of the decisions we make here may end up being based on measured
performance rather than theoretical analysis.

> * Reordering of value locks generally. I still need to ensure this
> will behave reasonably at higher isolation levels (i.e. they'll get a
> serialization failure). I think that Robert accepts that this isn't
> inconsistent with read committed's documented behavior, and that it is
> useful, and maybe even essential.

I think there's a sentence missing here, or something. Obviously, the
behavior at higher isolation levels is neither consistent nor
inconsistent with read committed's documented behavior; it's another
issue entirely.

> * The basic question of whether or not it's possible to lock values
> and rows at the same time, and if that matters (because it turns out
> what looks like that isn't, because deleters will effectively lock
> values without even touching an index). I think Robert saw the
> difficulty of doing this, but it would be nice to get a definitive
> answer. I think that any MERGE implementation worth its salt will not
> deadlock without the potential for multiple rows to be locked in an
> inconsistent order, so this shouldn't either, and as I believe I
> demonstrated, value locks and row locks should not be held at the same
> time for at least that reason. Right?

Right.

> * The syntax. I like the composability, and the way it's likely to
> become idiomatic to combine it with wCTEs. Others may not.

I've actually lost track of what syntax you're proposing.

> * The visibility hacks that V4 is likely to have. The fact that
> preserving the composable syntax may imply changes to
> HeapTupleSatisfiesMVCC() so that rows locked but with no currently
> visible version (under conventional rules) are visible to our snapshot
> by virtue of having been locked all the same (this only matters at
> read committed).

I continue to think this is a bad idea.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company