管理PL/SQL作业以避免大表死锁问题

1. 让作业串行执行（等待前一个完成再启动） #

如果不需要并行能力，强制串行是最直接的死锁规避方式，可通过以下几种方法实现：

• 用DBMS_LOCK手动控制串行：在循环中通过自定义锁确保前一个作业完成后，下一个才会启动。示例代码：

  DECLARE
    l_lock_handle VARCHAR2(128);
  BEGIN
    DBMS_LOCK.ALLOCATE_UNIQUE('JOB_SERIAL_LOCK', l_lock_handle);
    
    FOR i IN 1..your_param_count LOOP
      -- 请求排他锁，等待直到获取
      DBMS_LOCK.REQUEST(l_lock_handle, DBMS_LOCK.X_MODE, 0, TRUE);
      
      -- 提交当前作业
      DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'BRANCH_JOB_' || i,
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN your_procedure(' || i || '); END;',
        enabled => TRUE,
        auto_drop => TRUE
      );
      
      -- 轮询等待作业完成
      WHILE DBMS_SCHEDULER.JOB_STATUS('BRANCH_JOB_' || i) != 'SUCCEEDED' LOOP
        DBMS_LOCK.SLEEP(10); -- 每10秒检查一次状态
      END LOOP;
      
      -- 释放锁，允许下一个作业启动
      DBMS_LOCK.RELEASE(l_lock_handle);
    END LOOP;
  END;
  /
  

• 使用DBMS_SCHEDULER作业链：创建一个调度链，将每个作业设为前一个作业的后继节点，让Oracle调度器自动控制串行执行流程。
• 循环内同步等待：在触发作业的循环中，直接等待前一个作业的状态变更（完成/失败），确认后再提交下一个作业。

2. 并行执行同时避免死锁 #

并行执行的核心是让不同作业的操作范围完全隔离，从根源上消除锁冲突：

• 按分区拆分作业：如果大表是分区表，让每个作业单独处理一个分区（比如按日期、业务ID哈希分区），分区级别的锁不会互相干扰，也不会升级为表锁。
• 严格限定数据操作范围：不管表是否分区，每个作业只操作特定条件的行（比如WHERE branch_id = :param），确保不同作业的WHERE条件无重叠，仅获取行级锁而非表锁。
• 优化SQL缩短锁持有时间：
  • 用MERGE语句替代分开的INSERT和UPDATE，减少两次操作的锁冲突概率。
  • 批量操作时分批次提交，避免一次性处理百万级数据导致长时间持有锁。
  • 移除事务内不必要的查询、等待逻辑，让事务快速完成。
• 强制行级锁：执行ALTER TABLE your_large_table STORAGE (ROW_LOCKING = ALWAYS);，禁止Oracle将大量行级锁自动升级为表锁。

3. 通用死锁规避策略 #

• 统一事务操作顺序：所有作业遵循完全一致的操作顺序，比如先执行INSERT再执行UPDATE，或按主键从小到大的顺序更新行，避免交叉锁引发死锁。
• 监控锁状态：定期查询V$LOCK、V$SESSION_WAIT和DBA_DEADLOCKS视图，定位死锁的具体对象和SQL，针对性优化。
• 使用默认隔离级别：保持Oracle默认的READ COMMITTED隔离级别，不要使用SERIALIZABLE，后者更容易引发锁冲突。
• 拆分长事务：将大操作拆分为多个小事务，每处理一部分数据就提交一次，减少锁的持有时间。
• 乐观锁机制：给大表添加版本号字段（如version NUMBER），更新时用WHERE ... AND version = :current_version，若检测到冲突则重试，避免长时间持有锁。

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