Re: change in LOCK behavior

On 10.10.2012 22:42, Andres Freund wrote:
> On Wednesday, October 10, 2012 10:21:51 PM Tomas Vondra 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).
> That sounds like youre using different isolation levels in 9.1 and 9.2. Is that
> possible? I.e. your 9.1 test uses read committed, and 9.2 uses repeatable read
> or serializable.

Nope, it's 'read commited' on both. I haven't touched this, but I've
verified it to be sure.

============ 9.1 ============

$ psql testdb
psql (9.1.6)
Type "help" for help.

testdb=# show server_version;
server_version
----------------
9.1.6
(1 row)

testdb=# show transaction_isolation ;
transaction_isolation
-----------------------
read committed
(1 row)

============ 9.2 ============

$ psql testdb
psql (9.2.0)
Type "help" for help.

testdb=# show server_version;
server_version
----------------
9.2.0
(1 row)

testdb=# show transaction_isolation
testdb-# ;
transaction_isolation
-----------------------
read committed
(1 row)