Re: outer joins and for update

On Mon, 14 Nov 2005, Tom Lane wrote:

> Gavin Sherry <swm@linuxworld.com.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

I thought you might say that. I'm yet to do much reading on predicate
locking -- do you think it is an area we will even pursue?

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

Well.... we can guarantee that we wont see rows added by concurrent
transactions if we're in serializable isolation level :-).

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

I wont be able to actually test to see what they do until Thursday at the
earliest. Their manual offers no detail.

Gavin