Re: change in LOCK behavior

On 10.10.2012 22:37, ktm(at)rice(dot)edu wrote:
> On Wed, Oct 10, 2012 at 10:21:51PM +0200, Tomas Vondra wrote:
>> 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).
>>
>> regards
>> Tomas
>>
> Hi Tomas,
>
> 9.2 is doing it right. Per the documentation on explicit locking:
>
> http://www.postgresql.org/docs/9.2/static/explicit-locking.html
>
> Tip: Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement.

That is not the problem. We do expect it to block (that's why we do this
kind of lock in the first place), and that does happen both on 9.1 and 9.2.

The difference is that 9.1 does see the changes performed in the other
session (that held the lock and released it on commit), while 9.2 does not.

Tomas