How often should I commit?
Posted by Harald van Breederode on April 28, 2009
A recurring question during courses I teach is “How often should I commit?”, and the answer is really simple: At most once per transaction! This is generally not what people expect because I almost always respond to questions with: "It depends".
The underlying question is probably “How big should I make my transaction?”. The answer to that is also quite easy. A transaction should be a logical unit of work that should succeed or fail completely but never something in between. A transaction should move the database from one consistent state into another consistent state. A logical unit of work should generally follow what the business dictates as a logical amount of work. For example, the creation of a new customer in an order entry system or the deletion of a cancelled order. Usually a transaction manipulates a relevant small amount of data but it can become quite big, especially in a data warehouse environment. But even then, I strongly belief that if you have to manipulate millions of rows you should still do it in just one transaction.
Quite often the reaction to the above is that their DBA (this question is mostly asked by developers) told them to COMMIT more frequently because huge transactions cause problems in the UNDO tablespace. Again, I strongly believe one should not compromise the concept of a transaction and it is the responsibility of the DBA to make it possible that the database can handle the transaction size the business needs.
The only exception that I am willing to accept under very strict conditions is when there is a clear defined method on how to revert the database back to a consistent state after an unforeseen failure. For example, when you have to UPDATE 42 million rows and you decided to COMMIT every one million rows, and after processing 21.5 million rows a power failure occurs, you have to be able to either ‘rollback’ the 21 million updated (and committed) rows or finish the remaining 21 million rows yourself. However keep in mind that while all the above takes place other sessions will see inconsistent data! This should be avoided at all times and therefore an incrementally committing ‘solution’ is only acceptable if you are the only user accessing the data involved. The only real solution is to configure the database so it can handle transactions of the size dictated by the business.