PostgreSQL中带USING的DELETE语句锁类型与性能优化咨询

1. USING子句的锁限制问题 #

DELETE ... USING 不会带来额外锁限制：

• 被删除的表t1会正常获取ROW EXCLUSIVE锁（和普通DELETE一致），该锁会阻塞其他对t1的写操作（UPDATE/DELETE/INSERT），但不阻塞读操作。
• USING子句中的t2仅会获取ACCESS SHARE锁（普通SELECT的锁级别），这个锁不会阻塞其他读操作，仅会阻塞对t2的排他锁请求（比如ALTER TABLE、DROP TABLE这类操作）。
  简言之，USING子句只是将t2作为关联查询的数据源，不会给t2加额外排他锁，和单独查询t2的锁级别一致。

2. 拆分语句是否更优？ #

拆分（比如先查询要删除的t1主键，再批量DELETE）在多数场景下没有优势，反而可能引发问题：

• 原语句是原子操作，能保证数据一致性；拆分后两次查询之间，若有其他事务修改t1或t2的数据，可能导致删除结果不准确（漏删或误删）。
• 若要删除的数据集很大，拆分后需将所有主键加载到应用内存，反而增加内存开销；而原语句是数据库内部关联，执行效率更高。
  你测试未发现差异，说明当前数据量较小，两种方式的性能差距被掩盖，但从一致性和长期扩展性来看，保留原语句更稳妥。

3. 性能优化建议 #

针对预订系统的高性能需求，可从以下方向优化：

• 添加复合索引：给t1创建(attr, updated)复合索引，给t2也创建(attr, updated)复合索引。PostgreSQL能快速定位符合条件的关联行，避免全表扫描，这是最有效的优化手段。
• 分批删除：若单次删除行数极多（上万条级别），不要一次性执行DELETE，改用LIMIT分批处理，示例：

  WHILE EXISTS (
    SELECT 1 FROM table t1 USING table t2 WHERE t1.updated < t2.updated AND t1.attr = t2.attr
  ) LOOP
    DELETE FROM table t1 USING table t2 
    WHERE t1.updated < t2.updated AND t1.attr = t2.attr
    LIMIT 1000;
    COMMIT;
  END LOOP;
  

  此举能缩短单事务执行时间，避免长时间持有锁，降低对并发业务的影响。
• 定期分析表：执行ANALYZE table;更新表统计信息，让PostgreSQL生成更优的查询计划。
• 调整事务隔离级别：若业务允许，将事务隔离级别从可重复读调整为读已提交，可减少锁的持有时间，降低锁等待概率。
• 排查锁等待：用SELECT * FROM pg_locks WHERE NOT granted;查看是否存在锁等待，比如是否有其他事务长时间占用t1或t2的锁，导致当前DELETE阻塞。
• 考虑分区表：若t1和t2数据量极大（千万级以上），可按attr或updated字段做分区，删除时直接删除对应分区，效率远高于逐行删除。

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