Informix Guide to SQL: Tutorial
Chapter 7: Programming for a Multiuser Environment
Home
Contents
Index
Master Index
New Book
Hold Cursors
When transaction logging is used, the database server guarantees that anything done within a transaction can be rolled back at the end of it. To do this reliably, the database server normally applies the following rules:
All cursors are closed by ending a transaction.
All locks are released by ending a transaction.
These rules are normal with all database systems that support transactions, and they do not cause any trouble for most applications. However, circumstances exist in which using standard transactions with cursors is not possible. For example, the following code works fine without transactions. However, when transactions are added, closing the cursor conflicts with using two cursors simultaneously.
EXEC SQL DECLARE master CURSOR FOR . . .
EXEC SQL DECLARE detail CURSOR FOR . . . FOR UPDATE
EXEC SQL OPEN master;
while(SQLCODE == 0)
{
EXEC SQL FETCH master INTO . . .
if(SQLCODE == 0)
{
EXEC SQL BEGIN WORK;
EXEC SQL OPEN detail USING . . .
EXEC SQL FETCH detail . . .
EXEC SQL UPDATE . . . WHERE CURRENT OF detail
EXEC SQL COMMIT WORK;
}
}
EXEC SQL CLOSE master;
In this design, one cursor is used to scan a table. Selected records are used as the basis for updating a different table. The problem is that when each update is treated as a separate transaction (as the pseudocode in the previous example shows), the
COMMIT WORK
statement following the
UPDATE
closes all cursors, including the master cursor.
The simplest alternative is to move the
COMMIT WORK
and
BEGIN
WORK
statements to be the last and first ones, respectively, so that the entire scan over the master table is one large transaction. Treating the scan of the master table as one large transaction is sometimes possible, but it can become impractical if many rows need to be updated. The number of locks can be too large, and they are held for the duration of the program.
A solution that Informix database servers support is to add the keywords
WITH HOLD
to the declaration of the master cursor. Such a cursor is referred to as a
hold cursor
and is not closed at the end of a transaction. The database server still closes all other cursors, and it still releases all locks, but the hold cursor remains open until it is explicitly closed.
Before you attempt to use a hold cursor, you must be sure that you understand the locking mechanism described here, and you must also understand the programs that are running concurrently. Whenever
COMMIT WORK
is executed, all locks are released, including any locks placed on rows fetched through the hold cursor.
The removal of locks has little importance if the cursor is used as intended, for a single forward scan over a table. However, you can specify
WITH HOLD
for any cursor, including update cursors and scroll cursors. Before you do this, you must understand the implications of the fact that all locks (including locks on entire tables) are released at the end of a transaction.
Informix Guide to SQL: Tutorial
, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.