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

On May 14, 2010, at 16:32 , Kevin Grittner wrote:

> Florian Pflug <fgp(at)phlo(dot)org> wrote:
>
>> I must admit that I wasn't able to find an explicit reference to
>> Oracle's behavior in their docs, so I had to resort to
>> experiments. They do have examples showing how to do FK-like
>> constraints with triggers, and those don't contain any warning
>> whatsoever about problems in SERIALIZABLE mode, though. But
>> still, if there is word on this from Oracle somewhere, I'd love to
>> hear about it.
>
> I suspect that in trying to emulate Oracle on this, you may run into
> an issue which posed challenges for the SSI implementation which
> didn't come up in the Cahill prototype implementations: Oracle, and
> all other MVCC databases I've read about outside of PostgreSQL, use
> an "update in place with a rollback log" technique. Access to any
> version of a given row or index entry goes through a single
> location, with possible backtracking through the log after that,
> which simplifies management of certain concurrency issues. Do they
> perhaps use an in-RAM lock table, pointing to the "base" location of
> the row for these SELECT FOR UPDATE locks? (Just guessing; I've
> never used Oracle, myself.)

Thanks for the heads up. I think my proposed doges this, though, since UPDATE as well as FOR SHARE and FOR UPDATE already follow the ctid chain to find the most recent tuple and fail with a serialization error (within >= REPEATABLE READ transaction) should this tuple be inaccessible to the transaction's snapshot.

Btw, I've just posted a quick-and-dirty patch that implements the parts of my proposal that deal with FOR UPDATE vs. UPDATE conflicts in response to Robert Haas' mail on this thread, just in case you're interested.

best regards,
Florian Pflug