Re: Serializable Isolation without blocking - Mailing list pgsql-hackers

Greg Stark <stark@enterprisedb.com> wrote: 
> Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>> Greg Stark <stark@enterprisedb.com> wrote:
>>
>>> I thought the big problem with providing true serializability was
>>> the predicate locking. If it doesn't address that need then does
>>> this get us any closer?
>>
>> I thought the big problem was the perception that performance would
>> suffer and that the level of blocking required would be
>> unacceptable.
> 
> This thread has really been one of those cases where everyone
> thought they were having a different kind of discussion.
Apparently so.
> If predicate locking is so well understood and if someone who
> understands it and understands what kind of implementation would
> work well in Postgres steps forward with an implementation which
> doesn't cause major downsides then I suspect we might revisit our
> prejudices against it. But as it stands I think the assumption is
> that having to maintain locks on hypothetical records which don't
> exist would be an expensive cost to impose on every query which
> would unduly impact performance.
It would only impact transactions running at the full serializable
isolation level, and I'm guessing that the performance would be
reasonable if an implementation similar to that in DB2, Sybase,
Microsoft SQL Server, etc. is used.  Some here have derided that
approach as crude and implied that only something more aesthetically
pleasing would be considered, but that such implementations would be
prohibitively slow (which, of course, is exactly why they are not used
in these other products).
> I, for one, certainly assumed if we did anything like that it would
> work like our existing locks in that it wouldn't impose any
> additional blocking.
Until this paper, implementation of serializable transactions, even in
an MVCC database required S2PL techniques which caused a lot of
blocking, including readers blocking on writes and vice versa.  The
advance of this paper isn't any novel implementation of predicate
locking, but the reduction of the locks generated by reads to a new
SIREAD level lock which would not introduce any blocking; but instead
would assist in the detection of unsafe patterns of reads and writes
to allow rollbacks to prevent serialization anomalies.
> If there was any question of that then it sounds like this
> paper might be a step forward in that you're on-side at least on
> that question now?
I was never on the other side of that.  I know that some apparently
thought that my attempts to document PostgreSQL's deviation from
current standards in this regard, and to provide more real-world
examples of where people might run into trouble, were really sly
attempts to persuade people to implement full support for serializable
transactions.  That really wasn't the case.
We had been slightly burned by the differences in spite of my having
read the current documentation, because the example given is so
far-fetched and bizarre, that I rather naively thought "Well, if
that's how far out you have to go to hit a problem, the risk is quite
low."  I was trying to find something which gave people a clearer
picture of the issue, so others didn't make the same mistake.  I
wasn't advocating for full serializable support at that point, and
probably would have been reluctant to use it if available because of
the performance issues (assuming a traditional implementation).
In the course of discussing the issue, this paper, published by ACM
earlier in the year, was brought to my attention.  I see it as the
best of both worlds -- MVCC performance with the protections of
serializable transactions.  Back when I first read the paper, though,
it looked to be a struggle to get 8.4 to beta testing, so I sat on it
until now.
-Kevin