Subscribe Services

Examples

HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002

B-5

Scenario 2. The Invoices Queue

Invoices are submitted to request payment for a service. In this scenario, an invoice

must be processed exactly once and therefore should be received by only one server.

The Invoices Table

Suppose the invoices table is defined as:

CREATE TABLE pubs.invoices

(contractnbr INT, amount INT, priority INT);

The invoices table does not have a user-defined clustering key. The Data Access

Manager guarantees that rows are inserted into the table with a consistently increasing

system-generated key, the SYSKEY.

Dequeueing Invoices

An application can use a SELECT statement specifying stream access to read and

delete entries with a single operation. The SELECT statement contains an embedded

DELETE operation that dequeues (removes) entries from a table and returns the

entries to the application.

In this scenario, an application can receive and dequeue invoices in any order by

issuing this SELECT statement:

SELECT * FROM (DELETE FROM STREAM(invoices)) AS invoices;

The invoices are selected and deleted in one operation.

For further detail, see Embedded DELETE on page 2-3 and Embedded UPDATE on

page 2-5. For this scenario, the dequeueing of invoices can be refined in these ways.

Skipping Rows Locked by Another Transaction

The skip conflict access mode allows applications to skip rows that are locked by a

concurrent transaction. This SELECT statement illustrates the skip conflict access

mode:

SELECT * FROM

(DELETE FROM STREAM(invoices)

FOR SKIP CONFLICT ACCESS) AS invoices;

This mode prevents transactions from blocking each other while other queue entries

are available. The entries are not necessarily retrieved in first-in, first-out (FIFO) order.

This mode of access implies what is sometimes referred to as semiqueue behavior.

Waiting for Rows Locked by Another Transaction

In contrast to the skip conflict access mode, an application can use read committed

access to ensure transactions do not skip an invoice currently locked by another