Avoiding long transactions

Long transactions consume resources and decrease concurrent data access.

Old applications based on IBM® Informix® database without transaction logging might perform long running SQL modifications.

With recent database engines, using huge transactions can lead to errors because of transaction log buffer overflow. For example, if a table holds many rows, a "DELETE FROM table" might produce a "snapshot too old" error in Oracle, if the rollback segments are too small.

Therefore, you must avoid long transactions when connected to a database using transactions:

• keep transactions as short as possible.
• access the least amount of data possible while in a transaction.
• split a long transaction into many short transactions. Use a loop to handle each block.
• to delete all rows from a table use the "TRUNCATE TABLE" instruction instead of "DELETE FROM" (Not for all vendors).
• In the end, increase the size of the transaction log to avoid filling it up.