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

On Wed, May 27, 2009 at 9:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Greg Stark <greg.stark@enterprisedb.com> writes:
>> Without any real way to represent predicates this is all pie in the
>> sky. The reason we don't have predicate locking is because of this
>> problem which it sounds like we're no closer to solving.
>
> Yeah.  The fundamental problem with all the "practical" approaches I've
> heard of is that they only work for a subset of possible predicates
> (possible WHERE clauses).  The idea that you get true serializability
> only if your queries are phrased just so is ... icky.  So icky that
> it doesn't sound like an improvement over what we have.

I think we're veering off on a tangent, here.

As I understand it, the serialization anomalies that we have today are
caused by the fact that readers don't block concurrent writers.  So if
I read some data from table A and write it to table B and meanwhile
someone reads from table B and writes to table A, we may pass each
other like ships in the night unless we remember to use SELECT ... FOR
SHARE to guard against concurrent UPDATEs and DELETEs and LOCK ... IN
SHARE MODE to guard against concurrent INSERTs.

It would be nice to be have the option to dispense with this explicit
locking and still get serializable behavior and AIUI that's what these
SIREAD locks are designed to do (they also don't lead to additional
blocking as explicit locks potentially do).  The limitation is that
the granularity of the SIREAD locks isn't going to be magically better
than the granularity of your underlying lock subsystem.  Fortunately,
our underlying locking system for protecting against UPDATE and DELETE
operations is already row-level and therefore as good as it gets.  Our
underlying system for protecting against INSERT is pretty primitive by
comparison, so we'd have to decide whether to ignore inserts or take a
table-level SIREAD lock, and the latter would probably result in such
poor concurrency as to make the whole thing pointless.

But that doesn't mean that the entire project is pointless.  It just
means that we'll be able to protect against concurrent UPDATEs and
DELETEs without explicit locking, if the transaction isolation level
is set to serializable, but we'll still fall short when it comes to
concurrent INSERTs.  That would be a massive improvement versus where
we are now.  I do a fair amount of explicit locking in my code and
it's nearly all row-level locks to protect against concurrent
updates/deletes, so I can't see that only handling those cases would
be a bad place to start.  Fortunately, for my applications,
concurrency is low enough that explicit locking isn't a problem for me
anyway (also, I'm good at figuring out what to lock), but that's
clearly not true for everyone.

...Robert