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

On May 14, 2010, at 12:56 , Kevin Grittner wrote:
>> 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.

I can see how a single SIREAD lock can potentially be cheaper than a FOR SHARE or FOR UPDATE lock. But the number of SIREAD locks would exceed the number of FOR SHARE / FOR UPDATE locks by a few order of magnitude I'd think - at least of you ran even transaction under true serializable isolation.

I don't quite understand how SIREAD locks work if they don't involve any disk IO, since shared memory isn't resizable. But I guess I'll find out once you post the patch ;-)

> 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.

That is entirely possible. However, unless your patch completely removes support for snapshot isolation (what is current called SERIALIZABLE), my proposal still eliminates the situation that user-level constraints are correct in READ COMMITTED and (true) SERIALIZABLE isolation but not in snapshot isolation.

Btw, the only user of FOR SHARE locks inside postgres proper are the RI triggers, and those do that special crosscheck when called within a SERIALIZABLE transactions. I do take this as evidence that the current behavior might not be all that useful with serializable transactions...

best regards,
Florian Pflug