我有一个AFTER INSERT
表 A 的触发器,它更新表 B 中的多行(增加表 B 中的一列)。通常,Web 服务器会使用具有隔离级别的单个插入语句将多行插入到表 A 中READ COMMITTED
,这会导致触发器为插入的每一行触发。由于同时有多个 Web 服务器,因此总会有多个并发插入语句/事务。
最初,当触发器更新表 B 中的行时,这会导致 Postgres 报告死锁。为了解决这个问题,我更改了触发器函数以按照 PK 的顺序更新表 B 中的行:
UPDATE table_b
SET counter = counter + 1
WHERE ctid = ANY(ARRAY(
SELECT ctid FROM table_b
WHERE name = 'some name' AND store = 'store id'
ORDER BY id
FOR UPDATE
))
进行此更改后,死锁仍在发生。我认为上面的语句不起作用,所以我将其更改为使用 PL/pgSQLFOR
循环:
FOR row_b IN (
SELECT id
FROM table_b
WHERE name = 'some name' AND store = 'store id'
ORDER BY id
)
LOOP
UPDATE table_b
SET counter = counter + 1
WHERE id = row_b.id;
END LOOP;
但这也没有用,死锁仍在发生。据报告,死锁的来源是表 B 的更新语句(当增加行的时counter
)。这是 Postgres 报告的确切错误:
进程 316 在事务 850467907 上等待 ShareLock;被进程 426 阻塞。
进程 426 在事务 850467903 上等待 ShareLock;被进程 316 阻塞。
我对在这种情况下如何仍然发生僵局感到困惑。
您尝试以一致的排序顺序获取锁是在正确的轨道上。手册:
但是,您的实施实际上并没有实现这一点。你写:
通过按表 A 中插入的行对表 B 中的目标行进行排序,您只能实现部分排序。没有什么会在一个事务中保留表 A 中较早的行来更新表 B 中较晚的行,反之亦然。在同一事务的过程中,您必须对表 B 中写锁定的整组行强制执行一致的排序顺序。而且您必须首先对表 A 进行所有锁定,并且排序顺序一致。
假设有一个 trigger
FOR EACH ROW
,那不是它的工作原理。手册:有多种方法可以解决这个问题。
SERIALIZABLE
隔离级别。(更昂贵。并且您需要准备好在序列化失败时重复事务。)INSERT
在执行(和触发的)语句之前手动锁定表 A 和表 B 中涉及的行UPDATE
。但首先我会调查您是否真的需要该触发器来增加表 B 中的多行。即使在理想情况下,这也很昂贵。也许有更聪明的方法,从更好的数据库模式开始?
有关的: