SQL/MX Queuing and Publish/Subscribe Services

Embedded SQL Examples
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
3-10
Managing Transactions With the Stream Timeout
Attribute
Managing Transactions With the Stream
Timeout Attribute
A statement selecting from a stream can wait for new rows for a very long duration—
hours or even days or weeks. Unless the statement is also using READ
UNCOMMITTED access, a transaction will be started by the system, when the
statement begins executing, or by the application before execution of the statement.
The transaction will consume resources, and locks will be held. Eventually, the
transaction might be aborted by the system if it exceeds the TMF autoabort threshold.
This subsection shows how to write a publish/subscribe application to limit the
transaction duration. Two methods are shown:
•
The first method uses the STREAM_TIMEOUT configuration to limit the
transaction duration to an interval bounded by the STREAM_TIMEOUT value.
•
The second method uses two statements to avoid beginning a transaction until
rows are available in the stream.
The first method is simpler to code. The second is more effective in avoiding
unproductive use of transaction resources and locks.
You can configure the STREAM_TIMEOUT value to limit the time a statement waits for
a new row to appear in a stream. When the statement is finished waiting, the
application can commit the transaction, start a new transaction, and resume waiting. If
a cursor is used, the cursor can be declared using the WITH HOLD clause so that the
application need not reopen the cursor. In this way, the cursor can maintain its position
in the stream. For examples of a WITH HOLD cursor, see the embedded DELETE or
UPDATE examples in Embedded DELETE on page 3-3 or Embedded UPDATE on
page 3-4.
This example shows STREAM_TIMEOUT being used with a SELECT INTO statement
using an embedded DELETE and a stream. The longest transaction generated by the
code will not exceed approximately 30 seconds.
---------------------------------------------------------------
#include "stdio.h"
#include "stdlib.h"
#include "assert.h"
const int TRUE = 1;
const int FALSE = 0;
/* This global variable is set to TRUE by the
handle_error_or_timeout() function. It is read
and reset to FALSE by the main() function. */
int timed_out = FALSE;
void handle_error_or_timeout(void);