SQLCODE -911 / -913 — deadlock or timeout

What happened

A batch job or transaction gets SQLCODE -911 or -913 on an otherwise normal statement. At the same moment, the Db2 master address space log shows a deadlock or timeout message naming both parties.

DSNT408I SQLCODE = -911, ERROR:  THE CURRENT UNIT OF WORK HAS BEEN
         ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON 00C9008E,
         TYPE OF RESOURCE 00000302, AND RESOURCE NAME DBPAY01.TSPAY01.X'00001A'
DSNT418I SQLSTATE   = 40001 SQLSTATE RETURN CODE
...
DSNT376I  PLAN=PAYPLAN WITH CORRELATION-ID=PAYB010 ... TIMED OUT
          ONE HOLDER OF THE RESOURCE IS PLAN=ONLPLAN ...

What it means

Db2 uses locks to keep data consistent. A timeout happens when your unit of work waits longer than the site's limit for a lock someone else holds. A deadlock happens when two units of work each hold a lock the other needs; Db2 picks a victim.

The SQLERRMC tokens give the reason code, resource type and resource name. Reason 00C90088 is a deadlock and 00C9008E is a timeout. Whether you see -911 or -913 can depend on the attachment and its settings, for example CICS thread definitions.

Typical causes

• Long units of work: a batch job that commits rarely and holds thousands of locks.
• Batch running inside the online window against tables that online transactions update.
• Two programs updating the same tables in a different order.
• Unneeded strong isolation (RR) or lock escalation to table space level.
• Hot rows or pages, such as a single control or counter row everyone updates.

Symptoms

Errors are intermittent and timing-related. A rerun often works. They cluster at busy times. Unlike -904, the resource is available, just busy; unlike -803, the data itself is fine.

Where to look

• The program's DSNTIAR or GET DIAGNOSTICS output in SYSOUT.
• The Db2 MSTR log (via SDSF): DSNT375I for deadlocks and DSNT376I for timeouts name both plans and correlation IDs.
• Monitoring tools or accounting traces for lock wait time, if your site has them.
• The batch schedule: what else was running at the same time.

How to diagnose

1. Record the SQLCODE, reason code, resource type and resource name.
2. Find the matching DSNT375I or DSNT376I message by time and identify the other party.
3. Decide: deadlock (00C90088) or timeout (00C9008E)?
4. For timeouts, check how long the holder had gone without a COMMIT.
5. For deadlocks, compare the access order of the two programs.
6. Check isolation level and whether lock escalation occurred.

How to fix

For -913, issue an explicit ROLLBACK first (in CICS, EXEC CICS SYNCPOINT ROLLBACK; in IMS, the IMS rollback call). For both codes, retry a small bounded number of times with a short pause, then fail cleanly with a clear message. Never retry in a tight loop.

For a recurring problem, commit more often, reorder access, lower isolation where safe, or move the batch out of the online window. To turn the raw SQLCA into readable text, call DSNTIAR (DSNTIAC in CICS) with the SQLCA and a message buffer, or use GET DIAGNOSTICS to fetch MESSAGE_TEXT, RETURNED_SQLSTATE and DB2_RETURNED_SQLCODE. Either way, write the full message to the job log before the program ends.

How to prevent

• Commit on a sensible interval and keep a checkpoint for restart.
• Access shared tables in a consistent order across programs.
• Use the weakest isolation that is still correct.
• Schedule heavy update batch outside peak online hours.
• Build bounded retry logic into the standard error routine.

Production considerations

Interview question

What is the difference between SQLCODE -911 and -913, and how should a program react to each?