Re: a SELECT FOR UPDATE question

On Thu, Feb 10, 2005 at 09:53:49AM -0500, Tom Lane wrote:
>
> Right.  Furthermore, xactC's query result could have been stale when it
> was obtained, nevermind the separate query to pg_locks:
>
> xactA: updates row
> xactC: starts, sets snapshot
> xactB: attempts to update same row, blocks until xactA completes
> xactA: commits
> xactB: unblocks and acquires a lock on the row
> xactC: query finds xactA in row's xmax because of MVCC rules

Hmmm...that's not what I'm seeing in 8.0.1, at least not when
xactC is READ COMMITTED:

CREATE TABLE foo (id integer PRIMARY KEY, val text NOT NULL);
INSERT INTO foo VALUES (1, 'initial');

xactA=> BEGIN;
xactA=> UPDATE foo SET val = 'A' WHERE id = 1;
xactA=> SELECT xmin, xmax, * FROM foo;
  xmin  | xmax | id | val
--------+------+----+-----
 122508 |    0 |  1 | A

xactC=> BEGIN;

xactB=> BEGIN;
xactB=> UPDATE foo SET val = 'B' WHERE id = 1;  -- blocks

xactA=> COMMIT;  -- xactB now unblocked

xactB=> SELECT xmin, xmax, * FROM foo;
  xmin  | xmax | id | val
--------+------+----+-----
 122512 |    0 |  1 | B

xactC=> SELECT xmin, xmax, * FROM foo;
  xmin  |  xmax  | id | val
--------+--------+----+-----
 122508 | 122512 |  1 | A

In xactC's query, xmax is xactB.  Is this test not the situation
you describe?  I've seen stale info under certain conditions when
xactC is SERIALIZABLE, but when it's READ COMMITTED then the tests
I've done so far have always seen xmax change to whoever currently
holds the lock.  There's still a race condition, but visibility
doesn't seem to be a problem.  Is that not supposed to be happening,
or am I still missing something?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/