The Dutch Prutser's Blog

By: Harald van Breederode

  • Disclaimer

    The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 310 other followers

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.


3 Responses to “How often should I commit?”

  1. Gary said

    Some variations on this, especially in the old fahioned batch world.
    You may have a file with hundreds or thousands of business transactions. You don’t necessarily need to commit each one individually. In the ‘old’ days, you might batch them up and commit them every few hundred to speed up processing (ie reduce waits on the log writer). In newer versions, you can use COMMIT WRITE BATCH or COMMIT WRITE NOWAIT to have the same effect.

  2. Jaap van Lelieveld said

    I generally agree on your arguments. There is one thing I’d like to add though. Check if the business is aware of what they are asking for. This might help you to find the easiest and cheepest route to a solution. My statement is: a business riquirement should never be translated to a DB “statement” on a 1:1 bases. It is my experience the business is not aware of what they are asking for in terms of DB consequences and performance.

  3. Smurf said

    Hi Harald,

    Are you going to write about MySQL too, now Oracle is also owner of that wonderful database?


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: