Do Not Commit Unnecessarily

David Clement

September 2004

Oracle versions 7-8

Overusing the 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.

Each unnecessary 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.

A unnecessary 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.