4

What are the cases when Oracle advanced queuing is the preferred mechanism for implementing functional requirements? For example, money transfer from bank account A to bank account B might theoretically be considered as two different operations, and might be implemented separately - first, enqueue money transfer from account A (update), then enqueue money trasfer to account B (update). However, it's obvious it can't be done like that because those two operation should be done in one consistent operation - in transaction.

Maybe advanced queuing should be only considered when developing stored procedures/functions that perform some logic that is both done internally (by doing DML operations and calling other local stored proc/fnc) and externally (by calling some webservices). When using such calls to webservices we can't wrap it all into consistent transaction, so the only way is to use some queuing mechanism...

Any real life detailed examples would be appreciated.

What I'm curious about is not the details of the technology itself, but rather what are the real life cases of using such messaging because I haven't done that before. Like, why do I need to pass some data (message payload) around?

0

5 Answers 5

4

I use AQ for

  • transactions between databases
  • implementing business rules that would otherwise need to be implemented using triggers ( ie actions must be taken on the same table that initiates the action)

By using a queue you can do these things:

  • a transaction can take place in the order you want it to, just not instantaneously. eg insert in one database, copy same record to another database
  • the second transaction is now independent of the first but you still have consistency. eg only if the first insert succeeds does the second AQ transaction take place and if the second transaction takes place on another database it only happens if the second database is ready to receive

Here is more information on how I use AQ between databases. I am not an expert and got most of the code from the Internet. Oracle documentation is lengthy but did not really help me.

First create the queue:

BEGIN
  SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
  (
    QUEUE_TABLE           =>        'QT_NEW_CASE'
   ,QUEUE_PAYLOAD_TYPE    =>        'FILE_ACTION'
   ,COMPATIBLE            =>        '8.1'
   ,STORAGE_CLAUSE        =>        '
                                     TABLESPACE USERS
                                     PCTUSED    0
                                     PCTFREE    10
                                     INITRANS   1
                                     MAXTRANS   255
                                     STORAGE    (
                                                 INITIAL          64K
                                                 NEXT             1M
                                                 MINEXTENTS       1
                                                 MAXEXTENTS       UNLIMITED
                                                 PCTINCREASE      0
                                                 BUFFER_POOL      DEFAULT
                                                )'
   ,SORT_LIST             =>        'ENQ_TIME'
   ,MULTIPLE_CONSUMERS    =>         FALSE
   ,MESSAGE_GROUPING      =>         0
   ,SECURE                =>         FALSE
   );
End;

On the origin database:

CREATE OR REPLACE TYPE FILE_ACTION                                                                                                                                                       AS OBJECT
(    ACTION VARCHAR2(20),
     CASE_ID NUMBER(10),
     OTHER VARCHAR2(20)
);

The Action is admittedly crude but versatile. The requirements were to funnel changes from multiple tables to another database where further processing was required without touching the application code. A typical call is to a package from a trigger, other procedure or job.

queue_util.add_file ('CLOSE', v_case_id,:NEW.ID);

Inside the package

   PROCEDURE add_file_to_queue (action_in      IN VARCHAR2,
                                d_case_id_in   IN NUMBER,
                                d_other_in     IN VARCHAR2:= NULL)
   IS
      /******************************************************************************
      PURPOSE: when there is a change to a file (create, closed or reopen)  add the change to the queue of changes 
     ******************************************************************************/
      queue_options        SYS.DBMS_AQ.enqueue_options_t;
      message_properties   SYS.DBMS_AQ.message_properties_t;
      message_id           RAW (16);
      my_message           file_action;
      err_text             VARCHAR2 (2000);
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      my_message := file_action (action_in, d_case_id_in, d_other_in);
      DBMS_AQ.enqueue (queue_name           => 'NEW_CASE_QUEUE',
                       enqueue_options      => queue_options,
                       message_properties   => message_properties,
                       payload              => my_message,
                       msgid                => message_id);

      IF g_debugging
      THEN
        ;
      --insert debugging info if g_debugging is true
      END IF;

      COMMIT;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         err_text := SQLERRM;
         --logging error to another table

      WHEN OTHERS
      THEN
         err_text := SQLERRM;
         --more logging
   END add_file_to_queue;

--and popping messages off the queue

   PROCEDURE send_from queue (case_id_in IN NUMBER := NULL)
   IS
      /******************************************************************************
      PURPOSE:get the list of file changes and send them out
      *****************************************************************************/
      queue_options                DBMS_AQ.dequeue_options_t;
      message_properties           DBMS_AQ.message_properties_t;
      message_id                   file_action;
      v_file                       VARCHAR2 (20);
      v_case_id                    NUMBER (10);
      v_filename                   VARCHAR2 (500);
      v_action                     VARCHAR2 (20);
      v_other                      VARCHAR2 (20);
      v_err_id                     INTEGER;
      bad_data_ex EXCEPTION;
      v_err_text                   VARCHAR2 (50);

      TYPE cases_cur IS REF CURSOR;

      new_cases                    cases_cur;
   BEGIN
      IF case_id_in IS NULL
      THEN
         OPEN new_cases FOR
              SELECT   qt.msgid
                FROM   cqt_new_case qt
            ORDER BY   qt.enq_time;
      ELSE
         OPEN new_cases FOR
              SELECT   qt.msgid
                FROM   qt_new_case qt
               WHERE   qt.user_data.case_id = case_id_in
            ORDER BY   qt.enq_time;
      END IF;

      --should have added a check here to make sure
      --the other database is up and running
      LOOP
         BEGIN
            FETCH new_cases INTO   message_id;

            --reinitialize values to null
            v_case_id := NULL;
            v_filename := NULL;
            v_file := NULL;
            v_action := NULL;
            v_other := NULL;
            --to try and clear all locks
            COMMIT;
            EXIT WHEN new_cases%NOTFOUND;

            IF case_id_in IS NOT NULL
            THEN
               queue_options.deq_condition :=
                  'tab.user_data.case_id = ' || case_id_in;
            END IF;

            DBMS_AQ.dequeue (queue_name           => 'NEW_CASE_QUEUE',
                             dequeue_options      => queue_options,
                             message_properties   => message_properties,
                             payload              => my_message,
                             msgid                => message_id);
            v_case_id := my_message.case_id;
            v_action := my_message.action;
            v_other := my_message.other;

            IF v_case_id IS NOT NULL AND v_case_id > 0
            THEN
               IF g_debugging
               THEN
                 ;
               --insert your debugging information
               END IF;
            --continues on with lengthy data transforms
            --for actions like NEW, CLOSE, REOPEN
END SEND_FROM_QUEUE;

I would certainly write this differently today but it works.... The key problem with AQ for me is that I have never gotten queue to queue messaging going between different databases. This was described as a primary value for it. Yet I still like having the certainty that the initial transaction can complete without regard for the state of the destination database. If the message fails to be inserted when into the destination database an error is logged and an email is sent notifying the developer.

2
  • Thanks for sharing experience. Have several questions :) First, do you put the calls to stored procedures into the queue? It's written the queues are used for communicating using "messages" but I guess such messages could be just PL/SQL blocks that contain ordinary procedure calls.
    – Centurion
    Commented Oct 2, 2012 at 20:37
  • Regarding your example with transaction: what would happen if an insert of a copy of the same record fails in the second database? I guess that should be managed manually in the second stored procedure, who is doing the insert (catching exceptions and etc). Otherwise it would break such "distributed transaction" consistency. However, it's interesting what would happen if there would occur system or hardware error during insert of second record in 2nd database. IMHO, it would be hard to "rollback" the first insert to the first database.
    – Centurion
    Commented Oct 2, 2012 at 20:39
7

I am not as familiar with Oracle as I am with Postgres. Nonetheless, I will tell you where I see queuing approaches like this as ideal (as the author of pg_message_queue): allowing database transactions to have non-transactional side-effects with minimal complexity cost. A simple example is "when we commit a transaction saying we have shipped a part, let's send an email out notifying the customer."

You could do this without Oracle AQ, but Oracle AQ will likely make this simpler. The key factor is that this allows you to enqueue the message such that it is visible on commit, and have the message which will be the basis of an email once the database transaction commits and not before. If you try to send the message from inside the transaction, you get a number of nasty failure cases:

  1. If the message fails to send should we abort the transaction?
  2. If the message sends and the transaction later is rolled back, how do we un-send the email? We can't.

Another example may be loosely coupled applications, integrated over a message queue. Each application can largely run unaware of the other, but when transactions are committed, messages are sent to the other. If they can't be delivered immediately, no problem. We will process them when we can.

So the basic thing is that there are many cases where you want to centralize logic around the database where you can't really do everything properly in a single transaction. Being able to send messages to other components on db commit is really helpful.

And sure you could build everything yourself without AQ. But this has it already built for you.

Edit: I have re-read the Oracle docs here and they are hopelessly confusing, so I don't blame you for your confusion here. If I wasn't already somewhat familiar with the sorts of things they were talking about, I am not sure I would have been able to follow them. I am now 100% sure that my answer is on-target though.

3
  • Thanks. As currently being iOS mobile developer, I also did faced with queues. Recently, I needed to perform execution of several dependent tasks in a queue (do some procesing, download data from webservice 1, then from webservice 2 and then update user interface). In the past as Oracle stored procedure developer, I did all the logic with local DB tables in a single or nested proc/fnc calls, so all the changes were consistent within one transaction.
    – Centurion
    Commented Oct 2, 2012 at 20:54
  • I'm starting to think DB queues should be used when dealing with external functionality (like you mentioned email sending) or with external databases (like @kevinky mentioned about transactions between databases)
    – Centurion
    Commented Oct 2, 2012 at 20:54
  • 1
    In the case where you have loosely coupled applications messaging eachother over a message queue, this is more or less how you'd do transactions hitting multiple databases. Note this is not the only way you might do this, but it is one way you might. The point though is that if you go this route, the db transactions are loosely coupled. For example, I am looking at integrating LedgerSMB with GNUmed using pg_message_queue. The nice thing is that the two applications don't have to be aware of the db structure of the other. Commented Oct 3, 2012 at 0:09
1

In real life AQ can be used to send notifications to customers when a specific actions are performed in the database.

Consider an example where a user requested for purchase of an item. As soon as the payment is received an order will be created by populating in queue with the details. This queue will have subscribers for the shop owner and the payment messaging applications.

So subscriber 1 i.e. shop owner(any notification to be sent to shop owner) will pick the message about the item to be shipped. and another subscriber will prepare the notification about item purchased so in this case once the message is processed by both the subscriber the order is marked as complete and message from queue is removed.

If during the processing of the order there is any error an entry will be marked in the exception queue and then the exception queue messages can be processed later on.

1

You can use Oracle AQ for variety of real life examples,like we have used -

  1. Order Flow Order - We have various order coming in, which need to processed in the given order so client will put the message from different places at different times, so we just enqueue the message and get the correct order during dequeue.
  2. We have used the Oracle AQ for notification for the workflow complete i.e. some client who initiated/submitted a procedure to run successfully same is executed by scheduler so once this procedure finishes processing we enqueue a message which notify the client who initiated the procedure (using oracle aq pl/sql notifications).
  3. Best use is to replicate data from one database to another, you have just set the remote subscriber then AQ will transfer the data fro one queue to another queue in the same order internally this is called propagation which is being used in the oracle streams as well.
0

It depends on the requirement of the web applications and the purpose of using Advanced Queueing.

I think there are alternatives such as comparing the timestamp which starts the query and hence check the priority of the transaction query to be executed first.

1
  • 1
    But what if you have multiple consumers processing the data? Unless you selected the time stamp 'for update skip locked' you might select a record that was already being processed by another processor
    – mancini0
    Commented Apr 11, 2017 at 4:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.