Re: incoherent view of serializable transactions - Mailing list pgsql-hackers

> The page locking provides this because every index page or data page
> the serializable transaction looks at is locked against updates until
> the end of the transaction.  If it can see all the COLUMN=0 rows
> through an index, the index locks protect the transaction.  If a table
> scan is required, the entire table is locked against all
> modifications.  (That's right, it is not unusual to have entire tables
> locked against any modification until the end of a database
> transaction.)

You've mentioned a couple of times that you're surprised by the
reaction of the community to your proposed documentation changes.  I
have (a more muted version of) the same reaction as several previous
posters, and I think in the context of this paragraph I can explain
why.

If we were to construct a database that had one giant lock for the
entire database so that only a single query could execute at one time,
transactions would be serializable (because they'd in fact be
serialized).  However, performance would suck.  Therefore, database
developers and researchers have spent the last twenty (or more?) years
trying to come up with ways to allow multiple transactions to execute
in parallel while maintaining the appearance of serialization.
They've done this by introducing lower level locks: table-level,
page-level, row-level.  For most users, the artifacts that have been
introduced by these fine-grained locks are outweighed by the
performance benefits of greater concurrency - but, as you point out,
not necessarily always.

I don't think I can overstate the extent to which fine-grained locking
is viewed as a good thing.  It wouldn't surprise me to find out that
the locking behavior that you're relying on in Sybase is one which
some group of Sybase developers (if there still are any) are busily
laboring to eliminate.  I think you can see this reflected in Greg
Stark's comment as well: "It would very much surprise me if they did
since they've always had the most primitive locking infrastructure of
the three major databases."

With respect to predicate locking, what you're describing is NOT
predicate locking.  It's coarse-grained locking that largely or
completely obviates the need for predicate locking by greatly reducing
concurrency.  Now, from the point of view of the application developer
who needs very strong serializability guarantees but doesn't care
about concurrency, that's six of one, half a dozen of the other, but
to me that's the opposite of the typical situation.  Maybe our
documentation could say something along the lines of "PostgreSQL's
MVCC framework and row-level locking permit a greater degree of
concurrency than some other databases.  Even when the transaction
isolation level is set to serializable, serialization anomalies can
occur in the following situations.  When it is important to prevent
these anomalies, explicit row-level or table-level locking can be used
at the expense of reduced concurrency."

...Robert