Committing Without Freeing a Transaction

Go Up to Using COMMIT

To write transaction changes to the database without releasing the current transaction snapshot, use the RETAIN option with COMMIT. The COMMIT RETAIN statement commits your work and opens a new transaction, preserving the old transaction’s snapshot. In a busy multi-user environment, retaining the snapshot speeds up processing and uses fewer system resources than closing and starting a new transaction for each action. The disadvantage of using COMMIT RETAIN is that you do not see the pending transactions of other users.

The syntax for the RETAIN option is as follows:

EXEC SQL
COMMIT [TRANSACTION name] RETAIN [SNAPSHOT];

Tip:
Developers who use tools such as Delphi use this feature by specifying “soft commits” in the BDE configuration.

For example, the following C code fragment updates the POPULATION column by user-specified amounts for cities in the CITIES table that are in a country also specified by the user. Each time a qualified row is updated, a COMMIT with the RETAIN option is issued, preserving the current cursor status and system resources.

. . .
EXEC SQL
BEGIN DECLARE SECTION;
char country[26], city[26], asciimult[10];
int multiplier;
long pop;
EXEC SQL
END DECLARE SECTION;
. . .
main ()
{
EXEC SQL
DECLARE CHANGEPOP CURSOR FOR
SELECT CITY, POPULATION
FROM CITIES
WHERE COUNTRY = :country;
printf("Enter country with city populations needing adjustment: ");
gets(country);
EXEC SQL
SET TRANSACTION;
EXEC SQL
OPEN CHANGEPOP;
EXEC SQL
FETCH CHANGEPOP INTO :city, :pop;
while(!SQLCODE)
{
printf("City: %s Population: %ld\n", city, pop);
printf("\nPercent change (100%% to -100%%:");
gets(asciimult);
multiplier = atoi(asciimult);
EXEC SQL
UPDATE CITIES
SET POPULATION = POPULATION * (1 + :multiplier / 100)
WHERE CURRENT OF CHANGEPOP;
EXEC SQL
COMMIT RETAIN; /* commit changes, save current state */
EXEC SQL
FETCH CHANGEPOP INTO :city, :pop;
if (SQLCODE &amp;&amp; (SQLCODE != 100))
{
isc_print_sqlerror(SQLCODE, isc_$status);
EXEC SQL
ROLLBACK;
EXEC SQL
DISCONNECT;
exit(1);
}
}
EXEC SQL
COMMIT;
EXEC SQL
DISCONNECT;
}

Note:
If you execute a ROLLBACK after a COMMIT RETAIN, it rolls back only updates and writes that occurred after the COMMIT RETAIN.

Important:
In multi-transaction programs, a transaction name must be specified for COMMIT RETAIN, except when retaining the state of the default transaction. For more information about transaction names, see Naming Transactions.

Advance To:

Using ROLLBACK