Transactions affect performance because they hold locks until
they are terminated with a COMMIT WORK or ROLLBACK WORK statement.
If the transaction contains processing that could actually be
performed outside the transaction, this processing takes time,
which means that locks may be held longer than needed, which
reduces concurrency and degrades performance. Such extra
processing should be removed from the transaction.
Whenever possible, avoid holding locks around terminal reads.
As a general rule, all user input should be retrieved before the
start of the transaction. This helps to keep the transaction as
short in duration as possible, and it has the following advantages:
Locks are not held around user prompts, thus avoiding
application "hangs."
The result of the transaction can be viewed and used by other users
sooner, thus improving throughput.
When user input must be accepted during a transaction, you can
use ALLBASE/SQL features that help avoid excessive locking. In
cursor operations, you can
use KEEP CURSOR WITH NOLOCKS when reading data.
For cursor or non-cursor operations, you can use the RC or RU isolation level.
When using these options, you can use the REFETCH statement to acquire
locks and revalidate data before updating tuples.
