Forum Stats

  • 3,836,726 Users
  • 2,262,174 Discussions
  • 7,900,088 Comments

Discussions

Inconsistent Queue Behavior. Messages will work fine and then suddenly will be stuck in Ready State

38832123-c423-4d96-9408-3e7fe53e1b9f
edited Jul 29, 2016 3:19AM in Advanced Queueing

Hi, I am trying to achieve the following:

I bulk insert records to a table. There's a trigger on this table that writes the primary key into Queue Table. The subscriber to the queue reads this key and do some processing.

Here's what I am noticing on a frequent basis:

  • If I am bulk inserting let's say 15K records, the first 400 something would be queued and dequeued perfectly fine but suddenly the subscriber proc will stop dequeing the messages and the messages will be stuck in 'READY' state and will never be dequeued.
  • Whenever we restore the database, it will stop working even though if nothing is in disabled state. In this case also the messages will be stuck in queue table in 'Ready' state. The only way to revive it is by dropping the queue, queue table and recreating these objects.


Below is my code:

--CREATE A CUSTOM MESSAGE TYPE

CREATE or replace type XXFAN_MESSAGE_TYP as object (

subject     VARCHAR2(30),

text        VARCHAR2(4000)); 

--CREATE QUEUE TABLE

begin

DBMS_AQADM.CREATE_QUEUE_TABLE (

queue_table        => 'XXFAN_ASN_LINE_Q_TABLE',

multiple_consumers => true,

queue_payload_type => 'XXFAN_MESSAGE_TYP');

end;

/

--CREATE MESSAGE QUEUE

begin

DBMS_AQADM.CREATE_QUEUE (

queue_name         => 'XXFAN_ASN_LINE_MSG_QUEUE',

queue_table        => 'XXFAN_ASN_LINE_Q_TABLE'

);

end;

/

--START THE QUEUE

begin

DBMS_AQADM.START_QUEUE (

queue_name         => 'XXFAN_ASN_LINE_MSG_QUEUE');

end;

/

--ADD SUBSCRIBER TO THE QUEUE

BEGIN   

dbms_aqadm.add_subscriber (

          queue_name => 'XXFAN_ASN_LINE_MSG_QUEUE',

          subscriber => sys.aq$_agent(

                           'XXFAN_ASN_LINE_Subscriber',NULL,NULL)

          );  

       DBMS_AQ.REGISTER (

          SYS.AQ$_REG_INFO_LIST(

             sys.aq$_reg_info(

                'XXFAN_ASN_LINE_MSG_QUEUE:XXFAN_ASN_LINE_Subscriber',

                DBMS_AQ.NAMESPACE_AQ,

                'plsql://XXFAN_CREATE_ASNLINES_FROM_PO',

                HEXTORAW('FF')

                )

             ),

          1

          );

end;

/

--*************************************Subscriber Proc*************************

create or replace

PROCEDURE             XXFAN_CREATE_ASNLINES_FROM_PO (

  context  raw,

  reginfo  sys.aq$_reg_info,

  descr    sys.aq$_descriptor,

  payload  raw,

  payloadl number

)

as

  dequeue_options dbms_aq.dequeue_options_t;

  message_properties dbms_aq.message_properties_t;

  message_handle raw ( 16 ) ;

  message XXFAN_MESSAGE_TYP; --Message Type

  no_messages exception;

  pragma exception_init ( no_messages, -25228 ) ;

begin

  dequeue_options.wait := DBMS_AQ.NO_WAIT;

  dequeue_options.msgid := descr.msg_id;

  dequeue_options.consumer_name := descr.consumer_name;

 

  loop

    dbms_aq.dequeue (

      queue_name => descr.queue_name,--'XXFAN_ASN_LINE_MSG_QUEUE',

      dequeue_options => dequeue_options,

      message_properties => message_properties,

      payload => message,

      msgid => message_handle

    ) ;

    ----------CREATE ASN DETAILs START-----------

    DECLARE

    v_po_line_item_id VARCHAR2(50) := message.text; --PO LINE ITEM FROM THE QUEUE

    v_message_subject VARCHAR2(50) := message.subject; ----PO LINE SUBJECT FROM THE QUEUE

       

   BEGIN

  INSERT INTO xyzTable (PoItemId, MsgSubject)

  VALUES (v_po_line_item_id,v_message_subject);

  COMMIT;  

   END;   

   

  end loop;

   

end;

Below are some of the messages which are stuck:

select msg_state, msg_id from aq$XXFAN_ASN_LINE_Q_TABLE;

Result:

READY            387AEA70E99EF49AE05305838A0A5985

READY            387AEA70E99FF49AE05305838A0A5985

READY            387AEA70E9A0F49AE05305838A0A5985

READY            387AEA70E9A1F49AE05305838A0A5985

READY            387AEA70E9A2F49AE05305838A0A5985

Answers

  • WGabriel
    WGabriel Member Posts: 202 Bronze Badge
    edited Jul 29, 2016 3:19AM

    Hello,

    in this forum there are again and again reports of problems (strange effects etc.) concerning using the PL/SQL AQ notification mechanism.

    In order to avoid these it would be better to use an AQ listener process to dequeue data.

    Kind regards,

    WoG

This discussion has been closed.