两个表锁上的 PostgreSQL 死锁

Question

我正在锁定两个没有互连列的不同表，但我仍然遇到死锁。

这是服务器日志:

2013-10-22 15:16:19 EDT ERROR:  deadlock detected
2013-10-22 15:16:19 EDT DETAIL:  Process 26762 waits for AccessExclusiveLock on relation 39913 of database 39693; blocked by process 26761.
    Process 26761 waits for RowExclusiveLock on relation 40113 of database 39693; blocked by process 26762. 
    Process 26762: lock table par_times in access exclusive mode
    Process 26761: INSERT INTO cached_float (entry_id, figure_type, value) VALUES (33225, 1, 54.759402056277075) RETURNING cached_float.id

有什么想法吗？

Answer 1

您可以通过检查此处显示的数字来调试它:

Process 26762 waits for AccessExclusiveLock on relation 39913 of database 39693; blocked by process 26761. Process 26761 waits for RowExclusiveLock on relation 40113 of database 39693; blocked by process 26762.

在你的数据库中运行:

SELECT 39913::regclass AS tbl1, 40113::regclass AS tbl2

查看涉及的表格。还要考虑所涉及表的任何触发器和可能的外键约束。

通常:手动锁定表不一定能防止死锁。这可能是导致死锁的原因。