Re: Serializable Isolation without blocking

aaah... I think I see where we've gone off track in previous
discussions...you think postgres keeps row level locks in a shared
memory data structure. It doesn't it stores all row level locks *in*
the tuple itself. It only stores the lock in memory briefly while
actually acquiring the lock. Once it acquires it the only record of
the lock is the xid in the tuple itself.

This means there are no memory limits on the number of records locked
by a transaction.

storing the lock data in the tuples won't work for you at all because
you need to lock rows that don't exist yet at all.that's why "where to
store the lock" is a critical blocking issue to figure out to know
whether the plan is feasible at all.

On Thursday, December 31, 2009, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Robert Haas  wrote:
>
>> It seems to me that the hard part of this problem is to describe
>> the general mechanism by which conflicts will be detected, with
>> specific references to the types of data structures that will be
>> used to hold that information.
>
> Well, the general approach to tracking SIREAD locks I had in mind is
> to keep them in the existing lock data structures.  I have in mind to
> use multiple granularities, with automatic escalation to coarser
> granularities at thresholds, to keep RAM usage reasonable.  There are
> clearly some tough problems with the pluggable indexes, types,
> operators, and such that will take time to sort out an acceptable
> implementation at any fine-grained level, so my intent it to punt
> those to very coarse granularity in the first pass, with "XXX SIREAD
> optimization opportunity" comments where that's not a production-
> quality solution or it just seems likely that we can do better with
> some work.
>
> I didn't want to get too detailed before I checked that creating a
> new lock method for this seemed sane, since the details of the
> implementation depend on that choice.  Lack of detail tends to draw
> accusations of hand-waving, so I was trying to stay away from those
> details until my intuition on the lock method was confirmed or shot
> down, so I could solidify those details before presenting them.
> There is a bit of a chicken and egg problem with moving this forward
> -- I guess I was overly conservative on what I presented.
>
> I do understand that this does mean that more RAM will need to be
> allocated to the lock structures to support serializable mode.  I
> don't think that any other option is likely to provide acceptable
> performance.  I also realize that this means that in the final form,
> optimized to where my shop considers it usable, there will still be
> coarser granularity than theoretically possible and resulting false
> positives causing serialization failures for which the cause is
> obscure.  We don't care, and anyone who does will probably not want
> to use this isolation level.  Traditional S2PL doesn't have that
> fault, but it blocks so badly that performance is worse; I'll take
> the transaction restarts over that any day.  I know there are others
> who won't.
>
> Basically, the reasons given for having separate lock methods for
> DEFAULT ("normal") locks and USER locks seem to apply with almost as
> much force to SIREAD locks (no blocking between them, different
> source of setting, different lifespans), so I was pretty sure this
> was a sane choice, but I just wanted a quick reality check before
> developing the level of detail that would move this past hand-waving.
>
> Other than the SIREAD locks to cover predicate locking for
> serializable transactions, there is no change to what locks are
> acquired.  There is no change to blocking or deadlock detection and
> recovery.  Other transaction isolation levels do not need to change,
> except perhaps to fast-path a skip over blocking and deadlock
> checking against SIREAD locks (one of those details I'm looking at).
>
> Let me know if you need more information to firm up an opinion on the
> sanity of my intuition regarding the new lock method; I'm eager to
> move on to the next level of detail.
>
> And thanks for the feedback.  :-)
>
> -Kevin
>

--
greg