On Mon, Nov 14, 2005 at 07:38:00PM -0500, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
