Postgresql: Implicit lock acquisition from foreign-key constraint evaluation

Question

So, I'm being confused about foreign key constraint handling in Postgresql. (version 8.4.4, for what it's worth).

We've got a couple of tables, mildly anonymized below:

device:
   (id, blah, blah, blah, blah, blah x 50)…
   primary key on id
   whooooole bunch of other junk

device_foo:
   (id, device_id, left, right)
   Foreign key (device_id) references device(id) on delete cascade;
   primary key on id
   btree index on 'left' and 'right'

So I set out with two database windows to run some queries.

db1>  begin; lock table device in exclusive mode;
db2>  begin; update device_foo set left = left + 1;

The db2 connection blocks.

It seems odd to me that an update of the 'left' column on device_stuff should be affected by activity on the device table. But it is. In fact, if I go back to db1:

db1> select * from device_stuff for update;
          *** deadlock occurs ***

The pgsql log has the following:

blah blah blah deadlock blah.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."device" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF X: update device_foo set left = left + 1;

I suppose I've got two issues: the first is that I don't understand the precise mechanism by which this sort of locking occurs. I have got a couple of useful queries to query pg_locks to see what sort of locks a statement invokes, but I haven't been able to observe this particular sort of locking when I run the update device_foo command in isolation. (Perhaps I'm doing something wrong, though.) I also can't find any documentation on the lock acquisition behavior of foreign-key constraint checks. All I have is a log message. Am I to infer from this that any change to a row will acquire an update lock on all the tables which it's foreign-keyed against?

The second issue is that I'd like to find some way to make it not happen like that. I'm ending up with occasional deadlocks in the actual application. I'd like to be able to run big update statements that impact all rows on device_foo without acquiring a big lock on the device table. (There's a lot of access going on in the device table, and it's kind of an expensive lock to get.)

Answer 1

Locking table in exclusive mode means that none process can read that table, and checking foreign key needs reading the table device.

Answer 2

The statement lock table device in exclusive mode takes a very restrictive lock on the table ("exclusive mode"). Modifying a table that has a foreign key onto a parent table takes a fairly innocuous share lock on the parent table (you can't truncate a table while rows referencing it are potentially being updated, for example).

Actually, trying it now, I can't reproduce your locking behaviour (on 8.4.4 as you are). I did:

create table device(device_id serial primary key, value text not null);
create table device_foo(device_foo_id serial primary key, device_id int not null references device(device_id) on delete cascade, value text not null);
insert into device(value) values('FOO'),('BAR'),('QUUX');
insert into device_foo(device_id, value) select device_id, v.value from (values('mumble'),('grumble'),('fumble')) v(value), device;

And then in two concurrent connections I did:

<1>=# begin; lock table device in exclusive mode;
<2>=# begin; update device_foo set value = value || 'x';

This appears to me to be equivalent to what you're doing, but I don't get the second session locking- it immediately gives "UPDATE 9" as expected. Inserting into device_foo blocks, as you'd expect, and so does an update statement setting the device_id column. I can see the ExclusiveLock in pg_locks from the db1 session in the db2 session. It also blocks if I do "select * from device for share", which is the statement you're seeing in the deadlock error. I also don't get a deadlock if I do a "select * from device_foo for update" from the db1 connection while db2 is blocked trying to update the device_id column in device_foo.

Updating a row does mark the row as locked, but that lock isn't visible in pg_locks. It does also take a lock on the table to lock out anyone trying to drop/truncate/reindex the table while one of its rows is being updated.

To lock the device table against concurrent updates, you may want a less strict locking mode. The manual suggests "share row exclusive" for this kind of activity. Although this is just one level down from "exclusive" it is compatible with a "select ... for share" statement.

So really, the open question is--- what's issuing that "select ... for share" query? :-S It does look like a statement intended to assert foreign-key integrity, but I can't reproduce it.