Re: More FOR UPDATE/FOR SHARE problems

Kevin Grittner wrote:
> >>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> >> There you see a snapshot of the table that never existed. Either
> the
> >> snapshot was taken before the UPDATE, in which case i=3 should be
> >> included, or it was taken after the UPDATE, in which case i=4 should
> be
> >> included. So atomicity is broken for WHERE.
> >
> > This assertion is based on a misunderstanding of what FOR UPDATE in
> > read-committed mode is defined to do. It is supposed to give you
> the
> > latest available rows.
>
> Well, technically it's violating the Isolation part of ACID, not the
> Atomicity, since the UPDATE transaction will either commit or roll
> back in its entirety, but another transaction can see it in an
> intermediate (partially applied) state.[1]
>
> I guess the issue of whether this violation of ACID properties should
> be considered a bug or a feature is a separate discussion, but calling
> it a feature seems like a hard sell to me.

In trying to get some closure on this issue, I started investigating
this myself. I realize there is the issue with serializable isolation
level that is already documented:

http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html#XACT-SERIALIZABLE

Particularly, inserts by two transactions not seeing each other. OK, at
least it is documented.

There is also the problem of queries that add and remove rows from
SELECT FOR UPDATE sets:

http://archives.postgresql.org/pgsql-hackers/2009-01/msg01803.php

I have come up with a simpler example of that behavior:

S1:
test=> CREATE TABLE mvcc_test (status BOOLEAN);
CREATE TABLE
test=> INSERT INTO mvcc_test VALUES (true), (false);
INSERT 0 2
test=> BEGIN;
BEGIN
test=> UPDATE mvcc_test SET status = NOT status;
UPDATE 2

S2:
test=> SELECT * FROM mvcc_test WHERE status = true FOR UPDATE;

S1:
test=> COMMIT;

S2:
status
--------
(0 rows)

As you can see, the S2 SELECT FOR UPDATE returns zero rows, even though
one row would be returned before the UPDATE, and one row after the
update, and at no time were no rows matching its criteria ('true').

So, I thought, this is another SELECT FOR UPDATE problem, but then I was
able to duplicate it with just UPDATEs:

S1:
test=> CREATE TABLE mvcc_test (status BOOLEAN);
CREATE TABLE
test=> INSERT INTO mvcc_test VALUES (true), (false);
INSERT 0 2
test=> BEGIN;
BEGIN
test=> UPDATE mvcc_test SET status = NOT status;
UPDATE 2

S2:
test=> UPDATE mvcc_test SET status = true WHERE status = false;
UPDATE 0

S1:
test=> COMMIT;

S2:
test=> SELECT * FROM mvcc_test;
status
--------
t
f
(2 rows)

If the S2 UPDATE was run before or after the S1 UPDATE, it would have
set both rows to true, while you can see the two rows are different.

What is significant about this is that it isn't a serializable failure,
nor is it a SELECT FOR UPDATE failure.

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.

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