Commits, locking and why batch jobs collide

DB2 protects data by locking it. Locks are held until you commit. A batch job that updates a million rows without committing holds a million locks, blocks everything else, and eventually falls over. This is the most common DB2 performance problem in batch.

The unit of work

Everything between one commit and the next is a unit of work. DB2 guarantees it happens completely or not at all. COMMIT makes it permanent and releases locks; ROLLBACK undoes it.

Isolation levels

Level	Name	Behaviour
UR	Uncommitted read	Reads data others have not committed. Fastest, and may be wrong. Fine for reporting on stable data.
CS	Cursor stability	The default. Holds a lock only on the row being processed.
RS	Read stability	Rows you have read stay stable; new matching rows can still appear.
RR	Repeatable read	The strictest. Holds locks on everything read. Highest integrity, worst concurrency.

Commit frequency in batch

A batch program updating many rows should commit periodically — every few thousand rows is typical. The trade-off is real in both directions:

Choosing a commit interval

Commit rarely

Fewer commits, less overheadLocks held a long timeOther work is blockedRestart replays a lot of work

Commit often

Locks released quicklyGood concurrencyMore commit overheadRestart loses little

Restartability is the other half

Committing means that a failure halfway through leaves half the work done. A restartable batch program therefore keeps a restart record: it stores the key it last processed in a control table, commits that in the same unit of work as the data, and on restart reads it back and resumes from there.

The commit-and-checkpoint pattern

       2000-PROCESS-ROW.
           PERFORM 2100-UPDATE-ACCOUNT
           ADD 1 TO WS-SINCE-COMMIT
           IF WS-SINCE-COMMIT >= WS-COMMIT-FREQ
               PERFORM 2900-CHECKPOINT
           END-IF.

       2900-CHECKPOINT.
           EXEC SQL
               UPDATE PAYPROD.RESTART_CTL
                  SET LAST_KEY = :WS-CURRENT-KEY,
                      ROWS_DONE = :WS-COUNT
                WHERE PGM_NAME = :WS-PGM-NAME
           END-EXEC
           EXEC SQL COMMIT END-EXEC
           MOVE ZERO TO WS-SINCE-COMMIT.

Recognising a locking problem

Repeated -911 or -913 in the job log, especially clustered in time.
A job that used to finish in 20 minutes now runs for hours with low CPU — it is waiting, not working.
Online transactions timing out while a batch job runs.
A DBA display showing many locks held by one thread.