When transaction logging is used, Dynamic Server 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:
Extended Parallel Server might not release locks at the end of a transaction. To demonstrate how to acquire a table lock, suppose the database server acquires a lock on all coservers that store a part of the table. If a transaction first acquires a SHARED mode table lock and tries to upgrade to EXCLUSIVE mode table lock, locks might not be released at the end of the transaction. This can happen if the transaction performs a SELECT and then performs an INSERT on a table with lock mode TABLE. In this case, the upgrade might succeed on some coservers and fail on other coservers. No attempt is made to roll back the successful upgrades, which means that the transaction might end with EXCLUSIVE locks on the table for some coservers.
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.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]