Timeouts

A timeout is DB2 giving up on a lock wait. The program waited longer than the allowed time, so DB2 ends the wait instead of hanging forever.

What is a lock timeout

• When a program waits for a lock longer than a predefined time, DB2 terminates the request with SQLCODE -911 or -913.
• The wait limit is set by the DBA in the DB2 system parameter for lock timeout (IRLMRWT). A common value is 30 to 60 seconds.
• A timeout means "someone else is holding the lock and not letting go". It is usually caused by a long-running uncommitted transaction.
• SQLCODE -904 (resource unavailable) is different. -904 means the object itself is stopped or restricted, not that a lock wait timed out.

Timeout vs deadlock

• A deadlock is a circular wait: A waits for B and B waits for A. DB2 detects it quickly and picks a victim.
• A timeout is a one-way wait: A waits for B, but B is simply slow. DB2 only finds out when the timer expires.
• Both report -911 or -913, so check the reason code and the DB2 log to tell them apart.
• Frequent timeouts point to long transactions. Frequent deadlocks point to bad access order or lock promotion.

Reducing timeouts

• Commit often. Every COMMIT releases the locks of that unit of work, so other programs stop waiting.
• Keep transactions short. Do not hold locks across user think time or across slow file I/O.
• Do not run heavy batch updates against tables that online users need at the same time.
• Use the weakest isolation level your program can accept, so it takes fewer and shorter locks.
• Example of committing inside a batch loop:-
  PERFORM UNTIL WS-END-OF-FILE PERFORM PROCESS-ONE-ROW ADD 1 TO WS-ROW-COUNT IF WS-ROW-COUNT = 100 EXEC SQL COMMIT END-EXEC MOVE 0 TO WS-ROW-COUNT END-IF END-PERFORM EXEC SQL COMMIT END-EXEC