17. Improving Database and Query Performance : Locking and Concurrency Issues : Multi-query Transactions and Performance
 
Share this page                  
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