Re: outer joins and for update

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> I think we could, in fact, lock rows on the nullable side of the join if
> we say that locking the NULL rows is not necessary. The rows do not
> physical exist and I could see an argument which says that those rows do
> not match any other rows which a concurrent transactions if attempting to
> modify -- since they don't exist.

The point of the comment really is that this is a predicate locking
problem. I should think that a minimum expectation of SELECT FOR UPDATE
is that you have exclusive hold on the selected rows and they won't
change underneath you before the end of your transaction. In the case
of an outer join where the left-side row joined to nothing on the
right-side, we can't guarantee that: repeating the SELECT might find a
matching right-side row, thereby changing the allegedly-locked join row.
To guarantee a stable view of the data, we'd need a predicate lock that
prevents a matching right-side row from being inserted.

The fact that MySQL doesn't care about consistency or sane semantics is
no news, of course, but I'm slightly more interested by your claim that
Oracle allows this. What do they do about the locking issue?

regards, tom lane