Re: Serializable Isolation without blocking

Robert Haas wrote:

> What predicate locking? If you take ACCESS EXCLUSIVE locks on every
> read, that should serialize all access to every table. Predicate
> locking wouldn't do anything, because the table would be completely
> inaccessible to all competing transactions.

Yeah, that's the benefit of starting with the ACCESS EXCLUSIVE locks,
but once I've confirmed that I've found all the places to get the
table level locks, the next step is to turn them into table level
SIREAD locks, and then to implement the SSI. Locking against
referenced objects is the only practical technique for implementing
predicate locking for production environments that I've seen.

The phase where I'm making each referenced table totally inaccessible
to all competing transaction should be pretty short-lived. It just
gives me an interim milestone to test that piece in isolation before
going on to use it; which is great, but not a place to stop for long.

Or have I totally misunderstood your suggestion?

-Kevin

On Thu, Dec 31, 2009 at 7:45 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Robert Haas  wrote:
>
>> What predicate locking? If you take ACCESS EXCLUSIVE locks on every
>> read, that should serialize all access to every table. Predicate
>> locking wouldn't do anything, because the table would be completely
>> inaccessible to all competing transactions.
>
> Yeah, that's the benefit of starting with the ACCESS EXCLUSIVE locks,
> but once I've confirmed that I've found all the places to get the
> table level locks, the next step is to turn them into table level
> SIREAD locks, and then to implement the SSI.  Locking against
> referenced objects is the only practical technique for implementing
> predicate locking for production environments that I've seen.
>
> The phase where I'm making each referenced table totally inaccessible
> to all competing transaction should be pretty short-lived.  It just
> gives me an interim milestone to test that piece in isolation before
> going on to use it; which is great, but not a place to stop for long.
>
> Or have I totally misunderstood your suggestion?

Nope, you're on target. Although - if I were you - I would post the
ACCESS EXCLUSIVE lock version of the patch for feedback. I can't
speak for anyone else, but I'll read it.

(Just clearly label it as what it is, of course.)

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> Nope, you're on target. Although - if I were you - I would post
> the ACCESS EXCLUSIVE lock version of the patch for feedback. I
> can't speak for anyone else, but I'll read it.

Here you go! :-)

This is the milestone of having full serializable behavior, albeit
with horrible performance, using the simplest implementation
possible. I didn't use ACCESS EXCLUSIVE locks, because on review it
seemed to me that a SHARE lock would be strong enough. It compiles
and passes the regression tests, and I've been testing some of the
scenarios previously used to show the snapshot anomalies; I now get
correct behavior through blocking.

I identified the points to insert predicate locking by looking for
places where ExecStoreTuple was called with a valid heap buffer; if
there is anywhere that obtains tuples from the heap without going
through that method, I have more work to do. If anyone knows of
such locations, I'd be grateful for a "heads up".

If I've done anything horribly wrong in organizing the code, that'd
be nice to hear about before I go too much farther, too.

I'm definitely not looking for this to be committed, but should I
add it to the CF page just for a "feedback" review? (I'm OK with
keeping it more ad hoc, especially if it's going to hold up the
beta at all.)

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

> Here you go! :-)

Hmmm... I just got a write skew example to show a snapshot
isolation anomaly, so I've got something wrong still. :-(

Will continue to work on it. Sorry.

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

> This is the milestone of having full serializable behavior, albeit
> with horrible performance, using the simplest implementation
> possible.

A tad too simple, as it turns out. It did it's main job of
providing a simple milestone to identify code organization and lock
points, but I'd have to jigger some things to make S2PL work with
snapshot isolation which aren't needed for SSI. So, for those
keeping score, I'm moving on down the checklist to get to the SSI
implementation, rather than futzing with code which would just be
thrown away.

No need for anyone to test for full serializable behavior.
Comments on technique welcome.

-Kevin