Kevin Grittner wrote:
> Jeff Davis <pgsql@j-davis.com> wrote:
>
> > I don't know what you mean by "get-a-new-snapshot strategy" or
> > how it is different from the current read committed behavior.
>
> Our current Read Committed level, if it blocks on a competing UPDATE
> or DELETE, can provide a view of data which is based on a mix of
> snapshots, causing various anomalies. (See the recent thread titled
> "determine snapshot after obtaining locks for first statement" that
> veered off into a discussion of this issue.)
>
> Basically, if an UPDATE or DELETE blocks on a competing UPDATE or
> DELETE (within a READ COMMITTED database transaction), it will, once
> it is free to proceed, find the modified version of the row on which
> it was blocking. So that will be committed data as of a later
> transaction ID than other data we may be viewing, which would be
> based on the snapshot from the start of the statement. As described
> in the thread, that causes a target row not to be visible unless it
> is present in both snapshots, and may cause joined tables or
> subquery data to be out of sync with data from target rows.
FYI, this behavior is documented:
http://www.postgresql.org/docs/8.4/static/transaction-iso.html#XACT-READ-COMMITTED
I know Kevin knows this but others might find the docs help clarify the
issue.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +