Oracle 检查行被跳过更新跳过锁定

嗨，汤姆，

SP的逻辑是一次返回一个批处理ID给应用程序进行处理，如果某个实例选择了该批处理，则不应将相同的批处理ID返回到其他实例。并且应用程序服务器处于多集群环境中。

我试图锁定一个批次内部循环，如果该批次被其他实例锁定，则跳过该批次并尝试其他批次。

所以这里真正的问题是当批处理已经被其他实例锁定时，它直接进入NO_DATA_FOUND异常块并退出循环。因此，我们无法尝试下一个待处理批次。

Belo SP代码:

PROCEDURE getNextBatch(
  i_host_name       IN VARCHAR2, 
  i_instance_name   IN VARCHAR2, 
  o_batch_id        OUT VARCHAR2, 
  o_request_info    OUR SYS_REFCURSOR, 
  o_error_code      OUT VARCHAR2, 
  o_error_message   OUT VARCHAR2) 
IS 
  v_batch_id batch_queue.batch_id%TYPE;     
  PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN
  -- Loop is to try to lock a batch,  exits the loop unless a batch is locked or no pending batch to return
  LOOP
    -- Business logic(Batching SLA, Batch request count and Sorting for FIFO logic) query to a identify batch qualified for processing.
      BEGIN
        SELECT batch_id
          INTO v_batch_id
        FROM ( SELECT ROW_NUMBER() OVER( ORDER BY created_dttm ASC) row_num,
               COUNT(request_id) OVER( PARTITION BY batch_id) batch_count,
                 ...... 
               FROM batch_queue b
               INNER JOIN request r
               ON ( r.batch_id = r.batch_id)
                 ...
               WHERE status ='PND'
             )
        WHERE row_num <= 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
      END;

      -- Try to lock the identified batch here     
      BEGIN
        SELECT batch_id
          INTO o_batch_id
        FROM batch_queue
        WHERE status='PND'
          AND batch_id = v_batch_id
        FOR UPDATE of status SKIP LOCKED;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          COMMIT;
        EXIT;
      END;
      -- Update batch status to pending   
      IF o_batch_id IS NOT NULL THEN
        UPDATE batch_queue SET status='INP'
        WHERE batch_id = o_batch_id;
        COMMIT;
        EXIT;
      END IF;
  END LOOP;

  -- Retrun the output cursor 
  OPEN o_request_info FOR
  SELECT ...
  FROM ..
  WHERE batch_id = o_batch_id;
  
  o_error_code := NULL;
  o_error_message := NULL;
EXCEPTION
  WHEN user_defined_exception TEHN
    ROLLBACK;
    -- log the errors and return the error code and message
    o_error_code := SQLCODE;
    o_error_message := SQLERRM;
  WHEN OTHERS THEN
    ROLLBACK;
    -- log the errors and return the error code and message  
    o_error_code := SQLCODE;
    o_error_message := SQLERRM;
END getNextBatch;
复制

注意:-我们也正在考虑使用oracle AQ

谢谢你的帮助。

巴鲁

你退出循环是因为...

你的异常处理程序有退出!

这将您从循环中转储:

begin
  for i in 1 .. 10 loop
    begin 
      dbms_output.put_line('running ' || i );
      raise no_data_found;
    exception
      when no_data_found then 
        exit;
    end;
  end loop;
  dbms_output.put_line('done');
end;
/
running 1
done
复制

为避免这种情况，请删除出口:

begin
  for i in 1 .. 10 loop
    begin 
      dbms_output.put_line('running ' || i );
      raise no_data_found;
    exception
      when no_data_found then 
        null;
    end;
  end loop;
  dbms_output.put_line('done');
end;
/
running 1
running 2
running 3
running 4
running 5
running 6
running 7
running 8
running 9
running 10
done
复制

但是，如果您希望以FIFO的方式处理批处理，则只能保证只有一个过程会接收它们，那么使用AQ是必经之路。