SELECT FOR UPDATE SKIP LOCKED - Secrets of the Oracle Database - page 148

that want to use concurrent processes for dequeuing. To be precise, this applies only when
applications ask for any message that is ready for dequeuing, which is the predominant
approach. When concurrent processes ask for specific messages, the probability for contention
is lower.
The following example shows how to obtain a SQL trace file that illustrates the use of the
clause SKIP LOCKED by AQ. Before running the code below, make sure you have sufficient privi-
leges to execute the package DBMS_AQADM that is needed to create queue tables and queues.
SQL> EXEC dbms_aqadm.create_queue_table('post_office_queue_table', 'raw');
SQL> EXEC dbms_aqadm.create_queue('caught_in_slow_q_again', -
'post_office_queue_table');
SQL> EXEC dbms_aqadm.start_queue('caught_in_slow_q_again');
SQL> ALTER SESSION SET SQL_TRACE=TRUE;
Session altered.
SQL> DECLARE
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
payload blob;
msgid raw(16);
BEGIN
dequeue_options.wait:=dbms_aq.no_wait; -- default is to patiently wait forever
DBMS_AQ.DEQUEUE (
queue_name => 'caught_in_slow_q_again',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => payload,
msgid => msgid);
END;
/
DECLARE
*
ERROR at line 1:
ORA-25228: timeout or end-of-fetch during message dequeue from
NDEBES.CAUGHT_IN_SLOW_Q_AGAIN
ORA-06512: at "SYS.DBMS_AQ", line 358
ORA-06512: at "SYS.DBMS_AQ", line 556
ORA-06512: at line 8
In the SQL trace file, you will see an example of a SELECT with the FOR UPDATE SKIP LOCKED
clause.
PARSING IN CURSOR #27 len=367 dep=0 uid=30 oct=47 lid=30 tim=69919173952 hv=38306557
86 ad='6771d48c'
DECLARE
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
payload blob;
msgid raw(16);