PostgreSQL批量删除的锁行为及死锁规避问题咨询

核心结论 #

PostgreSQL的批量DELETE不会原子性地给所有匹配行加锁，而是逐行扫描并依次获取锁——这正是它和并行运行的INSERT ON CONFLICT产生死锁的核心原因。

锁机制细节 #

1. 批量DELETE的锁行为 #

执行DELETE FROM table WHERE condition时，PostgreSQL会按照执行计划的扫描顺序（比如索引顺序、全表物理存储顺序）遍历匹配行，每找到一行就立即加上ROW EXCLUSIVE锁（这和INSERT ON CONFLICT DO UPDATE操作冲突行时的锁类型一致），直到所有目标行都被加锁。

这个过程是渐进式的，锁的获取顺序完全由执行计划决定，若不主动干预，很可能和你的INSERT ON CONFLICT锁顺序产生交叉。

2. INSERT ON CONFLICT的锁行为 #

你已经通过对插入行排序避免了INSERT之间的死锁——当INSERT ON CONFLICT触发UPDATE分支时，会按照你指定的排序顺序（比如主键ID升序）依次尝试获取冲突行的ROW EXCLUSIVE锁，不会出现交叉等待。

死锁触发场景 #

假设：

• DELETE语句的执行计划是按主键ID从大到小扫描并加锁（比如用了反向索引）
• 你的INSERT ON CONFLICT是按ID从小到大尝试获取锁

此时就可能出现：

1. DELETE先拿到ID=100的锁，等待ID=1的锁
2. 同时INSERT ON CONFLICT先拿到ID=1的锁，等待ID=100的锁
   双方互相等待对方的锁，死锁触发。

规避死锁的具体方案 #

1. 强制DELETE的锁获取顺序与INSERT一致 #

在DELETE语句中显式指定排序并加锁，确保锁的获取顺序和你INSERT ON CONFLICT的排序完全匹配。比如你INSERT时按ID升序，那么：

DELETE FROM table 
WHERE condition
ORDER BY id ASC
FOR UPDATE;

ORDER BY id ASC FOR UPDATE会强制PostgreSQL按ID升序遍历并加锁，和你的INSERT锁顺序对齐，从根源避免交叉等待。

2. 拆分大DELETE为小批量 #

如果要删除的行数极多，一次性删除会持有大量锁且耗时久，建议拆分为多个小批量事务，比如每次删1000行：

-- 循环执行直到无数据删除
DELETE FROM table 
WHERE condition
ORDER BY id ASC
LIMIT 1000;

小批量操作能缩短锁的持有时间，降低并发冲突的概率。

3. 控制事务时长 #

确保DELETE操作在短事务中执行，避免长时间持有锁——不要在DELETE前后执行无关的耗时操作，减少锁的冲突窗口。

验证方法 #

可以通过查询pg_locks视图观察锁的持有和等待情况，确认锁的获取顺序是否符合预期：

SELECT locktype, relation::regclass, pid, mode, granted
FROM pg_locks
WHERE relation = 'your_table'::regclass;

也可以在测试环境模拟并发场景，同时运行DELETE和INSERT ON CONFLICT，验证调整后的语句是否能避免死锁。

内容的提问来源于stack exchange，提问作者Heyjojo