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

On Tue, Jul 25, 2006 at 07:12:28AM -0500, Wes wrote:
> > 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.
>
> My apologies for being so unclear. I had intended to just indicate that the
> problem occurred when we upgraded from 7.4.5 to 8.1, as opposed to this
> being fixed specifically in 8.1. I didn't realize this was fixed in a 7.4
> release - I thought it was 8.x. The last time I'd checked, it hadn't been
> fixed in 7.x.

New in 8.1 is that foreign key references use SELECT FOR SHARE
instead of SELECT FOR UPDATE, but in all versions the acquired locks
conflict with EXCLUSIVE. I see nothing in the Release Notes
indicating that that behavior changed between 7.4.5 and 7.4.13.

Did you try the example I posted? What happens if you do the
following? First set up a couple of tables:

CREATE TABLE foo (id integer PRIMARY KEY);
CREATE TABLE bar (fooid integer NOT NULL REFERENCES foo);
INSERT INTO foo VALUES (1);

Then open two connections to the database and execute the steps in
T1 in one connection and then the steps in T2 in the other connection:

T1: BEGIN;
T1: INSERT INTO bar VALUES (1);

T2: BEGIN;
T2: LOCK TABLE foo IN EXCLUSIVE MODE;

The T2 connection should block until you execute COMMIT or ROLLBACK
in T1. What happens on your system? Does this approximate what
you're doing?

Is it possible that your foreign key constraints are DEFERRED in
7.4 but not in 8.1? That would change transaction behavior that
depends on 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).
>
> Thanks for the confirmation. Is there any stronger lock that would not
> block SELECT foreign key references? I didn't find any documentation on
> what type of lock is grabbed by a when a foreign key is referenced during
> SELECT (or other).

An ordinary SELECT acquires ACCESS SHARE, which conflicts only with
the strongest lock, ACCESS EXCLUSIVE. If you're modifying a table
that has a foreign key constraint then 8.1 does a SELECT FOR SHARE
on the referenced table and earlier versions do a SELECT FOR UPDATE;
both cases acquire ROW SHARE on the referenced table. Two lock
types are stronger than SHARE ROW EXCLUSIVE: EXCLUSIVE and ACCESS
EXCLUSIVE, both of which conflict with ROW SHARE. So instead of
saying that SHARE ROW EXCLUSIVE is the weakest lock that meets your
requirements, I should have said that it's the only lock that does.

Hopefully I've understood what you're asking; if not then please
clarify.

--
Michael Fuhr