DB2 Locking, Part 9: Avoid Bachelor Programming Syndrome

Unless you plan for, and issue regular COMMITs in your DB2 application programs, you will be causing locking problems. It is important for every programmer to issue COMMIT statements in all application programs where data is modified (INSERT, UPDATE, and DELETE). A COMMIT externalizes the modifications that occurred in the program since the beginning of the program or the last COMMIT. A COMMIT ensures that all modifications have been physically applied to the database, thereby ensuring data integrity and recoverability.

Failing to code COMMITs in a data modification program is what I like to call Bachelor Programming Syndrome — or in other words, fear of committing.

One important factor affecting the need for a COMMIT strategy is the amount of elapsed time required for the program to complete. The greater the amount of time needed, the more important it becomes to issue periodic COMMITs. A sound COMMIT strategy will reduce rollback time and reprocessing time in the event of program failure. It is a safe assumption that the elapsed time increases as the number of modifications increases.

Issuing COMMITs in an application program is important for three reasons:

1. If the program fails, all the modifications are backed out to the last COMMIT point. This process could take twice the time it took to perform the modifications in the first place—especially if you are near the end of a program with no COMMITs that performed hundreds or thousands of modification operations.
2. If you resubmit a failing program that issues no COMMITs, the program redoes work unnecessarily.
3. Programs using the repeatable read page locking strategy or the RELEASE(COMMIT) table space locking strategy hold their respective page and table space locks until a COMMIT is issued. If no COMMITs are issued during the program, locks are not released until the auto-COMMIT when the program completes, thereby negatively affecting concurrent access. This can cause lock timeouts and lock escalation.

If concurrent batch or online access uses uncommitted read (UR) isolation, sometimes referred to as dirty reads, COMMIT processing is irrelevant. This is so because uncommitted read isolation does not take any locks. However, most processing requires accurate data and, as such, will not use uncommitted read isolation.

If your program is running for a long time and has issued no COMMITs, your program will probably not suffer. However, others attempting to access the data that is locked by your program will suffer. This will manifest itself with lock timeout errors (such as -911 and -913 SQL return codes). If you see a lot of lock timeouts occurring it is probably a good bet that you have at least one long-running program (maybe more) that needs to have COMMIT logic added.

It is also good programming practice to issue periodic COMMITs in long-running read-only programs. Even though data is not being changed, some databae management systems, such as DB2 for z/OS, acquire read locks whenever data is accessed. A read lock can block other programs (such as utility operations) from running successfully. Therefore, failure to COMMIT, even in a read only program, can result in locking problems.

In some rare circumstances, you might be able to avoid issuing COMMITs in your database programs. But you should only consider doing so for programs that run very quickly, and therefore will not hold locks for a long duration.

Given the tremendous benefits to your DBMS environment that can be gained by assuring a proper COMMIT strategy, why would you ever consider avoiding COMMITs? Perhaps the simplest argument is that the code is easier to write without COMMITs. Additionally, recovery is simpler. When modification programs without COMMITs fail, you can generally restart them from the beginning because database changes have not been committed. Of course, this might not be true if you have modified data not controlled by the DBMS (in which case dual modifications may cause problems).

If you choose to forgo COMMITs in any application program it is wise to clearly document why there are no COMMITs being issued. That way, if the program is changed later rendering the original assumptions inaccurate, adding COMMITs can be considered using the original reasoning accompanied by the added processing changes.

I recommend that you plan to issue COMMITs in every batch program... even read only programs. The COMMIT will release claim requests, which can free up DB2 utilities to execute, thereby improving overall availability.

You can structure the logic so that the COMMIT processing is contingent on a parameter passed to the program. This approach enables an analyst to turn off COMMIT processing but ensures that all batch programs are prepared if COMMIT processing is required in the future.