Multi-query Transactions and Performance
Remember that a transaction accumulates locks on resources until you roll back or commit. A transaction that is waiting for locks, or that is not waiting for a lock but nevertheless seems unusually slow, can be using excessive server or system resources.
Here are suggestions:
• Keep your transactions as short as possible.
• Commit your transactions quickly:
– You create large multi-query transactions (MQTs) unless you use SET AUTOCOMMIT ON or COMMIT after each statement. Statements accumulate as one multi-query transaction until you commit.
– MQTs must not include prompts that hang the transaction until a user responds, or sleeps that prevent your transaction from being released quickly.
• Avoid bottlenecks in your transaction such as:
– Insert to heap table with secondary indexes
– Counter table updates
– Iterative deletes
– Unbounded long iterations