Re: change in LOCK behavior

On Wed, Oct 10, 2012 at 10:21:51PM +0200, 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).
>
> 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.

Regards,
Ken