Re: change in LOCK behavior - Mailing list pgsql-hackers

On Wednesday, October 10, 2012 11:23:10 PM Tomas Vondra wrote:
> On 10.10.2012 23:05, Andres Freund wrote:
> > On Wednesday, October 10, 2012 10:43:57 PM Thom Brown wrote:
> >> On 10 October 2012 21:21, Tomas Vondra <tv@fuzzy.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.
> 
> Yes, that was my guess too (that the snapshot is acquired before asking
> for the lock and not re-acquired after getting the lock).
> 
> > The easy workaround is acquiring a AccessShareLock in the B transaction
> > separately.
> 
> I know - I've mentioned explicit locking as a possible solution in my
> first message, although it would make the whole process more complex.
I read your original statement as if you would want to use a separate lock 
(advisory?) which you don't need.

> The question is whether that should be necessary or whether the 9.2
> should behave the same as 9.1.
Given that 9.1 behaves the same as 9.2 with prepared statements I don't really 
see a convincing argument for changing this from the status quo.

You can hit the same/similar behaviour in 9.1 even if youre not using PREPARE 
although the window isn't too big and you need DML + only an EXCLUSIVE (not 
access exlusive) lock for it.

Greetings,

Andres

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