Re: More FOR UPDATE/FOR SHARE problems

Bruce Momjian wrote:
> The fundamental behavior above is that the S1 transaction is adding
> _and_ removing rows from the S2 query's result set; S2 is seeing the
> pre-query values that don't match its criteria and ignoring them and
> blocking on a later row that does match its criteria. Once S1 commits,
> the new row does not match its criteria and it skips it, making the
> SELECT FOR UPDATE return zero rows, and the S2 UPDATE do nothing.
>
> Serializable mode does prevent the problem outlined above.
>
> Is this behavior documented already? If not, where should I add it?
> Perhaps section 13.2.1., "Read Committed Isolation Level":
>
> http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html#XACT-SERIALIZABLE
>
> That section vaguely suggests this might happen but doesn't give an
> example.

Well, with no one replying, :-(, I went ahead and added to the Read
Committed section of our manual to show a simple case where our read
committed mode produces undesirable results. I also did a little
cleanup at the same time.

You can see the resulting text here:

http://momjian.us/tmp/pgsql/transaction-iso.html#XACT-READ-COMMITTED

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +