Re: Select For Update and Left Outer Join

Patrick Earl <patearl(at)patearl(dot)net> writes:
> On Sun, May 1, 2011 at 4:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Quite. What would it mean to lock the absence of a row?

> I would argue that SELECT FOR UPDATE never locks on the absence of a
> row. For example, if I do:
> SELECT * FROM Table WHERE Column = 10
> The existing rows are locked, but somebody could come along and add
> another unlocked row with Column = 10.

Addition of new rows certainly isn't supposed to be prevented by a
SELECT FOR UPDATE, but that's not the problem here. What I *do* expect
a SELECT FOR UPDATE to promise is that the rows it did return can't
change or be deleted by someone else for the life of my transaction.
This is not possible to promise for null-extended rows unless you
somehow lock out addition of a matching row on the inside of the join.
Without that, a row that looked like <pet fields, nulls> when you
selected it might suddenly start looking like <pet fields, cat fields>
due to someone else's modification. And after that, since you still
haven't got a lock on the cat row, the cat fields could keep on
changing.

I'm prepared to believe that there are some applications where that
can't happen due to other interlocking, or doesn't matter to the
application, but SELECT FOR UPDATE really can't assume that. I think
what you're proposing is to fundamentally break the semantics of SELECT
FOR UPDATE for the sake of convenience.

You didn't explain exactly why your application doesn't care about this,
but I wonder whether it's because you know that a lock on the parent
"pet" row is sufficient due to application coding rules. If so, you
could just tell SELECT FOR UPDATE to only lock the "pet" rows, and be
happy:

select * from pet left join cat ... for update of pet;

regards, tom lane