如何排查存储过程及触发器内获取锁的耗时与产生原因

实时锁信息获取方法 #

PostgreSQL内置系统视图可以直接查询当前所有锁的持有、等待状态，AWS RDS默认开放这些视图的访问权限：

• 目标表锁详情查询
  你可以在触发器慢查询执行的同时运行以下SQL，直接确认是否存在锁冲突，以及冲突锁的持有方信息：

SELECT 
  pid,
  locktype,
  relation::regclass AS table_name,
  mode,
  granted,
  now() - xact_start AS txn_duration,
  query AS related_query
FROM pg_locks l
JOIN pg_stat_activity psa ON l.pid = psa.pid
WHERE relation = '你的目标表名'::regclass;

返回结果中granted = false的行即为正在等待锁的事务，对应mode字段为事务申请的锁类型。

• 自动记录锁等待日志
  在RDS参数组中将log_lock_waits参数设置为on，只要锁等待时间超过deadlock_timeout（默认1秒，刚好覆盖你遇到的1.4秒延迟场景），系统就会自动将锁等待的完整信息写入数据库日志，你可以直接在RDS控制台下载日志查看，无需修改业务代码。

触发器内语句执行耗时排查方法 #

针对你无法在触发器函数内使用EXPLAIN复现问题的场景，可以通过auto_explain扩展捕获触发器内部语句的实际执行计划：

1. 修改RDS参数组配置：
   • 在shared_preload_libraries参数中追加auto_explain（注意不要覆盖原有值，多个值用英文逗号分隔即可）
   • auto_explain.log_min_duration设置为100（单位为毫秒，所有执行耗时超过100ms的语句都会记录执行计划）
   • auto_explain.log_triggers设置为on（开启触发器内部语句的记录能力）
   • auto_explain.log_analyze设置为on（记录语句实际运行阶段的耗时、扫描行数等明细数据）
2. 重启RDS实例使配置生效后，所有符合阈值的触发器内部语句的执行计划都会写入日志，你可以直接看到UPDATE语句的耗时具体分布在哪个阶段，确认是否真的卡在锁等待环节。

高概率隐蔽问题排查点 #

你提到几乎没有其他事务运行，可以优先排查以下容易被忽略的场景：

• 长事务持锁：部分事务可能执行完查询后未提交，长期持有行锁，可通过以下SQL查询运行时长超过10秒的活跃事务：

SELECT pid, now() - xact_start AS xact_duration, query, state
FROM pg_stat_activity
WHERE state <> 'idle' AND now() - xact_start > interval '10s';

• 外键隐式锁：如果目标表存在外键关联其他表，UPDATE执行时会自动给关联表对应行加共享锁，若关联表存在未提交的修改也会触发锁等待，你可以去掉锁查询SQL中的表名过滤条件，查询所有活跃锁确认。
• 同事务自等待：如果触发该触发器的操作属于包含多条DML的大事务，事务内前面的语句可能已经持有目标表的冲突锁，导致后续UPDATE出现自等待，你可以将触发触发器的操作单独放在空事务中执行验证。

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