PostgreSQL中删除操作触发Tuple Lock问题排查与解决

为什么会产生元组锁？ #

1. 事务锁持有时间过长：PostgreSQL中，DELETE操作会对目标元组加排他锁（ExclusiveLock），且锁会持有到事务提交或回滚。如果应用执行删除后未及时提交事务，频繁的删除操作会积累大量未释放的锁。
2. 触发器逻辑的锁叠加：你的AFTER FOR EACH ROW触发器会在主表daily_shifts_tab的每行删除后，对daily_shifts_wfv执行DELETE操作。该操作同样会对daily_shifts_wfv的匹配元组加排他锁，若并发请求较多，锁等待会持续积累。
3. 删除条件不精准：应用执行的删除语句delete from daily_shifts_tab where OBJECTID = $1;仅用OBJECTID作为条件，而表的主键是(id,objectid)，这意味着该语句可能匹配多行数据，每行都会触发一次触发器，进一步增加锁的数量和持有时间。
4. 触发器DELETE条件的潜在问题：触发器中删除daily_shifts_wfv的条件使用current_setting('ctx_ng_vpd.ctx_id_fil')获取id，而非直接使用old.id（主表被删行的id）。若current_setting返回的id与old.id不一致，可能导致匹配到错误的元组甚至多行元组，从而锁定更多不必要的数据。

解决方法 #

1. 精准定位删除行，减少触发器触发次数 #

修改应用的删除语句，带上主键的两个字段，确保每次只删除一行：

delete from daily_shifts_tab where id = $1 and OBJECTID = $2;

这样不仅能避免一次删除多行导致的多次触发器调用，还能让数据库更快定位目标行，缩短锁的持有时间。

2. 优化触发器的DELETE逻辑 #

将触发器中删除daily_shifts_wfv的条件改为直接使用主表被删行的主键值，无需依赖外部上下文变量，确保精准匹配唯一元组：

elsif (TG_OP = 'DELETE') then       
    delete from daily_shifts_wfv 
    where id = old.id 
    and OBJECTID = old.OBJECTID;
    RETURN OLD;
end if;

此修改能避免因current_setting导致的匹配错误，同时让数据库通过主键快速定位目标行，减少锁的持有时间。

3. 及时提交事务 #

确保应用在执行删除操作后立即提交事务，避免长时间持有锁。如果应用使用了事务批量操作，合理控制批量大小，避免事务持续时间过长。

4. 排查长事务与锁状态 #

使用以下SQL查询当前持有排他锁的事务，定位长时间占用锁的会话：

SELECT 
    locktype,
    relation::regclass,
    pid,
    mode,
    granted,
    query
FROM pg_locks 
WHERE mode = 'ExclusiveLock' 
AND relation IN ('daily_shifts_tab'::regclass, 'daily_shifts_wfv'::regclass);

针对查询到的长事务，优化应用逻辑，缩短事务执行时间。

5. 考虑批量同步替代行级触发器 #

如果删除操作非常频繁，可考虑用定时任务或批量同步脚本替代行级触发器，减少每次删除的锁开销。例如，定期将daily_shifts_tab中已删除的数据同步到daily_shifts_wfv，而非每行触发一次同步。

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