Do Not Commit Unnecessarily
Oracle versions 7-8
commit statement can cause a number of
problems. The ORA-01555 error is one of them. Since
commit is the end of a transaction, the next SQL
statement will incur the overhead of a new rollback segment snapshot.
At the same time, the rollback segment tail will pull away from the
transaction just committed, which now has a chance of being
overwritten by the rollback segment head.
commit also slows down the system.
Because a transaction has to be durable (the D in ACID) the database
calls the LGWR process to write the redo logs to disk on every
commit. The session then pauses on the
log file sync
wait event. On a busy system, with considerable disk I/O, this can be
a serious drag on performance.
commit within a transaction also incurs
the risk that a rollback or a recovery will violate transaction
atomicity (the A in ACID). That is, if the transaction is saved
part-way through, then after any system error, part of it may be
rolled back, or part of it may be recovered. Transactions are
generally meant to be logical units of work (LUWs) such as generating
the bill for a meter, approving a medical claim, printing an invoice,
and so on, so they really should not be processed part-way through.
In the worst case, a
commit within an LUW might cause
ordinary hardware faults (bad phone connections and the like) to
become significant business risks.
In a nutshell,
commit ends a transaction, and should
only be used as the end of a transaction.