Re: change in LOCK behavior

On 10 October 2012 21:21, Tomas Vondra <tv(at)fuzzy(dot)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
--
Thom