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

On Wednesday, October 10, 2012 11:45:41 PM Tomas Vondra wrote:
> On 10.10.2012 23:31, Andres Freund wrote:
> > 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.
> 
> Oh yeah, right. Any lock would work - advisory or not.
Well, it needs to be a lock youre conflicting on, not any lock ;)

> >> 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.
> 
> Well, equally it's not an argument for the 9.2 behavior, I guess. I'm
> not convinced this is a bug (partly because I haven't found any explicit
> statement regarding this in the docs), that's why I started this thread
> instead of spamming pgsql-bugs.
> 
> For us (our app) this means we'll need to make it a bit more complex,
> add some more explicit locking that we did not need in 9.1. Acquiring an
> Access Share lock explicitly feels a bit strange, because that's the
> lock acquired by SELECT statement anyway.
Yea, but its acquired *after* the snapshot is taken. And again, thats what 
happened in 9.1 as well. Just that *another* snapshot was just for planning the 
query which by also needs to lock the table in share mode. So after the lock 
was taken for planning a new snapshot was acquired for execution... Thats not 
the case anymore in simpler cases.

> The only difference seems to be that the snapshot is not reacquired
> after obtaining the lock. Which may or may not be the right thing,
> depending on the definition of when the query was executed (when asking
> for the lock or after obtaining it?)
You can't generally reacquire snapshots after waiting for a lock. For one it 
would be noticeably expensive and for another it would actually result in very 
strange behaviour in queries with multiple tables.

> Anyway, this seems to me like a behavior change that might bite many
> others, unknowingly depending on the 9.1-like behavior and I believe
> it's worth mentioning somewhere - not sure where.
"Locking is not as simple as you (and most of us) thought!" ;)

> > 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.
>
> Probably yes, but we're not doing that so I haven't noticed that.
Btw, unrelated to this problem, but why are you access exlusive locking that 
table? Shouldn't an exlusive lock be enough?

Greetings,

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