Database Reference
In-Depth Information
BEGIN
dequeue_options.wait:=dbms_aq.no_wait;
DBMS_AQ.DEQUEUE (
queue_name => 'caught_in_slow_q_again',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => payload,
msgid => msgid);
END;
END OF STMT
PARSE #27:c=0,e=123,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=69919173943
=====================
PARSING IN CURSOR #26 len=565 dep=1 uid=0 oct=3 lid=0 tim=69919341626 hv=319671114 a
d='674e3128'
select
/*+ FIRST_ROWS(1) */ tab.rowid, tab.msgid, tab.corrid,
tab.priority, tab.delay, tab.expiration, tab.retry_count,
tab.exception_qschema, tab.exception_queue, tab.chain_no,
tab.local_order_no, tab.enq_time, tab.time_manager_info, tab.state,
tab.enq_tid, tab.step_no, tab.sender_name, tab.sender_address,
tab.sender_protocol, tab.dequeue_msgid, tab.user_prop, tab.user_data
from "NDEBES"."POST_OFFICE_QUEUE_TABLE" tab where q_name = :1 and (state = :2 )
order by q_name, state, enq_time, step_no, chain_no, local_order_no
for update skip locked
END OF STMT
EXEC #26:c=0,e=168,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=2,tim=69919341618
FETCH #26:c=0,e=139,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=2,tim=69919363741
EXEC #27:c=0,e=38468,p=0,cr=12,cu=0,mis=0,r=0,dep=0,og=1,tim=69919378626
ERROR #27:err=25228 tim=0
Contention and SELECT FOR UPDATE SKIP LOCKED
Let's pretend the
SKIP
LOCKED
extension does not exist. To investigate what happens when
several processes attempt to consume messages simultaneously (any available message),
we need to first enqueue some messages.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
payload blob;
msg raw(64);
msgid raw(16);
BEGIN
dbms_lob.createtemporary(payload, true);
msg:=utl_raw.cast_to_raw('message in a bottle');
dbms_lob.writeappend(payload, utl_raw.length(msg), msg);
DBMS_AQ.ENQUEUE (
