title: Should dbms_aq.dequeue loop forever?
body: <p>Here my first steps with Oracle Advanced Queueing...</p>
  
  <p><em>Szenario</em>: I have a running application where many, many multiple independ processes report back to a central controller to handle the next steps. Simplified the processes are started via cron or via callback of a just finished process.The callbacks are from remote hosts via http -> php -> DB, basicly one http-call after the process has finished on the remote host.</p>
  
  <p>The complete controller logic was written in pl/sql with a singleton concept in mind, so only one process should execute the controller logic at the same time. In fact in 99% of all calls this is not necessary, but that's not the kind of thing I could change at the moment (nor the architecture in general).</p>
  
  <p>To ensure this there is actually a bad mutex implementation, pseudo-code</p>
  
  <pre><code>
      $mutex = false;
  
      while( not $mutex )
      {
          $mutex = getMutex();
  
          if( $mutex )
              executeController();
          else
              sleep(5);
      }
  </code></pre>
  
  <p>Wherein the mutex is a one field table having the values 0 (=> "free") or 1 ( => "busy" )</p>
  
  <p>The result of this "beautiful" contstruction is log-file full of "Hey! Got no mutex! Waiting...". And the more processes wait, the longer they wait with no control of who's next. Sometimes  the load gets so heavy that the apache first forks and finally dies...</p>
  
  <p><em>Solution</em></p>
  
  <p>So my first "operation" would be to replace the mutex with Oracle Advanced Queueing with the controller as single-consumer. Benefits: No more "busy waiting" within the apache layer, strict first come first serve. </p>
  
  <p>( Because all the DB-Actions take place in the same oracle-schema, this could be achieved with standard-objects, pl/sql-methods as well. But why reinvent the wheel, if there are dbms-packages?)</p>
  
  <p>As far as I read using the listen-feature (polling the queued items) in this context is far better than the registration-feaure (scheduling an action when a message arrives).</p>
  
  <p>Basicly everything works fine, i managed to:</p>
  
  <ul>
  <li>create the message type</li>
  <li>create the queue-table</li>
  <li>create the queue </li>
  <li>start the queue</li>
  <li>add USER as subscriber</li>
  <li>create a procedure for enqueueing</li>
  <li>create a procedure for processing &amp; dequeueing</li>
  <li>create a procedure for listening to the queue and calling the "process &amp; dequeue"-function when a message arrives.</li>
  </ul>
  
  <p>Of course the listener shall be active 24/7, so i specified no "wait" time. In general depending on the time of the day he will get "something to do" at least every few minutes, more likely every few seconds, sometimes more.</p>
  
  <p>Now here is my problem (if it actually is a problem), i just wrote it according to the examples i found so far:</p>
  
  <pre><code>
      CREATE OR REPLACE PROCEDURE demo_aq_listener IS
              qlist       dbms_aq.aq$_agent_list_t;
              agent_w_msg sys.aq$_agent;
          BEGIN
              qlist(0) := sys.aq$_agent(USER, 'demo_aq_queue', NULL);
          LOOP
              dbms_aq.listen(agent_list => qlist, agent => agent_w_msg);                  
              DEMO_AQ_DEQUEUE();--process & dequeue
          END LOOP;
      END;
      /
  </code></pre>
  
  <p>Calling the procedure basically does what i expect: It stays "up" and prosseces the queued messages.</p>
  
  <p>But is this the way to do this? What does it do if there are no queued messages? "Sleeping" within the <em>dbms_aq.listen</em>-routine or "Looping as fast as it can", so that I just have implemented another way of "busy waiting"? Might there be a timeout (maybe on oss-level or elsewhere) i just didn't reach?</p>
  
  <p>Here is the complete code with queue-definition etc.: <a href="http://meyer.so/dbms_aq_with_listener.sql" rel="nofollow noreferrer" title="dbms_aq_with_listener.sql">demo_dbms_aq_with_listener.sql</a></p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Through further testing i just realized that it seems, that i got a far greater lack of understanding then i hoped :(</p>
  
  <p>On "execution level" don't using the listener at all and just looping the dequeue function has the same effect: It waits for the first/next message</p>
  
  <p><pre><code>
      CREATE OR REPLACE PROCEDURE demo_aq_listener IS
      BEGIN
      LOOP
          DEMO_AQ_DEQUEUE();
      END LOOP;<br>
      END;
      /
  </pre></code></p>
  
  <p>At least this is easier to test, calling only</p>
  
  <p><pre><code>
      BEGIN
          DEMO_AQ_DEQUEUE();
      END;
      /
  </pre></code></p>
  
  <p>Also just waits for the first message. Which leaves me totally confused wether I need the listener at all and if what i'am doing does make any sense at all :(</p>
  
  <p><em>Conclusion</em></p>
  
  <p>I don't need the listener at all, because i have a single consumer who can treat all messages in the same way.</p>
  
  <p>But the key/core Question stays the same: <strong>Is it ok to keep DBMS_AQ.DEQUEUE on "maybe active waiting" in a loop knowing it'll get messages all day long in short intervalls</strong>? </p>
  
  <p>(you'll find DEMO_AQ_DEQUEUE() in linked sql-file above)</p>