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

On May 14, 2010, at 15:54 , Kevin Grittner wrote:
> Florian Pflug <fgp(at)phlo(dot)org> wrote:
>> On May 14, 2010, at 12:56 , Kevin Grittner wrote:
>> 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.

Ah, yeah, that makes a lot of sense. I kinda had forgotten about REPEATABLE READ...

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

My proposal would make SELECT ... FOR UPDATE behave like Oracle does with regard to serialization conflicts. SELECT ... FOR SHARE doesn't seem to exist on Oracle at all - at least I couldn't find a reference to it in the docs.

The syntax isn't 100% compatible because Oracle seems to expect a list of columns after the FOR UPDATE clause, while postgres expects a list of tables.

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.

best regards,
Florian Pflug