Florian Pflug <fgp(at)phlo(dot)org> wrote:
> On May 14, 2010, at 12:56 , Kevin Grittner wrote:
>> 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 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.
We use a well-worn technique used by many (most?) database products
-- granularity promotion. This is one of the things which could
cause enough false positives under some loads to cause your
technique to perform better than SSI for those loads.
> unless your patch completely removes support for snapshot
> isolation (what is current called SERIALIZABLE)
Both SERIALIZABLE and REPEATABLE READ currently map to snapshot
isolation. We're leaving REPEATABLE READ alone.
> my proposal still eliminates the situation that user-level
> constraints are correct in READ COMMITTED and (true) SERIALIZABLE
> isolation but not in snapshot isolation.
Agreed. If someone wants to enforce user-level constraints using
SSI, they will somehow need to ensure that less strict isolation
levels are never used to modify data. Your approach lifts that
burden.
By the way, if you can make this behave in a similar way to Oracle,
especially if the syntax is compatible, I'm sure it will help
promote PostgreSQL adoption. At PostgreSQL Conference U.S. East
2010, I talked briefly with a couple guys from an Oracle shop who
were looking at converting to PostgreSQL, and were very concerned
about not having what you describe. The techniques required to
ensure integrity in PostgreSQL were not, to put it mildly, appealing
to them. I suspect that they would be satisfied with *either* SSI
or the change you describe.
-Kevin