Postgresql: implicit lock detection due to foreign key constraint

Question

So, I got confused in handling foreign key constraints in Postgresql. (version 8.4.4, what it is for).

We have several tables slightly 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 sent two database windows to run some queries.

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

Db2 connection blocks

It seems strange to me that updating the "left" column on device_stuff should depend on activity in the device table. But this. 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;

, : -, , . pg_locks, , , , update device_foo . (, - .) . , , - . , , ?

, , . . , device_foo, . ( device, ).

Answer 1

lock table device in exclusive mode ( " " ). ( , , , , ).

, , ( 8.4.4, ). :

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;

:

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

, , , - " 9", . device_foo , , , device_id. ExclusiveLock pg_locks db1 db2. , " * ", , . , "select * from device_foo " db1, db2 , device_id device_foo.

, pg_locks. , , // , .

device , . " " . "", "select... for share".

, , --- "select... for share"?: -S , , .

Answer 2

, , .