Oracle dbms_aq.dequeue

Question

Good day, respective all!

Environment: Oracle 18c XE 64-bit under Windows. I have a simple point-to-point queue. Queue persitent. Single consumer. Can you tell me: In what moment exactly dequeuing messages must be removed from a queue?

In my dequeuing procedure I don't do any commit in main transaction. Instead, I process messages and do a commit in an autonomous transaction. Something like this:

procedure deq_main
as 
 procedure proc_deq (p_payload in sometype) 
 as 
 mytype sometype;
 pragma autonomous_transaction;
 begin
  ... do some work
    commit;
 end proc_deq;
begin
  dbms_aq.dequeue(...payload=> mytype,...);
  proc_deq(mytype);
end deq_main;  

There is no commit of dequeue process in deq_main procedure. However, messages removed from the queue. What does it mean? Should I never bother with commit after dbms_aq.dequeue, or it depends on the some conditions? If it depends, can you clarify, under what circumstances I must commit explicitly.

Thanks in advance, Andrew.

Answer 1

Your dequeue will immediately internally mark the message as in progress. You must either commit or rollback in that transaction to complete it. You are probably committing when you disconnect your session otherwise you are rolling back and marking the message as failed (which will likely move it to the exceptions queue).

The autonomous transaction is taking away a lot of the implicit handling that would be done. I would completely scrap it, then if your procedure completed successfully the message is marked as processed immediately.