Advanced Queues - AQ's

Steps to create AQ's.


1. Create payload to be enqueued and dequeued.  [ user defined object type ]

CREATE type Message_typ as object (subject VARCHAR2(30),
                                   text    VARCHAR2(80));  

2. Create a Queue table to hold the payload from step 1 on enqueue.

begin

 DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table ='demo_qtable',
                                queue_payload_type => 'Message_typ');

 commit;

end;

3. Create a Queue and attach Queue Table from step 2.

begin

       DBMS_AQADM.CREATE_QUEUE (queue_name => 'demo_queue',
                                queue_table => 'demo_qtable');

   commit;

end;

DBMS_AQADM.CREATE_QUEUE (
   queue_name          IN       VARCHAR2,
   queue_table         IN       VARCHAR2,
   queue_type          IN       BINARY_INTEGER DEFAULT NORMAL_QUEUE,
   max_retries         IN       NUMBER         DEFAULT NULL,
   retry_delay         IN       NUMBER         DEFAULT 0,
   retention_time      IN       NUMBER         DEFAULT 0,
   dependency_tracking IN       BOOLEAN        DEFAULT FALSE,
   comment             IN       VARCHAR2       DEFAULT NULL,
   auto_commit         IN       BOOLEAN        DEFAULT TRUE);

queue_type

Specifies whether the queue being created is an exception queue or a normal queue. NORMAL_QUEUE means the queue is a normal queue. This is the default. EXCEPTION_QUEUE means it is an exception queue. Only the dequeue operation is allowed on the exception queue.

retention_time

Number of seconds for which a message is retained in the queue table after being dequeued from the queue. INFINITE means the message is retained forever. NUMBER is the number of seconds for which to retain the messages. The default is 0, no retention.

4. Start the Queue

begin
    DBMS_AQADM.START_QUEUE (queue_name => 'demo_queue');
    commit;
end;

5. Add subscriber (agent) to the Queue (Q-listener).

 declare
    subscriber aq$_agent;
 begin
    subscriber:=aq$_agent(name =>'demo_agent', 
                          address => NULL, 
                          protocol => NULL);
    dbms_aqadm.add_Subscriber(queue_name => 'demo_queue',

                              subscriber => subscriber);

  commit;

 end;

6. Enqueue the payload to Q-Table

declare

   enqueue_options     dbms_aq.enqueue_options_t;

   message_properties  dbms_aq.message_properties_t;
   message_handle      RAW(16);
   message             aq.message_typ;

begin

dbms_aq.enqueue(queue_name => 'demo_queue',           
                enqueue_options      => enqueue_options,       
                message_properties   => message_properties,     
                payload              => message,               
                msgid                => message_handle);
 commit;
end;

7. Dequeue payload from Q-Table

DECLARE
   dequeue_options     dbms_aq.dequeue_options_t;
   message_properties  dbms_aq.message_properties_t;
   message_handle      RAW(16);
   message             aq.message_typ;

BEGIN
   DBMS_AQ.DEQUEUE(queue_name => 'demo_queue',
                   dequeue_options    => dequeue_options,
                   message_properties => message_properties,
                   payload            => message,
                   msgid              => message_handle);

   DBMS_OUTPUT.PUT_LINE ('Message: ' || message.subject ||
                                      ' ... ' || message.text );
   COMMIT;
END;

8. Setting Message Properties 

TYPE MESSAGE_PROPERTIES_T IS RECORD (
   priority               BINARY_INTEGER  DEFAULT 1,
   delay                  BINARY_INTEGER  DEFAULT NO_DELAY,
   expiration             BINARY_INTEGER  DEFAULT NEVER,
   correlation            VARCHAR2(128)   DEFAULT NULL,
   attempts               BINARY_INTEGER,
   recipient_list         AQ$_RECIPIENT_LIST_T,
   exception_queue        VARCHAR2(51)    DEFAULT NULL,
   enqueue_time           DATE,
   state                  BINARY_INTEGER,
   sender_id              AQ$_AGENT       DEFAULT NULL, 
   original_msgid         RAW(16)         DEFAULT NULL);

Example : 

declare
    message_properties  dbms_aq.message_properties_t;
begin
     message_properties.priority := 30;

     message_properties.delay := 7*24*60*60;

     message_properties.expiration := 2*7*24*60*60;

    /* Expiration is calculated from the earliest dequeue time. So, if an application wants a message to be dequeued no earlier than a week from now, but no later than 3 weeks from now, this requires setting the expiration time for 2 weeks. */




     -- enqueue payload ---
end;

9. Setting Enqueue Properties

TYPE  ENQUEUE_OPTIONS_T IS RECORD (
   visibility            BINARY_INTEGER  DEFAULT ON_COMMIT,
   relative_msgid        RAW(16)         DEFAULT NULL,
   sequence_deviation    BINARY_INTEGER  DEFAULT NULL,
   transformation        VARCHAR2(60)    DEFAULT NULL);

visibility





Specifies the transactional behavior of the enqueue request. The possible settings follow:

ON_COMMIT: The enqueue is part of the current transaction. The operation is complete when the transaction commits. This setting is the default.

IMMEDIATE: The enqueue is not part of the current transaction. The operation constitutes a transaction on its own. This is the only value allowed when enqueuing to a non-persistent queue.

10. Setting Dequeue Properties



TYPE DEQUEUE_OPTIONS_T IS RECORD (
   consumer_name     VARCHAR2(30)    DEFAULT NULL,
   dequeue_mode      BINARY_INTEGER  DEFAULT REMOVE,
   navigation        BINARY_INTEGER  DEFAULT NEXT_MESSAGE,
   visibility        BINARY_INTEGER  DEFAULT ON_COMMIT,
   wait              BINARY_INTEGER  DEFAULT FOREVER,
   msgid             RAW(16)         DEFAULT NULL,
   correlation       VARCHAR2(128)   DEFAULT NULL,
   deq_condition     VARCHAR2(4000)  DEFAULT NULL,
   transformation    VARCHAR2(60)    DEFAULT NULL);


 Example 


     dequeue_options.navigation := FIRST_MESSAGE;
     dequeue_options.navigation := NEXT_MESSAGE;
     exception : 
     dequeue_options.navigation := DBMS_AQ.NEXT_TRANSACTION;




consumer_name





Name of the consumer. Only those messages matching the consumer name are accessed. If a queue is not set up for multiple consumers, then this field should be set to NULL.

For secure queues, consumer_name must be a valid AQ Agent






dequeue_mode





Specifies the locking behavior associated with the dequeue. The possible settings follow:

BROWSE: Read the message without acquiring any lock on the message. This specification is equivalent to a select statement.

LOCKED: Read and obtain a write lock on the message. The lock lasts for the duration of the transaction. This setting is equivalent to a select for update statement.

REMOVE: Read the message and update or delete it. This setting is the default. The message can be retained in the queue table based on the retention properties.

REMOVE_NODATA: Mark the message as updated or deleted. The message can be retained in the queue table based on the retention properties.







11. Purge Queue Table

TYPE AQ$_PURGE_OPTIONS_T is RECORD (block  BOOLEAN  DEFAULT FALSE
                                    delivery_mode   PLS_INTEGER   DEFAULT PERSISTENT);

block

TRUE/FALSE.


If block is TRUE, then an exclusive lock on all the queues in the queue table is held while purging the queue table. This will cause concurrent enqueuers and dequeuers to block while the queue table is purged. The purge call always succeeds if block is TRUE.


The default for block is FALSE. This will not block enqueuers and dequeuers, but it can cause the purge to fail with an error during high concurrency times.




delivery_mode

Kind of messages to purge, either DBMS_AQ.BUFFERED or DBMS_AQ.PERSISTENT

DECLARE
   PurgeOptions dbms_aqadm.AQ$_PURGE_OPTIONS_T;
BEGIN
   PurgeOptions.block:=True;
   DBMS_AQADM.PURGE_QUEUE_TABLE (queue_table => 'demo_queue',

                                 purge_options => PurgeOptions);
END;
/

Dead Lock Detection.

A deadlock occurs when two or more session are waiting for data locked by each other, resulting in all the sessions being blocked. 

Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock. 

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

In addition to the deadlock error reported to the session, a message is placed in the alert log.

ORA-00060: Deadlock detected. More info in file $OracleHome\udump\xxxx_ora_4200.trc.

The error message contains a reference to a trace file, whose contents indicate the SQL statements blocked in both the session that detected the deadlock and the other blocked sessions.

Sample Trace file:

Deadlock Graph

The first section is important; it shows the deadlock graph. Here are the various pieces of information on the graph. Deadlock graph tells you which sessions are involved, what types of locks are being sought after, etc.

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TM-009f9121-00000000        85      54    SX   SSX       84       8    SX   SSX

TM-009f9121-00000000        84       8    SX   SSX       85      54    SX   SSX

session 54: DID 0001-0055-00002966 session 8: DID 0001-0054-0000541B 

session 8: DID 0001-0054-0000541B session 54: DID 0001-0055-00002966 

Row Information

The next critical section shows the information on the rows locked during the activities of the two sessions. From the tracefile you can see the object ID. Using that, you can get the object owner and the name from the DBA_OBJECTS view. The information in on rowID is also available here.

Deadlock due to Foreign Key

This is a really tricky one; but not impossible to identify. When a key value in parent table is updatd or a row is deleted, Oracle attempts to takes TM lock on the entire child table. If an index is present on the foreign key column, then Oracle locates the corresponding child rows and locks only those rows. The documentation in some versions may not very clear on this. There is a documentation bug (MOS Bug# 2546492). In the absense of the index, a whole table TM lock may cause a deadlock. Let's see the scenario when it happens.

Scenario

Here is the scenario when this deadlock occurs.

Step	Session1	Session2
1	Delete Chaild Row1
2		Delete Child Row2
3	Delete Parent Row1 (Waits on TM Enqueue)
4		Delete Parent Row2 (Waits on TM Enqueue) Deadlock!

Deadlock Graph

This is how the deadlock graph looks like when caused by unindexed foreign key. As you can see, the deadlock graph does not clearly say that the issue was to do with Foreign Key columns not being indexed.Instead, the clues here are:

• TM locks for both the sessions, instead of TX. Remember: TM are metadata related, as opposed to TX, which is a row related lock.
• The lock type of holders is Share Exclusive (SX) as opposed to Exclusive (X)
• Sessions do not show any row information

These three clues together show that this deadlock is due to FK contention rather than the conventional row locks. 

So, what do you do? Simple - create the indexes on those FKs and you will not see this again. As a general rule you should have indexes on FKs anyway; but there are exceptions, e.g. a table whose parent key is never updated or deleted infrequently (think a table with country codes, state codes or something pervasive like that). If you see a lot of deadlocks in those cases, perhaps you should create indexes on those tables anyway.

Deadlock due to Bitmap Index Contention

Bitmap Index is a special type of index that stores bitmaps of actual values and compare bitmaps to bitmaps, e.g. instead of comparing literals such as "A" = "A", Oracle converts the value to a bitmap and compares against the stored bitmap values. For instance “A” might be represented as "01011"; so the comparison will be “01011” = "01011". Index searches are way faster compared to literal comparison.

However, there is a price to pay for this performance. Unlike a regular b*tree index, when a row is updated, the index piece of the bitmap index is locked until the transaction is committed. Therefore udates to any of the rows covered by that index piece hangs. When two sessions update two different rows covered by the same index piece, they wait for each other. Here is the scenario when this condition arises.

Scenario                         

Step	Session1	Session2
1	Update Row 1 (Bitmap index piece is locked)
2		Update Row2 (Hangs for TX Row Lock)
3	Update Row2 (Hangs as bitmap index piece is locked by session2 and can't release until it commits) Deadlock!

Deadlock Graph

You can confirm this occurrence from readling the deadlock graph. 



The clues that show this type of deadlock:

• The lock type is TX (as shown in the Resource Name)
• The lock wait mode is “S” (shared) but the type of lock is TX rather than TM.
• The waiter waits with mode "S" instead of "X"
• The row information is available but the object ID is not the ID of the table; but the bitmap index.

The solution to this deadlock is really simple – just alter the application logic in such a way that the two updates will not happen in sequence without commits in between. If that’s not possible, then you have to re-evaluate the need for a bitmap index. Bitmap indexes are usually for datawarehouse only; not for OLTP. 

Deadlock due to Primary Key Overlap

This is a very special case of deadlock, which occurs during inserts; not updates or deletes. This is probably the only case where inserts cause deadlocks. When you insert a record into a table but not commit it, the record goes in but a further insert with the same primary key value waits. This lock is required for Oracle because the first insert may be rolled back, allowing the second one to pass through. If the first insert is committed, then the second insert fails with a PK violation. But in the meantime-before the commit or rollback is issued-the transaction causes the second insert to wait and that causes deadlock. Let's examine the scenario:

Scenario

Step	Session1	Session2
1	Insert PK Col value = 1 (Doesn't commit)
2		Insert PK Col value = 2 (Doesn't commit)
3	Insert PK Col = 2 (Hangs, until Session2 commits)
4		Insert PK Col = 1 (Hangs and Deadlock)

Deadlock Graph

The deadlock graph looks like the following. 


The key clues are:

• The lock type is TX (row lock)
• The holders are holding the lock in "X" (exclusive) mode
• The waiters are waiting for locks in “S” mode, even when the locks type TX.
• The subsequent parts of the tracefile don’t show any row information.

However, the latter parts of the tracefile shows the SQL statement, which should be able to point to the cause of the deadlock as the primary key deadlock. Remember, this may be difficult to diagnose first since there is no row information. But this is probably normal since the row is not formed yet (it's INSERT, remember?).

TKProf & SQL Trace