Oracle递归存储过程插入行无法即时更新问题求助

我在Oracle包中编写了递归存储过程TRAVERSE_TRACE，用于从网络顶端向下遍历，递归结束时向上返回值，返回过程中会将步骤结果插入输出表，偶尔还会更新下游递归已插入的记录。

存储过程逻辑：

1. 查找下游连接节点
2. 为每个下游节点调用TRAVERSE_TRACE
3. 可选更新其下方的输出表条目
4. 将当前节点插入输出表

执行完成后，输出表能看到插入的数据，但UPDATE语句设置的值全部不生效。通过DBMS日志发现，UPDATE的WHERE子句返回0条记录，尽管对应的插入操作已提前执行。日志显示待更新行已插入，但查询无结果，手动执行UPDATE语句则正常。

请问为何插入的行无法即时可见？如何强制插入生效或延迟更新？

相关代码片段 #

-- if the current feature is isolating, use the current downstream ISOs as the downstream ISOs for this area. If not then leave as null
IF v_IsolatingFeature = 1 THEN
    v_downstreamIsolators := v_downstreamIsolatorsStaging;
    v_downstreamIsolatorsStaging := G3E_FID;
    
    UPDATE HEDLPROD.ISOLATION_AREA_ASSETS IA
    SET IA.DOWNSTREAM_ISO_FIDS = to_char(v_downstreamIsolators)
    WHERE IA.UPSTREAM_ISO_FID = to_number(G3E_FID);
    COMMIT;
    
END IF;

-- Insert the current entry into the results table
Insert Into HEDLPROD.ISOLATION_AREA_ASSETS (FEEDERHEAD_FID, FEEDER, G3E_FID, G3E_FNO, UPSTREAM_ISO_FID, UPSTREAM_ASSET_FID, UPSTREAM_ISOLATORS, DOWNSTREAM_ISO_FIDS, DEPTH)
VALUES (v_feederHeadFid, FEEDER, G3E_FID, G3E_FNO, v_currentIsolatorFid, upstreamAssetFid, v_upstreamIsolatorFids,v_downstreamIsolators, v_depthCount);
COMMIT;

日志信息 #

Rows Inserted into output table = 1. ID: 16312200, UPSTREAM_ID: 16309677
Rows Inserted into output table = 1. ID: 16309676, UPSTREAM_ID: 16309677
Number of rows where UPSTREAM_ID = 16309677 is: 0
Updating DOWNSTREAM_ISO_FIDS to 16312200 where UPSTREAM_ID = 16309677
Rows updated = 0. ID: 16309677

原因分析 #

这是Oracle默认的READ COMMITTED隔离级别导致的：

• 递归调用的子过程执行INSERT后提交，但父过程的事务看不到子事务提交的新行——因为父事务启动时这些行还不存在，READ COMMITTED级别下只能读取事务启动前已提交的数据。
• 每个递归调用都是独立事务（过程中多次执行COMMIT），父事务无法读取子事务提交的数据，直到自身提交或结束。
• 手动执行UPDATE时，会话已脱离原父事务，自然能看到所有已提交的数据。

解决办法 #

1. 移除过程内COMMIT，采用单事务执行 #

把过程中所有COMMIT语句删除，在调用TRAVERSE_TRACE的外部统一提交。整个递归过程属于同一个事务，所有INSERT和UPDATE操作在事务内互相可见。

修改后代码片段：

IF v_IsolatingFeature = 1 THEN
    v_downstreamIsolators := v_downstreamIsolatorsStaging;
    v_downstreamIsolatorsStaging := G3E_FID;
    
    UPDATE HEDLPROD.ISOLATION_AREA_ASSETS IA
    SET IA.DOWNSTREAM_ISO_FIDS = to_char(v_downstreamIsolators)
    WHERE IA.UPSTREAM_ISO_FID = to_number(G3E_FID);
    -- 移除此处COMMIT
END IF;

Insert Into HEDLPROD.ISOLATION_AREA_ASSETS (...) VALUES (...);
-- 移除此处COMMIT

调用存储过程时统一提交：

BEGIN
    TRAVERSE_TRACE(...);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

2. 使用自治事务（谨慎选择） #

如果必须保留独立事务（比如需要即时释放锁），可将INSERT操作放在自治事务中，这样父事务能立即读取到自治事务提交的数据。但自治事务会增加逻辑复杂度，需谨慎使用。

示例：

CREATE OR REPLACE PROCEDURE INSERT_ASSET(
    p_feederHeadFid IN NUMBER,
    p_feeder IN VARCHAR2,
    p_G3E_FID IN NUMBER,
    -- 其他参数...
) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    Insert Into HEDLPROD.ISOLATION_AREA_ASSETS (...) VALUES (...);
    COMMIT;
END;
/

在原过程中调用该自治事务过程替代直接INSERT即可。

3. 延迟更新至遍历完成后 #

如果业务允许，可先将需要更新的记录信息暂存在内存集合中，等整个递归遍历完成后，再批量执行UPDATE操作，避免跨事务读取的问题。

示例：

• 在包中定义集合类型：

TYPE Update_Rec IS RECORD (
    upstream_iso_fid NUMBER,
    downstream_iso_fids VARCHAR2(2000)
);
TYPE Update_List IS TABLE OF Update_Rec;
g_updates Update_List := Update_List();

• 递归过程中记录更新信息：

IF v_IsolatingFeature = 1 THEN
    v_downstreamIsolators := v_downstreamIsolatorsStaging;
    v_downstreamIsolatorsStaging := G3E_FID;
    
    g_updates.EXTEND;
    g_updates(g_updates.LAST).upstream_iso_fid := to_number(G3E_FID);
    g_updates(g_updates.LAST).downstream_iso_fids := to_char(v_downstreamIsolators);
END IF;

• 遍历完成后批量更新：

BEGIN
    TRAVERSE_TRACE(...);
    FORALL i IN g_updates.FIRST..g_updates.LAST
        UPDATE HEDLPROD.ISOLATION_AREA_ASSETS IA
        SET IA.DOWNSTREAM_ISO_FIDS = g_updates(i).downstream_iso_fids
        WHERE IA.UPSTREAM_ISO_FID = g_updates(i).upstream_iso_fid;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

内容的提问来源于stack exchange，提问作者David Klein Ovink