Database Reference
In-Depth Information
queue_name => 'caught_in_slow_q_again',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => payload,
msgid => msgid);
dbms_output.put_line(rawtohex(msgid));
END;
/
89A118C42BFA4A22AE31932E3426E493
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
Let's see which messages, except the one with
MSGID=89A118C42BFA4A22AE31932E3426E493
we just enqueued, are in the queue.
SQL> SELECT tab.msgid, tab.state
FROM "NDEBES"."POST_OFFICE_QUEUE_TABLE" tab
WHERE q_name='CAUGHT_IN_SLOW_Q_AGAIN';
MSGID STATE
-------------------------------- ----------
3DB7BE5A803B4ECB91EF7B021FB223F4 1
00E146F0560C4760886B7AEEEDCF7BF2 1
34F01D98AF0444FF91B10C6D00CB5826 0
54783A999BB544419CDB0D8D44702CD3 0
B78950028B3A4F42A5C9460DDDB9F9D7 0
89A118C42BFA4A22AE31932E3426E493 0
25CC997C9ADE48FFABCE33E62C18A7F3 0
353D44D753494D78B9C5E7B515263A6D 0
2544AA9A68C54A9FB9B6FE410574D85A 0
F7E0192C2AEF45AEAEE5661F183261CC 1
10 rows selected.
There are currently ten messages in the queue (each row represents one message).
STATE=0
means the message is ready for dequeue, while
STATE=1
means the message was enqueued with
a delay for deferred dequeue (
message_properties.delay>0
; the delay is in seconds).
What happens when two processes dequeue concurrently? Let's take a simplified version
of the
SELECT
FOR
UPDATE
we found in the SQL trace file of the dequeue operation and run it in
session 1 without
SKIP
LOCKED
, while retaining the bind variables.
SQL> VARIABLE q_name VARCHAR2(30)
SQL> VARIABLE state NUMBER
SQL> BEGIN
:q_name:='CAUGHT_IN_SLOW_Q_AGAIN';
:state:=0;
END;
/
