Re: Lock changes with 8.1 - what's the right lock?

On Sun, Jul 16, 2006 at 05:46:16PM -0500, Wes wrote:
> Previously (pgsql 7.4.5), multiple loads would run simultaneously - and
> occasionally got 'deadlock detected' with the foreign key locks even though
> they were referenced in sorted order.  When loading tables other than
> 'addresses', foreign key locks did not prevent other jobs from grabbing the
> exclusive lock on 'addresses'.

Unless I'm misunderstanding you or a bug was fixed between 7.4.5
and 7.4.13 (the version I'm running), I'm not convinced that last
statement is true.  EXCLUSIVE conflicts with all lock types except
ACCESS SHARE; foreign key references prior to 8.1 use SELECT FOR
UPDATE and in 8.1 they use SELECT FOR SHARE, but in both cases they
acquire ROW SHARE on the referenced table, which conflicts with
EXCLUSIVE.

> With 8.1.4, the foreign key locks prevent other instances from grabbing the
> lock, so they wait until the first job is complete - only one job loads at a
> time.

Again, maybe I'm misunderstanding you, but the following example
behaves the same way in 8.1.4 and 7.4.13 (foo has a foreign key
reference to addresses):

T1: BEGIN;
T1: INSERT INTO foo (address_id) VALUES (1);
T2: BEGIN;
T2: LOCK TABLE addresses IN EXCLUSIVE MODE;
T2: (blocked until T1 completes)

Does this example differ from what you're doing or seeing?

> What is now the appropriate lock?  It needs to:
>
>   1. Prevent others from updating the table
>   2. Block other jobs that are requesting the same lock (if job 2 does a
> SELECT and finds nothing, it will try to create the record that job 1 may
> already have created in its transaction).
>   3. Not conflict with foreign key reference locks

SHARE ROW EXCLUSIVE is the weakest lock that meets these requirements.
It conflicts with itself (#2) and with ROW EXCLUSIVE, which UPDATE,
DELETE, and INSERT acquire (#1), but doesn't conflict with ROW SHARE,
which is what SELECT FOR UPDATE/SHARE acquire (#3).

--
Michael Fuhr