sql - 如果尝试对锁定行的父级进行级联删除，PostgreSQL 会发生什么情况？

Question

我有一个表 foo_bar 和另一个表 spam_eggs，其中一个 fb 外键指向 foo_bar。 spam_eggs 行在其相关 spam_eggs.fb 被删除时级联删除。

我正在使用 PostgreSQL。

在一个事务中，我使用了 SELECT... FOR UPDATE 来锁定 spam_eggs 行。在此事务期间，另一个事务试图DELETE FROM... 我锁定行的相关foo_bar。这会触发错误，还是我锁定的行会导致查询阻塞，直到我的原始更新事务结束？

Answer 1

试试看。打开 psql 并进行一些设置:

CREATE TABLE foo_bar(id integer primary key);
CREATE TABLE spam_eggs(
     foo_bar_id integer not null references foo_bar(id) on delete cascade
);
INSERT INTO foo_bar (id) VALUES (1),(2),(3),(4);
INSERT INTO spam_eggs(foo_bar_id) VALUES (1),(2),(3),(4);

然后打开另一个psql连接。 BEGIN 两者中的事务。

1. 在第一个(旧) session 中，运行 SELECT 1 FROM spam_eggs WHERE foo_bar_id = 4 FOR UPDATE;
2. 在第二个(新) session 中，运行 DELETE FROM foo_bar WHERE id = 4;

您会看到第二个语句阻塞在第一个语句上。这是因为 foo_bar 上的 DELETE 级联到 spam_eggs 并尝试使用外键引用锁定该行，以便它可以删除它。该锁阻塞了 SELECT ... FOR SHARE 持有的锁。

一般来说，尝试在所有这些情况下进行测试:

• tx 是 BEGIN ISOLATION LEVEL READ COMMITTED 并且首先发出 ROLLBACK
• tx 是 BEGIN ISOLATION LEVEL READ COMMITTED 并且首先发出 COMMIT
• tx 是 BEGIN ISOLATION LEVEL SERIALIZABLE 并且首先发出 ROLLBACK
• tx 是 BEGIN ISOLATION LEVEL SERIALIZABLE 并且首先发出 COMMIT

确保您知道会发生什么。如果您在测试之前对您期望发生的事情进行推理，这对您的学习也有好处。

在这种情况下，READ COMMITTED 和SERIALIZABLE 隔离级别的行为相同。但是，如果您在 SELECT ... FOR UPDATE 之后执行 UPDATE 然后执行 COMMIT 那么它们的行为会有所不同； READ COMMITTED 版本将DELETE 成功，而 SERIALIZABLE 版本将失败并显示:

regress=# BEGIN ISOLATION LEVEL SERIALIZABLE;
regress=# DELETE FROM foo_bar WHERE id = 4;                                                                                                                                                                                                    
ERROR:  could not serialize access due to concurrent update                                                                                                                                                        
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."spam_eggs" WHERE $1 OPERATOR(pg_catalog.=) "foo_bar_id""