Re: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle

[slight rearrangement]
Florian Pflug  wrote:
> I'm very exited about the work you're doing
Always nice to hear.  :-)
> I view my proposal as pretty orthogonal to that work.
> My proposal allows for simple FK-like constraints to be
> implemented at user-level that are correct for all isolation
> levels.
OK, I can see the attraction in that.
> True serializable transaction are much more powerful than what I
> proposed, but at a much higher price too, due to the necessity of
> SIREAD locks.
I think that SIREAD locks will generally be cheaper than SELECT FOR
UPDATE, since the former don't require any disk I/O and the latter
do.  I only have one benchmark so far (more on the way), but it
attempts to isolate the cost of acquiring the SIREAD locks by using
a read-only load against a fully cached database.  Benchmarks so far
show the new version of the SERIALIZABLE level as supporting 1.8%
fewer TPS than REPEATABLE READ (the existing snapshot isolation
level) in that environment.  That will probably disappear into the
noise for any load involving disk I/O.
Now *rollbacks*, particularly those due to false positives, might
become a more serious issue in some pessimal loads, but I'm still
working on developing meaningful benchmarks for that.
I guess what I'm suggesting is that unless you have a very small
database with a very large number of connections in a high
contention workload, or you can't require SERIALIZABLE transaction
isolation level, SSI might actually perform better than what you're
proposing.  Of course, that's all conjecture until there are
benchmarks; but I'd be very interested in getting any and all
alternative solutions like this worked into a benchmark -- where I
can pull out the FOR UPDATE and FOR SHARE clauses, any redundant
updates or denormalizations added just for concurrency issues, and
all explicit locking -- and compare that under SERIALIZABLE to the
original performance.
-Kevin