Postgres中UPDATE FOR与外键的锁定问题

Question

我的Postgres数据库表结构如下：

表名：performers（表演者）

• id（标识符）
• rank（排名）
• group_id（组的标识符，引用groups表）

表名：performances（表演）

• id（标识符）
• pay（报酬）
• performer_id（表演者的标识符，引用performers表）

表名：groups（组）

• id（标识符）

我经常需要从一个组中选择所有的表演者，并更新他们的排名。但这常常会导致死锁，因为我在执行一个SELECT ... FOR UPDATE的查询，而其他线程同时在进行INSERT操作。

我经常看到的错误示例（在Python SqlAlchemy中）：

DBAPIError: (TransactionRollbackError) deadlock detected
DETAIL:  Process 83182 waits for ShareLock on transaction 14282922; blocked by process 83171.
Process 83171 waits for ShareLock on transaction 14282925; blocked by process 83182.
HINT:  See server log for query details.
 'SELECT performers.id AS performers_id, performers.rank AS performers_rank, performers.group_id AS performers_group_id \nFROM performers \nWHERE performers.group_id = %(group_id_1)s FOR UPDATE' {'group_id_1': 2}

我已经找到了一些关于这个行为的示例。

我该如何解决这个问题？我可以切换到不同的事务锁定级别吗？我不想只是放弃然后重试——我希望数据库能为我处理这个竞争问题。

一定有办法解决这个问题——我想做的事情其实很简单。

Answer 1

如果你想避免死锁，可以让所有同时进行的写操作按照相同的唯一顺序进行。你可以在锁定语句中加上ORDER BY，并确保在所有地方使用相同的排序方式。比如：

SELECT ... FROM performers WHERE ... ORDER BY performers.id FOR UPDATE

这里的id可以是主键（或者其他任何稳定且不模糊的列组合）。

另外，如果你的操作涉及多个列，确保在不同的表中也按照严格相同的顺序进行锁定。

更多细节可以查看这个pgsql-general讨论串。
或者在手册中查看。

保持触发器（和外键约束）在必要的最小范围内。无论如何，首先要解决FOR UPDATE锁的问题。这可能会解决你的问题。如果没有，那就考虑换一个事务隔离级别。可串行化应该能解决问题。但这样你就必须准备好重试事务，直到它们成功。