Hold cursors

When transaction logging is used, HCL Informix® guarantees that anything done within a transaction can be rolled back at the end of it. To handle transactions reliably, the database server normally applies the following rules:

When a transaction ends, all cursors are closed.
When a transaction ends, all locks are released.

The rules that are used to handle transactions reliably are normal with most 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 statements, 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.