PostgreSQL 11创建视图时触发[40P01]死锁问题求助

死锁原因分析 #

你的场景中死锁的核心原因是表替换与视图重建在同一个事务中执行，导致锁的循环等待：

1. 存储过程事务（事务A）在步骤3中对旧表加了ACCESS EXCLUSIVE锁，且该锁会持有到整个事务结束（包括后续视图重建步骤）。
2. 在步骤3完成、视图重建未开始的窗口内，用户查询事务（事务B）访问视图时，会尝试访问旧表（视图仍依赖旧表的OID），因此需要获取旧表的ACCESS SHARE锁，进入等待事务A的状态。
3. 事务A接下来执行CREATE OR REPLACE VIEW，需要获取视图的ACCESS EXCLUSIVE锁，但此时事务B已持有视图的ACCESS SHARE锁，事务A进入等待事务B的状态。
4. 两个事务互相等待对方释放锁，触发死锁检测。

解决建议 #

1. 拆分事务（优先推荐） #

将表替换操作与视图重建操作拆分为两个独立事务，彻底切断锁的循环依赖：

• 第一个事务：完成临时表存数据、新表创建、旧表锁定与重命名操作，执行COMMIT释放旧表的锁。
• 第二个事务：执行视图重建逻辑，完成后COMMIT。

这样拆分后，用户查询视图时即使访问旧表，也不会因为锁等待阻塞；重建视图时仅需等待用户释放视图的锁，不会形成循环等待，从根源避免死锁。

2. 提前锁定目标视图（适合必须单事务的场景） #

如果业务要求必须在单个事务内完成所有操作，可在步骤3开始前，提前锁定所有需要重建的视图，阻止用户事务获取视图的共享锁：

-- 在步骤3前添加视图锁定逻辑
for _lock_view_rec in select schemaname, viewname from temp_views
loop
    execute format('LOCK VIEW %I.%I IN ACCESS EXCLUSIVE MODE', 
                   _lock_view_rec.schemaname, _lock_view_rec.viewname);
end loop;

注：这里使用format()函数避免SQL注入风险，替代直接字符串拼接。
此方法会在整个事务期间阻塞用户对视图的访问，需根据业务容忍度评估使用。

3. 优化视图重建顺序 #

如果视图之间存在依赖关系（如视图A依赖视图B），需按先重建被依赖视图、再重建依赖视图的顺序执行，避免重建过程中出现不必要的锁等待或对象不存在错误。

4. 配置锁超时（辅助缓解） #

在存储过程开头添加锁超时设置，减少死锁等待的影响范围：

SET LOCAL lock_timeout = '5s'; -- 可根据业务调整超时时间

该设置仅会让锁等待超时的事务报错退出，无法解决死锁根源，建议配合其他方案使用。

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