Re: Select For Update and Left Outer Join

On Jul11, 2011, at 20:16 , Kevin Grittner wrote:
> Florian Pflug <fgp(at)phlo(dot)org> wrote:
>> Part (B) has some relationship to what I tried to archive by
>> changing the way REPEATABLE READ transactions and row locks
>> interact. Though my intention wasn't full serializability, only
>> enough protection to make user-space FOREIGN KEYS work safely for
>> REPEATABLE READ transactions.
>
> Florian, I know that you looked at Oracle's treatment of SELECT FOR
> UPDATE, so could you respond to Tom's question about the semantics
> of that? (From what you and Patrick have posted I gather that from
> a user visible logical perspective SELECT FOR UPDATE is the same as
> a no-op UPDATE RETURNING, although there may be performance
> differences.

(CC'ing Tom now, hope thats OK)

I can only comment with certainty on the behaviour of FOR UPDATE
regarding serialization conflicts. There, Oracle treats FOR UPDATE
exactly like UPDATE, i.e. UPDATE raises a serialization error if it
encounters a row locked FOR UPDATE by a transaction invisible to the
UPDATEing one.

What Tom wanted to know, I believe, was whether FOR UPDATE locks only
existing *rows* (i.e., locks nothing in case of a LEFT JOIN without
a matching right row), or whether it actually locks the *fact* that
no such row exists (i.e., prevents future inserts of matching rows).

Now, I cannot comment on that with absolute certainty, and currently
don't have an Oracle instance available to test, but I can say so much:

I'd very *very*, *very* surprised if they did anything other than
simply locking nothing in the case of a LEFT join without a matching right
row. As far as I'm aware, Oracle simply doesn't do predicate locking,
and doesn't do true serializability. Their SERIALIZABLE mode is actually
snapshot isolation, just like ours used to be. It'd be very strange to
do yet, but yet to do predicate locking when it comes to SELECT FOR UPDATE.

> From Patrick's recent post I gather that MS SQL Server
> [at least in some configuration -- it has many settings which might
> affect this]

Yeah MS-SQL really isn't the idea target for comparison here. You
can override pretty much any lock that MS-SQL takes with a stronger
or weaker one from what I've seen. I wouldn't be at all surprised if
you could convince it to work either way by putting some (probably
rather obscure) incantations into your SQL statements.

best regards,
Florian Pflug