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

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

Oh, yeah, right. I was thinking about acquiring the same advisory lock
in both sessions, but somehow I forgot to mention that.

>> 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.

Yes, exactly what I was suspecting.

>> 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.

Not sure what strange behaviour would that cause (given that 9.1 did
that if I understand that correctly). More expensive - no doubt about
it, and the commit message mentions that it's an optimization.

>> 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!" ;)

I've never thought it's simple, there's a lot of things to deal with.
But I was somehow surprised that something that worked fine for quite
long time, suddenly broke on 9.2. Yes, it might be a dependency on
something that was not really guaranteed.

>>> 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?

Long story short, we don't, pg_bulkload does. And it's also the reason
why we're so unhappy about the change, because the other session is
waiting for the load to complete and then it is served with an old snapshot.

Tomas