Re: change in LOCK behavior

On Wednesday, October 10, 2012 10:43:57 PM Thom Brown wrote:
> On 10 October 2012 21:21, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
> > Hi,
> >
> > I've just noticed a change of LOCK command behavior between 9.1 and 9.2,
> > and I'm not sure whether this is expected or not.
> >
> > Let's use a very simple table
> >
> > CREATE TABLE x (id INT);
> >
> > Say there are two sessions - A and B, where A performs some operations
> > on "x" and needs to protect them with an "ACCESS EXCLUSIVE" lock (e.g.
> > it might be a pg_bulkload that acquires such locks, and we need to do
> > that explicitly on one or two places).
> >
> > Session B is attempting to read the data, but is blocked and waits. On
> > 9.1 it sees the commited data (which is what we need) but on 9.2 it sees
> > only data commited at the time of the lock attemt.
> >
> > Example:
> >
> > A: BEGIN;
> > A: LOCK x IN ACCESS EXCLUSIVE MODE;
> > A: INSERT INTO x VALUES (100);
> > B: SELECT * FROM x;
> > A: COMMIT;
> >
> > Now on 9.1, B receives the value "100" while on 9.2 it gets no rows.
> >
> > Is this expected? I suspect the snapshot is read at different time or
> > something, but I've checked release notes but I haven't seen anything
> > relevant.
> >
> > Without getting the commited version of data, the locking is somehow
> > pointless for us (unless using a different lock, not the table itself).
>
> I suspect it's this commit: d573e239f03506920938bf0be56c868d9c3416da
>
> http://archives.postgresql.org/pgsql-committers/2011-12/msg00167.php
Very likely, yes. In fact you get the same beaviour in 9.1 if you modify the
example slightly:

B: PREPARE foo AS SELECT * FROM x;
A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: EXECUTE foo;
A: COMMIT;

If you think about it for a second its not that surprising anymore. We start to
execute a query, acquire a snapshot for that, and then wait for the locks on
the target relations. We continue executing in the same snapshot for the
duration of the statement and thus cannot see any of the new rows which
committed *after* we assembled our snapshot.

The easy workaround is acquiring a AccessShareLock in the B transaction
separately.

Greetings,

Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services