主页 / dba / 问题 / 273163
Accepted
Theron Luhn
Asked: 2020-08-04 16:41:48 +0800 CST

为什么添加自引用外键会冻结表？

  • 772

我正在运行 PostgreSQL 11.5。我有一个简单的寄存器表。

create table register(
  id serial primary key,
  name text not null
);

该表很小（约 6000 行），读取相对较重（100 秒查询/秒），几乎没有写入。我想添加一个自引用外键：

alter table register
  add column leader_id integer references register(id);

鉴于桌子的大小，我认为这将是一个相当平凡的变化。它在本地和登台服务器上运行良好。但是，当我针对生产运行它时，表被锁定了。日志显示 ALTER 和各种 SELECT 语句需要大约 10m 才能完成。

2020-08-04 00:01:15 UTC:10.0.2.101(59588):postgres@db:[21609]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: unexpected EOF on client connection with an open transaction
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: duration: 456741.453 ms execute <unnamed>: alter table register
add column leader_id integer references register(id);

2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: could not send data to client: Broken pipe
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: duration: 456635.839 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 123 AND register.account_id = '22781BD1-F37A-4ACE-9A3D-CBF3464AFB43'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: duration: 456656.956 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 234 AND register.account_id = 'A6D8395C-63E8-40A8-A0AE-4F19B1DA5509'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:FATAL: connection to client lost

这里发生了什么导致表锁定？如何安全地使用自引用外键？

postgresql postgresql-11

1 个回答

  1. Best Answer

    快速尝试是set lock_timeout=1000;，然后尝试你的 ALTER 很多次。很可能您每次都会遇到超时，但您可能会很幸运，这将使您不必寻找实际的修复程序，我很遗憾地说，这几乎没有那么容易。

    几乎可以肯定的是，其他进程在注册时执行 SELECT 后保持其事务打开。在执行第一个 SELECT 之后，事务将在该表上拥有一个 ACCESS SHARE 锁，直到事务提交或回滚。

    不幸的是， ADD COLUMN 需要一个 ACCESS EXCLUSIVE 锁 - 无论是否涉及外键，自引用与否。而且，根据冲突锁定模式表，ACCESS EXCLUSIVE 确实与 ACCESS SHARE 冲突。

    您可以查看具体涉及哪些锁，并且加入以查看实际查询pg_locks很有用。pg_stat_activity接下来是 3 个 psql 会话，交错，所以你可以看到发生了什么。

    -- psql session 1: what the always-reading application code is doing
testdb=# BEGIN; SELECT * from register where id=10;
BEGIN
 id | name 
----+------
 10 | bar
(1 row)

testdb=# -- now going to psql session #2.


-- psql session 2: look at the locks from the SELECT's still-open transaction.

testdb=# select relation::regclass, case when granted then mode else concat(mode, '*') end as mode, query from pg_locks join pg_stat_activity on pg_locks.pid=pg_stat_activity.pid and pg_locks.pid<>pg_backend_pid() where pg_locks.locktype='relation';
   relation    |      mode       |                query                
---------------+-----------------+-------------------------------------
 register      | AccessShareLock | SELECT * from register where id=10;
 register_pkey | AccessShareLock | SELECT * from register where id=10;
(2 rows)

-- psql session #3: perform the ALTER
testdb=# begin; ALTER TABLE register ADD COLUMN leader_id integer;
BEGIN
-- is now hanging on the ALTER - back to session #2

testdb=# select relation::regclass, case when granted then mode else concat(mode, '*') end as mode, query from pg_locks join pg_stat_activity on pg_locks.pid=pg_stat_activity.pid and pg_locks.pid<>pg_backend_pid() where pg_locks.locktype='relation';
   relation    |         mode         |                       query                        
---------------+----------------------+----------------------------------------------------
 register      | AccessShareLock      | SELECT * from register where id=10;
 register_pkey | AccessShareLock      | SELECT * from register where id=10;
 register      | AccessExclusiveLock* | ALTER TABLE register ADD COLUMN leader_id integer;
(3 rows)

    如您所见，ALTER TABLE 正在尝试，但未能（当 pg_locks.granted 为 false 时添加星号）在注册表上获取 AccessExclusiveLock。

    此时，您的应用程序将开始出现问题。让我们打开第 4 个 psql 并尝试另一个 SELECT：

    testdb=#  BEGIN; SELECT * from register where id=12;
BEGIN
--- aaaand... the SELECT is hanging now too! Let's go back to #2 and check the locks again

testdb=# select relation::regclass, case when granted then mode else concat(mode, '*') end as mode, query from pg_locks join pg_stat_activity on pg_locks.pid=pg_stat_activity.pid and pg_locks.pid<>pg_backend_pid() where pg_locks.locktype='relation';
   relation    |         mode         |                       query                        
---------------+----------------------+----------------------------------------------------
 register      | AccessShareLock      | SELECT * from register where id=10;
 register_pkey | AccessShareLock      | SELECT * from register where id=10;
 register      | AccessExclusiveLock* | ALTER TABLE register ADD COLUMN leader_id integer;
 register      | AccessShareLock*     | SELECT * from register where id=12;
(4 rows)

    PG 看到表上有一个已经在等待的 ACCESS EXCLUSIVE 锁，在 ACCESS EXCLUSIVE 完成它并释放锁之前，将不再授予表上的额外 ACCESS SHARE 锁。因此，现在您的 SELECT 正在堆积，并且一切都卡住了，直到从ALTER TABLE 第一次尝试获取锁提交或回滚时选择的所有事务。register

    唉，这里的解决方法是“不要那样做”；应用程序代码需要在 SELECT 之后立即发出 COMMIT 或 ROLLBACK （只要 SELECT 是事务中唯一发生的事情，这无关紧要），以便立即释放 ACCESS SHARE 锁。您还可以拆分 ADD COLUMN 和 ADD FOREIGN KEY 步骤，因为只有 ADD COLUMN 需要 ACCESS EXCLUSIVE（ADD FOREIGN KEY 只需要 SHARE ROW EXCLUSIVE，这与 ACCESS EXCLUSIVE 不冲突），但如果这有帮助，我会感到惊讶; 这是导致问题的锁定序列，而不是执行 ADD FOREIGN KEY 所涉及的额外工作。

    • 1

相关问题