Re: User-facing aspects of serializable transactions - Mailing list pgsql-hackers

Greg Stark <stark@enterprisedb.com> wrote: 
> On Mon, Jun 1, 2009 at 9:24 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>>> I'm concerned with whether you can be sure that the 999th time you
>>> run it the database won't randomly decide to declare a
>>> serialization failure for reasons you couldn't predict were
>>> possible.
>>
>> Now you're questioning whether SERIALIZABLE transaction isolation
>> level is useful.  Probably not for everyone, but definitely for
>> some.
> 
> No, I'm not. I'm questioning whether a serializable transaction
> isolation level that makes no guarantee that it won't fire
> spuriously is useful.
Well, the technique I'm advocating virtually guarantees that there
will be false positives, since it looks only for the "dangerous
structure" of two adjacent read-write dependences rather than building
a rigorous read-write dependency graph for every serializable
transaction.  Even if you user very fine-grained locks (i.e., what
*columns* were modified in what rows) and had totally accurate
predicate locking, you would still get spurious rollbacks with this
technique.
In spite of that, I believe that it will run faster than traditional
serializable transactions, and in one benchmark it ran faster than
snapshot isolation -- apparently because it rolled back conflicting
transactions before they did updates and hit the update conflict
detection phase.
> Postgres doesn't take block level locks or table level locks to do
> row-level operations. You can write code and know that it's safe
> from deadlocks.
Who's talking about deadlocks?  If you're speaking more broadly of all
serialization failures, you can certainly get them in PostgreSQL.  So
one of us is not understanding the other here.  To clarify what I'm
talking about -- this technique introduces no blocking and cannot
cause a deadlock.
> Heikki proposed a list of requirements which included a requirement
> that you not get spurious serialization failures and you rejected
> that on the basis that that's not how MSSQL and Sybase implement it.
No, I rejected that on the basis that it precludes the use of the
technique published in the paper I cited, and I believe that technique
is the best currently available.  I'm perfectly happy to get to a
point where we have something which works correctly and have people
try to make it work better by tweaking the locking, but I think that
we'll find a point of diminishing returns -- where the cost of
tracking finer locks costs more than the cost of rerunning some
transactions.  For obvious high-risk situations, where you are
expending extreme resources on one database transaction, I believe it
will be most cost-effective to count on developers to recognize the
risk and use existing techniques.
> I'm unhappy with the idea that if I access too many rows or my query
> conditions aren't written just so then the database will forget
> which rows I'm actually concerned with and "lock" other random
> unrelated records and possibly roll my transaction back even though
> my I had no way of knowing my code was at risk.
Then you would apparently not be a good candidate for serializable
transactions, since I don't know of any implementation which performs
well which doesn't have those characteristics.  When Sybase introduced
row level locking, we benchmarked that against the page level locking,
and found that it was significantly slower for our mix.  We did
identify a small number of small tables with high update rates where
switching them to row level locking provided a small performance gain.
-Kevin