ALLBASE/SQL Reference Manual (36216-90216)

90 Chapter2

Using ALLBASE/SQL

Managing Transactions

aborted. The timeout action can also be set to abort the command being processed instead

of the entire transaction. Set the timeout limit for the DBEnvironment with the STARTDBE

statement or the SQLUtil ALTDBE command. To specify a timeout limit for a particular

session, use the SET USER TIMEOUT statement. Both SET SESSION and SET TRANSACTION

have parameters to specify which action the system should take when a timer expires. The

setting of timeout values is also incorporated into these commands. The SQLUtil SHOWDBE

command displays the current, default, and maximum values of the timeout parameter in

the DBECon ﬁle.

Monitoring Transactions

The SYSTEM.TRANSACTION pseudo-table contains the user identiﬁer, connection-id, session

identiﬁer, transaction identiﬁer, transaction priority, and isolation level of every current

transaction. To view this information with ISQL, issue the following statement:

isql=> SELECT * FROM System.Transaction;

To identify the transactions on the throttle wait queue, query the SYSTEM.CALL

pseudo-table as follows:

isql=> SELECT * FROM System.Call WHERE Status = 'Throttle wait';

For more information on transaction activity, consult Load subsystem in SQLMON, the

ALLBASE/SQL on-line monitoring tool. SQLMON provides the following transaction

information:

• total number of active and waiting transactions in the DBEnvironment

• total number of BEGIN WORK, COMMIT WORK, and ROLLBACK WORK statements executed

in the DBEnvironment

• maximum number of transactions conﬁgured

• which sessions have active or waiting transactions

• which sessions have executed BEGIN WORK, COMMIT WORK, and ROLLBACK WORK

statements

See the ALLBASE/SQL Performance and Monitoring Guidelines for more information on

SQLMON.

Tips on Transaction Management

Keep transactions short. As the length of a transaction increases, so does the chance that

other transactions are forced to wait for the locks it holds. In addition to increasing

concurrency, short transactions minimize the amount of data that must be re-entered after

a system crash. When archive logging is in effect, changes made to the database are

written to the log ﬁle whenever a COMMIT WORK is issued. If the system crashes during a

long transaction, a large number of uncommitted changes will be rolled back.

To shorten a transaction, place program statements not essential to the logical unit of

work outside of the transaction. Retrieve all user input before the start of a transaction, to

ensure that locks are not held if the user walks away from the terminal. Because terminal

writes can also be time consuming, they should not be performed within a transaction.