Re: outer joins and for update

On Mon, Nov 14, 2005 at 07:38:00PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > Do we really need to prevent inserts from happening under a SELECT FOR
> > UPDATE? ISTM that's trying to apply serializable concurrency to SELECT
> > FOR UPDATE even if it's running in a read committed transaction. In the
> > single table case we don't prevent someone from inserting a value...
>
> You're missing the point entirely, Jim. In the first place, SELECT FOR
> UPDATE has little or nothing to do with serializable mode: it's
> guaranteed to lock and return the latest committed version of the row.
> In the second case, inserting additional tuples does not invalidate your
> lock on the tuples you selected to begin with. SELECT FOR UPDATE
> doesn't try to guarantee that if you were to select again with the same
> WHERE condition, there might not be more rows matching the same
> condition. It does try to guarantee that the rows you selected before
> are still there and unchanged.
>
> In the case being discussed here, you're trying to lock rows of an
> outer-join. IMHO, if that means anything at all, it means that if
> you read those rows again they will still look the same. Having the
> righthand side go from NULL to not-NULL does not qualify as "looking the
> same" in my book.

Another way to look at it is that it's not locking rows in a SELECT
clause, it's locking rows in tables. In fact, that's at least supported
by DB2; it allows you to specify field or table names when you do SELECT
FOR UPDATE on a join. That way you can tell it exactly what you expect
to be updating, and what exactly it should be locking.

> Perhaps this could be clarified if someone has an actual use case of
> wanting to SELECT FOR UPDATE from an outer join, and can explain what
> semantics they think they need for that.

Agreed. Hopefully the original author can provide one.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461