Asynchronous processing using AQ Callback

Database transactions are awesome! The ability to do multiple changes to data in various tables and to commit those changes so that they all appear to other sessions at once, or to roll them all back as if nothing had happened is one of the most powerful features of relational databases.

… and here’s the “but”; but sometimes you don’t want to do all processing in one transaction. For example, with the on-line systems of today we might want to do the minimum processing needed to fulfil an order in a single transaction and defer all non-immediate processing to maximise throughput. “Near real time” would be the way to describe our deferred processing. There are various ways to do this but one way is via queues with callback routines.

So, how do we go about doing this? Here’s a high level summary of the steps involved:

• Create an object type with the details that the processing will require
• Create a queue whose payload is the object type
• Create a callback routine that will be run whenever an item is queued
• Associate the callback routine with the queue so that Oracle knows to call it

Getting into some solid detail we’ll use the EMP table from the SCOTT schema. We’ll assume our business processing has two functions that need to be done asynchronously; update an employees salary and transfer an employee to a different division (okay, probably not great scenarios for asynchronous processing but I wanted something simple using the EMP table…)

Since we’ll be working within the SCOTT schema we need to ensure SCOTT can do Advance Queue operations so we grant SCOTT the ability execute on the packages we will use:

GRANT EXECUTE ON dbms_aqadm TO scott;
GRANT EXECUTE ON dbms_aq    TO scott;

Next we will need to create our object type that the queue will hold. For this example, the type will hold the necessary EMP attributes we need to know about and also an attribute that will store a description of the operation to perform:

CREATE OR REPLACE TYPE t_emp_event
AS OBJECT
  (event_operation VARCHAR2 (20)
  ,empno           NUMBER (4)
  ,sal             NUMBER (7,2)
  ,mgr             NUMBER (4)
  ,deptno          NUMBER (2)
  )
/

Next up, we create our queue on this object type:

BEGIN
   dbms_aqadm.create_queue_table (queue_table        => 'SCOTT.EMP_EVENT_QT'
                                 ,queue_payload_type => 'T_EMP_EVENT'
                                 ,multiple_consumers => FALSE);

   dbms_aqadm.create_queue (queue_name  => 'SCOTT.EMP_EVENT_Q'
                           ,queue_table => 'SCOTT.EMP_EVENT_QT);

   dbms_aqadm.start_queue (queue_name => 'SCOTT.EMP_EVENT_Q');
END;
/

We can now enqueue messages onto our queue with a routine like the following:

PROCEDURE enqueue (p_msg  IN t_emp_event) 
IS
   l_enqueue_options    dbms_aq.enqueue_options_t;
   l_message_properties dbms_aq.message_properties_t;
   l_msgid              RAW(16);
BEGIN
   dbms_aq.enqueue (queue_name         => 'SCOTT.EMP_EVENT_Q'
                   ,enqueue_options    => l_enqueue_options
                   ,message_properties => l_message_properties
                   ,payload            => p_msg
                   ,msgid              => l_msgid);
END enqueue;

Before we can process our messages we need to set up our callback routine, which has a specific call interface. You can read about what this interface needs to look like in the Oracle documentation:

PROCEDURE emp_event_callback (context    RAW
                             ,reginfo    SYS.AQ$_REG_INFO
                             ,descr      SYS.AQ$_DESCRIPTOR
                             ,payload    RAW
                             ,payloadl   NUMBER)
IS
   l_dequeue_options    dbms_aq.dequeue_options_t;
   l_message_properties dbms_aq.message_properties_t;
   l_message_handle     RAW(16);
   l_payload            t_emp_event;
BEGIN
   l_dequeue_options.msgid         := descr.msg_id;
   l_dequeue_options.wait          := dbms_aq.no_wait;

   dbms_aq.dequeue (queue_name         => descr.queue_name
                   ,dequeue_options    => l_dequeue_options
                   ,message_properties => l_message_properties
                   ,payload            => l_payload
                   ,msgid              => l_message_handle);

   -- here's what we'll do with the message... in this case call
   -- out to a separate processing routine (not shown here)
   process_message (p_emp_event => l_payload);

END emp_event_callback;

The routine above assumes the processing of the message will be done by some other routine called “process_message”. In a full blown solution (see later on) this routine would be part of a package that captures all the logic associated with the EMP event processing.

Now that the callback routine has been defined we register it so that Oracle will call it whenever something is placed in our queue. To do this we need to call the DBMS_AQ.REGISTER routine, telling it the queue and the processing routine it needs to call:

BEGIN
   dbms_aq.register
      (sys.aq$_reg_info_list
         (sys.aq$_reg_info
            ('SCOTT.EMP_EVENT_Q' -- the queue 
            ,DBMS_AQ.NAMESPACE_AQ
            ,'plsql://SCOTT.EMP_EVENT_CALLBACK' -- this is the routine that will get called when a message is queued
            ,NULL)
         ),
      1
      );
END;
/

… and that’s pretty much all there is to it. Keep in mind that the messages on the queue are transactional so they will not be processed until the session that enqueues the messages commits, but this is exactly the behaviour that we would normally desire.

Of course it is much simpler to encapsulate all of the above inside packaged routines. For the above EMP event scenario I have constructed two packages, available for download:

• emp_event_management, which handles the setting up of the queue and registering the callback routine
• emp_event_processing, which contains the routines to queue up events and processing those same event messages

Once all the complicated DBMS_AQ calls are hidden behind a management API, starting up the event processing is just:

EXEC emp_event_management.setup
EXEC emp_event_management.startup

Shutting things down is:

EXEC emp_event_management.shutdown
EXEC emp_event_management.teardown

The package routines write processing messages to a table named MSG_LOG. I’ve gone a bit overboard with the message written out to help understand what’s going on. The messages are timestamped so the speed of processing can be monitored.

Creating events for processing are matter of making calls to our processing package, similar to these:

EXEC emp_event_processing.update_sal (p_empno => 7654,p_sal => 1600)
EXEC emp_event_processing.update_dept (p_empno => 7654,p_deptno => 20)

By way of demonstration, let’s execute the above commands against the standard SCOTT.EMP table. We’ll grab the current time so as to filter our MSG_LOG table later on, view the data we’re about to change, queue the messages, note the current time so we can see how long the callback processing takes, list the MSG_LOG entries and lastly view the data again to confirm the changes:

VARIABLE now  VARCHAR2(30)
EXEC :now := TO_CHAR(SYSTIMESTAMP,'dd/mm/yyyy hh24:mi:ss.ff')

SELECT empno
,      ename
,      sal
,      deptno
,      mgr
FROM   emp
WHERE  empno = 7654
/

EXEC emp_event_processing.update_sal (p_empno => 7654,p_sal => 1600)
EXEC emp_event_processing.update_dept (p_empno => 7654,p_deptno => 20)
SELECT TO_CHAR(SYSTIMESTAMP,'hh24:mi:ss.ff') AS current_time FROM dual;
COMMIT;

SELECT *
FROM   msg_log
WHERE  msg_timestamp >= TO_TIMESTAMP(:now,'dd/mm/yyyy hh24:mi:ss.ff')
ORDER  BY
       msg_id DESC
/

SELECT empno
,      ename
,      sal
,      deptno
,      mgr
FROM   emp
WHERE  empno = 7654
/

The output of which is:

SQL> VARIABLE now  VARCHAR2(30)
SQL> EXEC :now := TO_CHAR(SYSTIMESTAMP,'dd/mm/yyyy hh24:mi:ss.ff')

PL/SQL procedure successfully completed.

SQL> SELECT empno
  2  ,      ename
  3  ,      sal
  4  ,      deptno
  5  ,      mgr
  6  FROM   emp
  7  WHERE  empno = 7654
  8  /

     EMPNO ENAME             SAL     DEPTNO        MGR
---------- ---------- ---------- ---------- ----------
      7654 MARTIN           1250         30       7698

SQL> EXEC emp_event_processing.update_sal (p_empno => 7654,p_sal => 1600)

PL/SQL procedure successfully completed.

SQL> EXEC emp_event_processing.update_dept (p_empno => 7654,p_deptno => 20)

PL/SQL procedure successfully completed.

SQL> SELECT TO_CHAR(SYSTIMESTAMP,'hh24:mi:ss.ff') AS current_time FROM dual;

CURRENT_TIME
------------------
07:09:02.313000

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT *
  2  FROM   msg_log
  3  WHERE  msg_timestamp >= TO_TIMESTAMP(:now,'dd/mm/yyyy hh24:mi:ss.ff')
  4  ORDER  BY
  5         msg_id DESC
  6  /

MSG_ID MSG_TIMESTAMP   MSG
------ --------------- --------------------------------------------------
   237 07:09:02.329000 Processed CHANGE_DEPT event for emp 7654
   236 07:09:02.329000 Entering emp_event_callback
   235 07:09:02.329000 Processed UPDATE_SAL event for emp 7654
   234 07:09:02.329000 Entering emp_event_callback

SQL> SELECT empno
  2  ,      ename
  3  ,      sal
  4  ,      deptno
  5  ,      mgr
  6  FROM   emp
  7  WHERE  empno = 7654
  8  /

     EMPNO ENAME             SAL     DEPTNO        MGR
---------- ---------- ---------- ---------- ----------
      7654 MARTIN           1600         20       7566

All went according to plan. The changes took just a fraction of a second to process from when the messages were queued, which certainly satisfies the criteria of “near real time” to me.

---

The full set of scripts for the above are available for download.

Advertisement