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
SIREADlocks would exceed the number of FOR SHARE / FOR UPDATE locks by a few order of magnitude I'd think - at least of
youran 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.
ButI 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
currentcalled SERIALIZABLE), my proposal still eliminates the situation that user-level constraints are correct in READ
COMMITTEDand (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
whencalled within a SERIALIZABLE transactions. I do take this as evidence that the current behavior might not be all
thatuseful with serializable transactions... 

best regards,
Florian Pflug