Re: determine snapshot after obtaining locks for first statement

On Thu, 2009-12-17 at 12:16 -0600, Kevin Grittner wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > 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.
>
> Much better than my suggestion. Including both the problem
> conditions and the solution is ideal.
>
> I'd missed that we now allow FOR UPDATE and FOR SHARE on subqueries.
> Nice enhancement.

+1

Joshua D. Drake

>
> -Kevin
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.