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 <t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
