Re: SELECT FOR UPDATE violates READ COMMITTED isolation?

On Wed, Apr 12, 2017 at 3:14 PM, Gavin Wahl <gwahl(at)fusionbox(dot)com> wrote:

> I think this paragraph explains why it happens:
> https://www.postgresql.org/docs/9.6/static/transaction-
> iso.html#XACT-READ-COMMITTED.
>
> > If the first updater commits, the second updater will ignore the row if
> the
> > first updater deleted it
>
> How is that allowed in READ COMMITTED? I never committed with 0 rows in
> test,
> so I expected to never have a SELECT that returns 0 rows.
>

​"... they will only find target rows that were committed as of the command
start time"​

The newly added test row from <one> did not exist when <two> began so it
can never been seen by <two>. Period.

"In this case, the would-be updater will wait for the first updating
transaction to commit or roll back (if it is still in progress)"

The <two> SELECT * FROM test FOR UPDATE; query sees every single row in
test but it indeed must wait due to the FOR UPDATE

"If the first updater commits, the second updater will ignore the row if
the first updater deleted it, otherwise it will attempt to apply its
operation to the updated version of the row."

Once <one> commits then <two> continues and ignores the now deleted rows.

Thus:

So, while there was never a time when the table contained zero rows all of
the rows that <two> wanted to see were gone by the time it got to them and
so it had nothing to show. This is what is documented and what you show.

In short, this behavior is the price you pay for not having to concern
yourself with serialization errors - which is exactly what you would get if
you executed these transactions in any of the higher isolation levels.

David J.