Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> I could use a brief reminder of how this bit fits into the
> "serializable lock consistency" patch that's already sitting into
> the CF queue as "Ready for Committer" though.
Florian's patch prevents an "integrity inversion" that PostgreSQL
has had for ages, where attempting to prevent serialization
anomalies under snapshot isolation through SELECT FOR UPDATE/SHARED
gave you less protection than in the less strict levels. This has
been a particular nuisance to those trying to convert from Oracle,
where using the more strict isolation level always gives *more*
integrity protection, not less. That patch appears to strengthen
PostgreSQL integrity guarantees around SELECT FOR UPDATE/SHARED to
match Oracle.
One difference between that and the SSI patch is that with Florian's
patch you still need to recognize potential interactions which could
generate anomalies and explicitly code to prevent them, while in SSI
this is sorted out by the database engine automatically at run time.
Another difference is that Florian's patch introduces blocking to
protect integrity, while SSI does not.
There are definitely use cases for both. In general, SSI is a "big
shop" oriented solution, while the explicit coding may be more
appropriate in many environments where you have just a few
programmers working with dozens or hundreds of database transaction
types rather than thousands. The latter is also likely to be a
popular choice for those converting from other databases, even for
big shops, where they already have a working scheme using explicit
locking.
-Kevin