Postgresql：从外键约束评估中隐式获取锁

Question

所以，我对 Postgresql 中的外键约束处理感到困惑。 （8.4.4 版，无论它的价值）。

我们有几个表，下面稍微匿名：

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'

所以我开始使用两个数据库窗口来运行一些查询。

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

db2 连接块。

对我来说，device_stuff 上“左”列的更新应该受到设备表上的活动的影响，这似乎很奇怪。但确实如此。事实上，如果我回到 db1:

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

pgsql 日志有以下内容：

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

语句 以独占模式锁定表设备 对表进行非常严格的锁定 ("独占模式")。修改具有父表外键的表需要在父表上使用相当无害的共享锁（例如，当引用表的行可能被更新时，您不能截断表）。

实际上，现在尝试一下，我无法重现您的锁定行为（在 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';

这在我看来相当于你正在做的事情，但我没有得到第二个会话锁定 - 它立即按预期给出“UPDATE 9”。正如您所期望的，插入到 device_foo 块中，设置 device_id 列的更新语句也是如此。我可以在 db2 会话中的 db1 会话中看到 pg_locks 中的 ExclusiveLock。如果我执行“select * from device for share”（这是您在死锁错误中看到的语句），它也会阻止。如果我从 db1 连接执行“select * from device_foo for update”，而 db2 尝试更新 device_foo 中的 device_id 列被阻止，我也不会陷入死锁。

更新行确实会将该行标记为锁定，但该锁在 pg_locks 中不可见。它还需要对表进行锁定，以阻止任何在更新表的行时尝试删除/截断/重新索引该表的人。

要锁定 device 表以防止并发更新，您可能需要不太严格的锁定模式。 手册 建议对这种类型“共享行独占”活动。尽管这仅比“独占”低一级，但它与“选择...共享”语句兼容。

所以实际上，悬而未决的问题是——什么发出了“选择...共享”查询？ :-S 它看起来确实像是一个旨在断言外键完整性的声明，但我无法重现它。

Answer 2

以独占模式锁定表意味着没有进程可以读取该表，并且检查外键需要读取表设备。