Commit Frequency

One of the most common questions regarding transactions is "how often should my program commit?" The answer, as with most programming questions, is that it depends.

There are two extreme positions. One extreme is that the program can do all of its processing in a single transaction. Programs, such as reports, that are written without explicit transactions fall into this category. Performing all work in a single transaction minimizes the transaction setup/teardown overhead but it maximizes the possibility of causing lock contention and other problems in the database. The other extreme is that the program can commit after each record that it processes. This minimizes lock contention but it maximizes the transaction setup/teardown overhead and can lead to inconsistent data if not done correctly.

It is the responsibility of the programmer to ensure that the application data is consistent before committing and to balance transaction overhead vs lock contention.

As a rule of thumb, if multiple programs are going to be concurrently updating the same data, your program should commit more frequently - like every second. If only a single program is going to be updating the data, then 10 to 20 seconds between commits is reasonable. Read-only programs can go slightly longer but should still commit every minute.

Another rule of thumb relates to the volume of data that can be efficiently processed in a single transaction. On an active system being accessed by many different users performing different kinds of work, your program should not consume an excessive amount of system resources or it will negatively impact other users. The threshold for "excessive" is constantly changing, but currently a database should be able to update at least 50 MiB of data in a single transaction without adversly affecting other users.