Re: Serializable implementation

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. +