Oracle数据库多报表程序并行执行冲突解决技术问询

针对你遇到的并发报表生成时因表被截断/填充导致的报错问题，核心思路是让第二个报表任务等待第一个任务的所有存储过程执行完成后再启动，以下是几种实用的Oracle-native解决方案：

方案1：使用DBMS_LOCK实现自定义互斥锁 #

Oracle的DBMS_LOCK包可以创建用户自定义锁，用来精准控制任务的串行执行：

1. 先确保权限
   给执行报表的数据库用户授予DBMS_LOCK的执行权限：

   GRANT EXECUTE ON DBMS_LOCK TO your_report_user;
   

2. 在报表任务中添加锁逻辑

   • 第一个报表任务启动时，先获取独占锁：

     DECLARE
       v_lock_handle VARCHAR2(128);
     BEGIN
       -- 分配一个唯一锁标识，用报表组的名称来区分，比如'CORE_REPORT_LOCK'
       DBMS_LOCK.ALLOCATE_UNIQUE('CORE_REPORT_LOCK', v_lock_handle);
       -- 请求排他锁，0表示立即返回（也可以设为-1表示无限等待）
       IF DBMS_LOCK.REQUEST(v_lock_handle, DBMS_LOCK.X_MODE, 0, TRUE) = 0 THEN
         -- 执行你的报表存储过程（包含truncate、填充逻辑）
         exec_first_report_procs();
         -- 任务完成后主动释放锁
         DBMS_LOCK.RELEASE(v_lock_handle);
       ELSE
         -- 锁被占用时抛出提示
         RAISE_APPLICATION_ERROR(-20001, '已有报表任务在运行，请稍后重试');
       END IF;
     EXCEPTION
       WHEN OTHERS THEN
         -- 异常时也要释放锁，避免死锁
         DBMS_LOCK.RELEASE(v_lock_handle);
         RAISE;
     END;
     

   • 第二个报表任务使用完全相同的锁逻辑，只有当第一个任务释放锁后，才能获取到锁并执行。

优点：无需额外建表，直接利用Oracle内置机制，锁粒度可控；缺点：需要用户有DBMS_LOCK权限，需注意异常场景下的锁释放。

方案2：用状态表跟踪任务执行状态 #

创建一个轻量状态表，让第二个任务通过轮询等待第一个任务完成：

1. 创建状态表

   CREATE TABLE REPORT_TASK_STATUS (
     TASK_GROUP_ID VARCHAR2(50) PRIMARY KEY,
     RUN_STATUS VARCHAR2(20) CHECK (RUN_STATUS IN ('RUNNING', 'COMPLETED', 'FAILED')),
     START_TIME TIMESTAMP DEFAULT SYSTIMESTAMP,
     END_TIME TIMESTAMP
   );
   

2. 修改报表任务逻辑

   • 第一个报表任务启动时：

     BEGIN
       -- 标记任务组为运行中（用MERGE避免重复插入）
       MERGE INTO REPORT_TASK_STATUS t
       USING (SELECT 'REPORT_SET_AB' AS TASK_GROUP_ID FROM DUAL) s
       ON (t.TASK_GROUP_ID = s.TASK_GROUP_ID)
       WHEN MATCHED THEN UPDATE SET RUN_STATUS = 'RUNNING', START_TIME = SYSTIMESTAMP
       WHEN NOT MATCHED THEN INSERT (TASK_GROUP_ID, RUN_STATUS) VALUES ('REPORT_SET_AB', 'RUNNING');
       
       -- 执行所有关联的存储过程
       exec_report_a_proc();
       exec_report_a_aux_proc();
       
       -- 标记任务完成
       UPDATE REPORT_TASK_STATUS SET RUN_STATUS = 'COMPLETED', END_TIME = SYSTIMESTAMP WHERE TASK_GROUP_ID = 'REPORT_SET_AB';
       COMMIT;
     EXCEPTION
       WHEN OTHERS THEN
         -- 异常时标记任务失败
         UPDATE REPORT_TASK_STATUS SET RUN_STATUS = 'FAILED', END_TIME = SYSTIMESTAMP WHERE TASK_GROUP_ID = 'REPORT_SET_AB';
         COMMIT;
         RAISE;
     END;
     

   • 第二个报表任务启动前检查状态：

     DECLARE
       v_current_status VARCHAR2(20);
     BEGIN
       -- 循环检查，直到第一个任务完成或失败
       LOOP
         SELECT RUN_STATUS INTO v_current_status FROM REPORT_TASK_STATUS WHERE TASK_GROUP_ID = 'REPORT_SET_AB';
         IF v_current_status IN ('COMPLETED', 'FAILED') THEN
           EXIT;
         END IF;
         -- 每5秒重试一次（可根据业务调整间隔）
         DBMS_LOCK.SLEEP(5);
       END LOOP;
       
       -- 执行第二个报表任务
       exec_report_b_proc();
     END;
     

优点：直观易维护，适合程序端和数据库端结合控制；缺点：需要维护状态表，轮询会消耗少量资源（可通过调整等待间隔优化）。

方案3：使用DBMS_SCHEDULER设置任务依赖 #

如果你的报表是通过Oracle调度器执行的，可以直接设置任务间的依赖关系：

1. 创建第一个报表任务

   BEGIN
     DBMS_SCHEDULER.CREATE_JOB(
       JOB_NAME        => 'REPORT_A_JOB',
       JOB_TYPE        => 'STORED_PROCEDURE',
       JOB_ACTION      => 'exec_first_report_procs',
       START_DATE      => SYSTIMESTAMP,
       ENABLED         => TRUE,
       AUTO_DROP       => FALSE
     );
   END;
   

2. 创建第二个报表任务并绑定依赖

   BEGIN
     DBMS_SCHEDULER.CREATE_JOB(
       JOB_NAME        => 'REPORT_B_JOB',
       JOB_TYPE        => 'STORED_PROCEDURE',
       JOB_ACTION      => 'exec_report_b_proc',
       START_DATE      => SYSTIMESTAMP,
       ENABLED         => TRUE,
       AUTO_DROP       => FALSE
     );
     -- 设置依赖：只有第一个任务成功完成后，第二个任务才会触发
     DBMS_SCHEDULER.ADD_JOB_DEPENDENCY(
       JOB_NAME         => 'REPORT_B_JOB',
       DEPENDENT_JOB_NAME => 'REPORT_A_JOB',
       DEPENDENCY_TYPE => 'SUCCESSFUL'
     );
   END;
   

优点：完全利用Oracle调度器原生能力，无需额外业务代码；缺点：更适合定时/批量调度场景，手动触发的报表需要调整触发逻辑。

方案4：锁定共享业务表 #

如果冲突仅来自那几个被truncate/填充的表，可以直接在第一个任务执行时锁定这些表，阻止其他会话操作：

BEGIN
  -- 对所有涉及的表加排他锁，会阻止其他会话的DDL（如truncate）和DML操作
  LOCK TABLE report_data_1, report_data_2, report_aux IN EXCLUSIVE MODE;
  
  -- 执行报表存储过程
  exec_first_report_procs();
  
  -- 提交事务后自动释放锁
  COMMIT;
END;

第二个报表任务在执行前也会尝试锁定这些表，会自动等待第一个任务的锁释放后再继续。

优点：逻辑简单直接，针对冲突资源精准锁定；缺点：锁范围较大，如果这些表还有其他业务操作，会影响整体并发性能。

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