我有两个查询一起死锁
PERFORM id
FROM stack
WHERE id IN (SELECT tmp.stkid FROM tmp_push_bulk tmp WHERE tmp.stkid > 0)
ORDER BY id
FOR UPDATE OF stack
和
PERFORM stk.id
FROM stack stk
WHERE stk.referer IN (
SELECT tmp.id
FROM tmp_renew_stk tmp
)
ORDER BY stk.id
FOR UPDATE OF stk
错误是:
- PG (20:46:37) [14786]: Execute command failed: ERROR: deadlock detected
DETAIL: Process 14797 waits for ShareLock on transaction 183495696; blocked by process 24303.
Process 24303 waits for ShareLock on transaction 183495704; blocked by process 14797.
HINT: See server log for query details.
我还认为每个进程都按 id 列的顺序锁定其行,因此死锁是不可能的。谁能告诉我为什么?
可能是
IN
表达式以不特定的顺序锁定行(未经测试)。在可能的情况下,我通常会用JOIN
替换较大集合上的IN
。这开始起来更快,从而最大限度地减少死锁的机会。我会尝试:
更新:根据您的评论,您有很多重复项。假设临时表中存在重复,我建议使用子查询进行三个目的:
- 折叠重复项。
- 使用
DISTINCT
同时对子查询中的行进行排序特别便宜。 - 应用
WHERE
条件AND tmp.stkid > 0
PERFORM s.id
FROM stack s
JOIN (
SELECT DISTINCT stkid
FROM tmp_push_bulk
WHERE stkid > 0
) tmp ON tmp.stkid = s.id
ORDER BY 1
FOR UPDATE OF s;
和:
PERFORM s.id
FROM stack s
JOIN (
SELECT DISTINCT id
FROM tmp_renew_stk
) tmp ON tmp.id = s.referer
ORDER BY 1
FOR UPDATE OF s;