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

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.

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

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

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.

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

Tomas