Kbase P71359: What are the consequences of having large transactions
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/03/2004 |
|
Status: Unverified
GOAL:
What are the consequences of having large transactions
FIX:
Transaction and subtransaction scope define how much Progress rolls back in case of an error. Progress tracks database changes that occur during a transaction in the before-image (.bi or .bn) file shared by all users. Progress uses the before-image file for transaction crash-recovery.
Therefore large transactions reduce performance. Keep the transactions only as large as necessary and as small as possible.
If a transaction or subtransaction is rolled back, Progress also undoes the variables changed during the transaction. The values of the variables are restored to what they were before the transaction or subtransaction began.
Specifying NO-UNDO on some variables that may not need to be rolled back can be more efficient. For example, a placeholder variable is not critical to the database and does not need to be restored to its previous value because it is assigned each time the transaction is executed. NO-UNDO reduces the system resources needed to maintain previous values, thus improving runtime processing.
Transaction size also influences the number of locks held at any given time and the duration of those locks. For this reason large transaction can result in a large number of lock held by each client and therefore in a decreased level of concurrency.
Also a high number of lock held means an intensive use of the lock table that can bring to a table overflow and a subsequent error 915:
Lock table overflow, increase -L on server (915)
The recommendation is to keep transaction as smaller as possible, however, anytime you consider changing the size of a transaction, you should make sure that you are not sacrificing database integrity.