Oracle同会话未提交数据无法被带自治事务的存储函数读取问题

你的猜测完全成立，问题直接由存储函数声明的PRAGMA AUTONOMOUS_TRANSACTION（自治事务）导致：

• Oracle的自治事务会将当前子程序（此处为你的存储函数）声明为完全独立于父会话事务的独立事务，和触发它的主会话事务完全隔离，二者的未提交修改互相不可见。
• 主会话未提交的插入数据，仅对当前主会话的普通执行上下文开放，同会话触发的自治事务属于独立事务上下文，只能读取到已经全局提交的数据，自然无法获取到主会话未提交的新增记录。
• 你测试移除自治事务声明后功能恢复正常，刚好验证了上述逻辑：移除后函数和主会话属于同一事务上下文，可正常读取同事务内未提交的修改。

你提到自治事务仅用于向其他无关表写入数据，可根据业务场景选择以下方案：

• 方案一：拆分逻辑，将需要写入其他表的自治事务逻辑抽离为独立的自治子程序，主查询函数不声明自治事务，仅在需要写入其他表时调用该独立自治子程序。既保留了写入其他表的独立事务能力，又保证查询逻辑和主会话同上下文，可读取未提交数据。
• 方案二：如果业务允许破坏原有事务原子性，可在调用查询函数前先提交主会话的插入操作，自治事务即可读取到已提交的新增数据，该方案不推荐对事务一致性要求高的业务使用。
• 方案三：如果写入其他表的操作不需要和主事务隔离（不需要独立提交/回滚），直接移除自治事务声明即可，这也是你已经验证过的可行方案。

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