主页 / dba / 问题 / 326580
Accepted
Alvin
Asked: 2023-04-29 10:05:27 +0800 CST

AFTER INSERT 触发器中的死锁

  • 772

我有一个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 阻塞。

我对在这种情况下如何仍然发生僵局感到困惑。

postgresql

1 个回答

  1. Best Answer

    您尝试以一致的排序顺序获取锁是在正确的轨道上。手册：

    防止死锁的最佳方法通常是通过确保所有使用数据库的应用程序以一致的顺序获取多个对象上的锁来避免死锁。

    但是，您的实施实际上并没有实现这一点。你写：

    • AFTER INSERT表 A 的触发器更新表 B 中的多行 ...
    • READ COMMITTED... 使用具有隔离级别的单个插入语句将多行插入表 A

    通过按表 A 中插入的行对表 B 中的目标行进行排序，您只能实现部分排序。没有什么会在一个事务中保留表 A 中较早的行来更新表 B 中较晚的行，反之亦然。在同一事务的过程中，您必须对表 B 中写锁定的整组行强制执行一致的排序顺序。而且您必须首先对表 A 进行所有锁定，并且排序顺序一致。

    假设有一个 trigger FOR EACH ROW，那不是它的工作原理。手册：

    PostgreSQL 提供每行触发器和每语句触发器。对于每行触发器，触发器函数会为受触发触发器的语句影响的每一行调用一次。相反，每条语句触发器只在执行适当的语句时调用一次，而不管该语句影响的行数。

    有多种方法可以解决这个问题。

    • 在同一个事务中只在表 A 中插入一行。（更贵。可能很乏味。）
    • 使用SERIALIZABLE隔离级别。（更昂贵。并且您需要准备好在序列化失败时重复事务。）
    • 使用语句级触发器。（可能很棘手或不可能。）
    • INSERT在执行（和触发的）语句之前手动锁定表 A 和表 B 中涉及的行UPDATE。

    但首先我会调查您是否真的需要该触发器来增加表 B 中的多行。即使在理想情况下，这也很昂贵。也许有更聪明的方法，从更好的数据库模式开始？

    有关的：

    • 3

相关问题