Re: Serializable implementation

On Mon, Dec 28, 2009 at 11:20 PM, Florian G. Pflug <fgp(at)phlo(dot)org> wrote:
> I believe the hard part of implementing true serializability is not the
> actual SSI or S2PL algorithm, but rather the necessary predicate locking
> strategy.
>
> So I think checking how InnoDB tackles that and how much of it's code is
> invovled might give a more realistic estimate of the effort required.

We've been over this a few times. The reason MySQL and Sybase/MSSQL
can tackle this is because they have very few access paths which are
closely tied with their index structures. Postgres supports many
different access paths and indexes are plugin-replacable modules with
a limited interface which can't specify or make detailed assumptions
about the pattern in which data is accessed.

If you read a record in a query then where would you place the
annotation to prevent me from performing a HOT update against that
record? How would you stop me from deleting that same record using an
access plan that doesn't use the same index as you used to find it? If
you scan a partial index where would you put the lock to prevent me
from updating one of those records to no longer fit the partial index
where clause? What if you do a reverse index scan instead of a forward
scan? If you scan the table for the one or two records matching
several indexes using bitmap index scans where would you put the
locks? In GIN, GIST, and hash indexes are you sure there's any
reasonable place to put the locks, especially given GIN's buffered
updates?

This is the hard part. Talking about the details of the conflict
resolution algorithms and the benefits of serializable mode to your
development methodology is all fantasy as long as you don't have any
approaches to solve actually being able to detect the conflicts in the
first place without rewriting many parts and large parts of the
system.

--
greg