Avoiding Long Transactions (LTX)

A transaction started in one particular logical log and is not committed, when the database server needs the same log for reuse said to be a long trasaction. In other words: When an active transaction reaches the long transaction high water mark (LTXHWM), it will be declared a long transaction (LTX) and hence will be rolled back automatically.

To prevent long transactions from developing, take the following precautions:

A. Ensure that the logical-log file does not fill too fast.
1. Size of the logical log
A smaller logical log fills faster than a larger logical log. You can increase the size by adding more logs.

2. Number of logical-log records
The more logical-log records written to the logical log, the faster it fills.

3. Type of log buffering
Databases that use unbuffered transaction logging fill the logical log faster than databases that use buffered transaction logging.

4. Size of individual logical-log records
The longer the data rows, the larger the logical-log records.

5. Frequency of rollbacks
More rollbacks fill the logical log faster.


B. Ensure that transactions do not remain open too long.
1. Transaction duration
Application designers should consider the transaction duration issue, and users should be aware that leaving transactions open can be detrimental. Develop certain mechanisms or control whereby you commit the batch of rows in consistent frequency. For huge inserts use dbload utility which offers user defined transaction commit facility. Syntax is as follows:

dbload -d <database>
-c <command file>
-l <error log file>
-e <num of errors(bad-row limits)>
-i <num of rows ignored>
-n <num of rows to commit(default 100)>
-r do not to lock table
-k lock table exclusively

A sample command file may look similar to following:
FILE "stock.unl" DELIMITER "|" 6;
INSERT INTO stock;

2. High CPU and logical-log activity
Repeated writes to the logical-log file increase the amount of CPU time that the database server needs to complete the transaction. Increased logical-log activity can imply increased contention of logical-log locks.


C. Set high-water marks (LTXHWM and LTXEHWM) to have the database server automatically slow down processing when a long transaction is developing. The default values for these configuration parameters are 50 and 60. These values eliminate any risk of a long transaction having too little log space in which to roll back. You may increase these parameters marginally and compensate its percentage by increasing the size of logical logs. To enable the logs-full high-water mark, set the LBU_PRESERVE configuration parameter to 1. When you set LBU_PRESERVE to 1, the database server blocks DB-Access, ESQL/C, and all other clients from generating log records in the last logical-log file when the logs-full condition is reached. The default value of LBU_PRESERVE is 0, or off.