Re: determine snapshot after obtaining locks for first statement

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: determine snapshot after obtaining locks for first statement
Дата
Msg-id 4635.1261073151@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: determine snapshot after obtaining locks for first statement  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Ответы Re: determine snapshot after obtaining locks for first statement  (Robert Haas <robertmhaas@gmail.com>)
Re: determine snapshot after obtaining locks for first statement  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Список pgsql-hackers
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.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.
        regards, tom lane


В списке pgsql-hackers по дате отправления:

Предыдущее
От: "Joshua D. Drake"
Дата:
Сообщение: Re: determine snapshot after obtaining locks for first statement
Следующее
От: Robert Haas
Дата:
Сообщение: Re: determine snapshot after obtaining locks for first statement