SQLCODE -911

What SQLCODE -911 means

• -911 means deadlock or timeout. The SQLSTATE is 40000.
• DB2 has already performed a ROLLBACK for you. The unit of work is undone.
• Your program can simply retry the work from the last commit point.

Why -911 happens

• Two programs lock the same rows or tables in opposite order. That is a deadlock.
• A program waits for a lock longer than the timeout limit. That is a timeout.
• Long units of work hold locks and block other programs.

Handling -911 - retry the unit of work

• Since DB2 rolled back, re-establish position and retry the unit of work.
• Remember: rollback closes all cursors, so re-open them before retrying.
• Limit retries, for example 3 attempts, so a persistent problem does not loop forever.
• Reduce the chance of -911: commit frequently, access tables in the same order everywhere, keep units of work short.
• Example retry loop:
  
  MOVE 1 TO WS-RETRY-COUNT PERFORM UNTIL WS-RETRY-COUNT > 3 EXEC SQL UPDATE EMP SET SALARY = :WS-NEW-SAL WHERE EMPNO = :WS-EMPNO END-EXEC EVALUATE SQLCODE WHEN 0 EXEC SQL COMMIT END-EXEC MOVE 4 TO WS-RETRY-COUNT WHEN -911 DISPLAY 'Deadlock/timeout - retrying...' ADD 1 TO WS-RETRY-COUNT WHEN OTHER DISPLAY 'Update failed. SQLCODE = ' SQLCODE STOP RUN END-EVALUATE END-PERFORM.