在PostgreSQL中DROP表时避免对被引用表进行排他访问锁定

Question

为什么在PostgreSQL中删除表需要对任何被引用的表进行"ACCESS EXCLUSIVE"锁定？有没有办法将其减少为"ACCESS SHARED"锁定或者不锁定？即，是否有一种方法可以在不锁定被引用的表的情况下删除关系？我在文档中找不到哪些锁是必需的，但是，除非我在并发操作期间以正确的顺序显式地获取锁定，否则我可以看到日志中等待AccessExclusiveLock的死锁，并且在常用引用表上获取这个限制性锁会导致在删除表时对其他进程造成短暂的延迟。为了澄清问题，

CREATE TABLE base (
    id SERIAL,
    PRIMARY KEY (id)
);
CREATE TABLE main (
    id SERIAL,
    base_id INT,
    PRIMARY KEY (id),
    CONSTRAINT fk_main_base (base_id)
        REFERENCES base (id)
        ON DELETE CASCADE ON UPDATE CASCADE
);
DROP TABLE main; -- why does this need to lock base?

- Dave

---

您希望发生什么情况呢？您正试图从一家繁忙的餐厅中清理家具，顾客们还在吃饭，那么他们应该支付什么费用呢？是一半的餐费？全额餐费？还是只支付他们已经消费的��分？ - wildplasser

@wildplasser，我不理解你的比喻；我说的不是在我要删除的表上的锁，而是在它所引用的表上的锁。被引用的表不应该关心正在进行的删除操作——它的数据不会因此而改变。它不需要做任何事情。 - Dave

我明白了。对于你想要删除并（可能）重新创建表的原因，我仍然不是很清楚。顺便说一下，对目录使用ACID很困难；可能是因为Postgres在这方面过于防御了。 - wildplasser

@wildplasser 表格没有被重新创建。这些表格用于确保客户隔离（例如 project_1_data 等）。我们正在迁移到使用模式来实现这一点（在其中我们可以简单地复制共享信息），但由于我们仍在从 MySQL 迁移，因此我们还没有能够进行这种更改。当我们归档死亡项目时，表格会被删除，但更重要的是在集成测试期间，我们会快速创建和销毁项目，以确保其测试环境的隔离。 - Dave

我已经成功重现了你的情况。经过一些重新思考，我必须得出结论，观察到的行为是正确的、有意的和必要的。如果DDL尚未提交，则另一个会话不应违反约束条件。一旦事务提交，另一个会话可以执行新数据模型允许的任何操作。（我认为允许多个版本的模式可能避免了独占锁的需要，但这很难做到正确） - wildplasser

Answer 1

对于所有搜索并试图了解为什么他们的drop table（或drop foreign key或add foreign key）长时间被卡住的人：

PostgreSQL（我查看了9.4到13版本）外键约束实际上是使用外键两端上的触发器实现的。

如果你有一个公司表（id作为主键）和一个银行账户表（id作为主键，company_id作为指向company.id的外键），那么实际上在bank_account表上有2个触发器，在company表上也有2个触发器。

table_name	timing	trigger_name	function_name
bank_account	AFTER UPDATE	RI_ConstraintTrigger_c_1515961	RI_FKey_check_upd
bank_account	AFTER INSERT	RI_ConstraintTrigger_c_1515960	RI_FKey_check_ins
company	AFTER UPDATE	RI_ConstraintTrigger_a_1515959	RI_FKey_noaction_upd
company	AFTER DELETE	RI_ConstraintTrigger_a_1515958	RI_FKey_noaction_del

在创建外键时，这些触发器的初始创建需要对这些表进行SHARE ROW EXCLUSIVE锁定（在9.4版本及更早版本中使用ACCESS EXCLUSIVE锁定）。此锁不会与“数据读取锁”冲突，但会与所有其他锁冲突，例如简单的INSERT / UPDATE / DELETE到公司表中。

删除这些触发器（当删除外键或整个表时）需要在这些表上进行ACCESS EXCLUSIVE锁定。此锁与所有其他锁定都冲突！

因此，想象一种情况，在该情况下您有一个正在运行的事务A，首先从公司表中进行了简单的SELECT（导致它在事务提交或回滚之前持有对公司表的ACCESS SHARE锁），现在正在进行3分钟的其他工作。您尝试在事务B中删除bank_account表。这需要ACCESS EXCLUSIVE锁定，需要等待直到ACCESS SHARE锁定被释放。 除此之外，所有要访问公司表（只是SELECT，或者可能是INSERT / UPDATE / DELETE）的其他事务都将排队等待ACCESS EXCLUSIVE锁定，该锁定正在等待ACCESS SHARE锁定。

长时间运行的事务和DDL更改需要细心处理。

- zutnop

---

请不要在多个问题中添加相同的答案。回答最好的一个，并将其余标记为重复。请参阅是否可以将重复的答案添加到多个问题中？ - Paul Roub

它不允许我将另一个标记为重复，因为这个问题还没有被接受的答案。但是我已经从另一个问题中删除了我的答案。 - zutnop

现在它有一个被接受的答案了 ;) - 这是很棒的信息；感谢您回答一个六年前的问题！ - Dave

Answer 2

        -- SESSION#1
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

BEGIN;
CREATE TABLE base (
    id SERIAL
    , dummy INTEGER
    , PRIMARY KEY (id)
);
CREATE TABLE main (
    id SERIAL
    , base_id INTEGER
    , PRIMARY KEY (id)
    , CONSTRAINT fk_main_base FOREIGN KEY (base_id) REFERENCES base (id)
        -- comment the next line out ( plus maybe tghe previous one)
        ON DELETE CASCADE ON UPDATE CASCADE
);
        -- make some data ...
INSERT INTO base (dummy)
SELECT generate_series(1,10)
        ;

        -- make some FK references
INSERT INTO main(base_id)
SELECT id FROM base
WHERE random() < 0.5
        ;
COMMIT;

BEGIN;
DROP TABLE main; -- why does this need to lock base?

SELECT pg_backend_pid();

        -- allow other session to check the locks
        -- and attempt an update to "base"
SELECT pg_sleep(20);

        -- On rollback the other session will fail.
        -- On commit the other session will succeed.
        -- In both cases the other session must wait for us to complete.
-- ROLLBACK;
COMMIT;

---

        -- SESSION#2
        -- (Start this after session#1 from a different terminal)
SET search_path = tmp, pg_catalog;

PREPARE peeklock(text) AS
SELECT dat.datname
        , rel.relname as relrelname
        , cat.relname as catrelname
        , lck.locktype
        -- , lck.database, lck.relation
        , lck.page, lck.tuple
        -- , lck.virtualxid, lck.transactionid 
        -- , lck.classid
        , lck.objid, lck.objsubid
        -- , lck.virtualtransaction 
        , lck.pid, lck.mode, lck.granted, lck.fastpath

FROM pg_locks lck
LEFT JOIN pg_database dat ON dat.oid = lck.database
LEFT JOIN pg_class rel ON rel.oid = lck.relation
LEFT JOIN pg_class cat ON cat.oid = lck.classid
WHERE EXISTS(
        SELECT * FROM pg_locks l
        JOIN pg_class c ON c.oid = l.relation AND c.relname = $1
        WHERE l.pid =lck.pid
        )
        ;

EXECUTE peeklock( 'base' );
BEGIN;
        -- attempt to perfom some DDL
ALTER TABLE base ALTER COLUMN id TYPE BIGINT;

        -- attempt to perfom some DML
UPDATE base SET id = id+100;

COMMIT;

EXECUTE peeklock( 'base' );

\d base
SELECT * FROM base;

- wildplasser

---

在回滚时，其他会话将失败：我认为这是你明显意识到的可能性，但我却忽略了：DROP TABLE 事务可能会回滚。这就解释了为什么约束不能立即删除和忘记。但我仍然不确定锁的类型；ALTER TABLE 和 UPDATE 是需要锁定的好例子，但我相信通过 ACCESS SHARED 锁可以避免这两个问题。你能想到任何 ACCESS SHARED 不足够的例子吗？还是像你最初建议的那样，Postgres 只是过于谨慎？ - Dave

经验之谈：不要混合使用DDL和DML。（也就是说，在进行DDL时，将其他会话踢出。对于测试目的，这不会造成问题）是的，这种课程锁定过于保守了；但有其原因。 - wildplasser

Answer 3

我想DDL锁定它所涉及的所有内容，仅出于简单起见 - 在正常操作期间，您不应该运行涉及非临时表的DDL。

---

为避免死锁，您可以使用咨询锁：

start transaction;
select pg_advisory_xact_lock(0);
drop table main;
commit;

这将确保只有一个客户端同时运行涉及引用表的DDL，因此其他锁定的获取顺序无关紧要。

---

您可以先删除外键来避免长时间锁定表：

start transaction;
select pg_advisory_xact_lock(0);
alter table main drop constraint fk_main_base;
commit;
start transaction;
drop table main;
commit;

这仍然需要独占锁定base，但时间要短得多。

- Tometzky

---

有多短？X秒有限制吗？还是只要比原来短10%？我问这个问题是因为知道时间长短会决定能否在大型数据库上线完成。 - Alan