You Asked

Hi Tom,

Logic of the SP is to return one Batch ID at a time to application for processing and same Batch ID should not be returned back to other instance if that batch is picked by an instance. And the application server is in a multi clustered environment.

I was trying lock a batch inside loop and if that batch was locked by other instance than skip that batch and try for other one.

So real issue here is when batch is locked already by other instance, it directly goes to NO_DATA_FOUND exception block and exits the loop. So we are not able to try for next pending batch.

Belo SP code:

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;

Note:- Also we are under consideration to use oracle AQ

Thank you for you help.

Balu

and Chris said...

You're exiting the loop because...

You've got EXIT in your exception handlers!

This dumps you out of the loop:

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

To avoid this, remove the exits:

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

But if you're looking to process batches in FIFO manner, guaranteeing only one process will pick them up, then using AQ is the way to go.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Balu Kuppusamy, March 02, 2018 - 12:30 pm UTC

May be I'm not clear on what is my question.

The question is why does it goes to NO_DATA_FOUND exception block when concurrent access happened, even though a row was found. ie, In session1: A batch id(1234) is locked and simultaneously in Session2: trying to lock the same batch id(1234) and skips the row.

My assumption was SKIP LOCKED would skip that row and it would go to outer block execution. Instead it goes to NO_DATA_FOUND exception block when dead lock happened. Is this expected behavior?
BEGIN
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;
END;

Thank you

March 02, 2018 - 4:38 pm UTC

I'm not sure I follow...

But if you use skip locked and someone else has already locked the row, the query returns nothing. Thus you get no data found.

So yes, this is expected.

No

Balu, March 03, 2018 - 4:46 am UTC

I appreciate your work on Oracle questions.

It does not go to NO_DATA_FOUND when I use NOWAIT instead of SKIP LOCKED.

March 05, 2018 - 3:55 pm UTC

No?! What do you mean no?

If you're switched SKIP LOCKED => NOWAIT, you'll get a different error:

create table t (
  x int
);
insert into t values (1);
commit;

Session 1

select * from t for update;

Session 2

declare
  vx t.x%type;
begin
  loop
    select x into vx from t
    for update skip locked;
  end loop;
  dbms_output.put_line('done');
end;
/

ORA-01403: no data found

declare
  vx t.x%type;
begin
  loop
    select x into vx from t
    for update nowait;
  end loop;
  dbms_output.put_line('done');
end;
/

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

No

More to Explore

PL/SQL demos

PL/SQL docs

Check row was skipped by FOR UPDATE SKIP LOCKED

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

No

More to Explore

PL/SQL demos

PL/SQL docs