SQLCODE -911 deadlock or timeout

SQLCODE -911: deadlock or timeout — your unit of work was rolled back because it deadlocked with another, or waited too long for a lock.

What it means

• After every EXEC SQL, DB2 returns a SQLCODE: 0 means success, +100 means no row found, and negative values mean an error.

Common causes

• Two programs updating the same rows in opposite order (classic deadlock).
• Lock wait exceeded the timeout (IRLMRWT) — another job holds the lock.
• Missing COMMITs: a long unit of work holds locks and collides with others.
• Lock escalation turning many row locks into one table lock.

How to diagnose

• Check SQLERRM for deadlock vs timeout detail.
• Find the competing job: -DISPLAY THREAD(*) output shows lock holders.
• Review commit frequency in both programs.
• Check whether the access path changed (new index or RUNSTATS).

How to fix it

• Retry the unit of work — deadlocks are often transient.
• Add frequent COMMITs to shrink the lock window.
• Access tables in the same order in all programs.
• Tune the timeout value only after fixing the application pattern.

Example: Check SQLCODE after every statement

Retrying after ROLLBACK is the standard -911 recovery.

Interview tip

• Q: How would you troubleshoot a SQLCODE -911 deadlock or timeout in production?
• A: I would check the SQLCODE and SQLERRM immediately after the failing statement, reproduce in SPUFI, then apply the specific fix.

Prevention checklist

• Always check SQLCODE after every EXEC SQL statement.
• Log the SQLCODE, SQLSTATE and failing statement for support.