Re: determine snapshot after obtaining locks for first statement

On Thu, Dec 17, 2009 at 1:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I'm not very sure what a clearer explanation would look like
>
>> As a stab at it, how about?:
>
>> This behavior makes Read Committed mode unsuitable for many UPDATE
>> or DELETE commands with joins or subqueries
>
> After thinking a bit, I'd be inclined to add a new paragraph.
> In particular, now that FOR UPDATE actually works in subqueries,
> it'd be worth pointing out that you can add that to guard against
> this type of issue.  Perhaps, after the "DELETE FROM website"
> example, we could add something like
>
> UPDATEs and DELETEs involving joins or subqueries are particularly
> at risk, since they may perform an update based on a combination of
> old rows from other tables with an up-to-date target row.  This risk
> can be mitigated by adding FOR UPDATE or FOR SHARE to subqueries, so
> that all rows directly involved in an update are guaranteed current.
> However that will also increase the risk of deadlock failures.

I like that. It might also be worth trying to explain that if you
select some data out of the database, do a computation with it, and
then use the results to drive an update, you're going to want to make
the initial select be FOR SHARE.

...Robert