Resolving DB2 SQLCODE -911 (Reason 00C90088 / Deadlock) and -904 Resource Unavailable
Quick Diagnostic Summary: What Causes Resolving DB2 SQLCODE -911 (Reason 00C90088 / Deadlock) and -904 Resource Unavailable?
DB2 SQLCODE -911 indicates your unit of work was rolled back due to a deadlock (Reason 00C90088) or lock timeout (Reason 00C9008E). SQLCODE -904 indicates an unavailable resource (tablespace locked, thread limit exceeded). Discover root causes and concurrency tuning.
1 Incident Symptoms: What Triggers Resolving DB2 SQLCODE -911 (Reason 00C90088 / Deadlock) and -904 Resource Unavailable?
Production transactions and overnight batch update suites crash with:
DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE
TO DEADLOCK OR TIMEOUT. REASON 00C90088, TYPE OF RESOURCE 00000302,
RESOURCE NAME DSN8D13A.DSN8S13E
DSNT418I SQLSTATE = 40001 SQLSTATE RETURN CODE
Or in peak periods:
DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE
RESOURCE. REASON 00C90084, TYPE 00000200, NAME DSN8D13A
Application threads are terminated, requiring manual transaction re-submits and delaying financial closing windows.
2 Technical Root Cause & Architecture Mechanics
DB2 for z/OS concurrency is orchestrated by the Internal Resource Lock Manager (IRLM):
1. SQLCODE -911 / Reason 00C90088 (Deadlock): Two threads requested exclusive (X) or update (U) locks on database pages or rows held by each other in cyclical dependency. IRLM selects one as the 'victim' and rolls back its uncommitted SQL statements.
2. SQLCODE -911 / Reason 00C9008E (Timeout): A thread waited longer than the IRLM timeout value (defined in DSNZPARM IRLMRWT, typically 30 or 60 seconds) for a lock to be released.
3. SQLCODE -904 / Reason 00C90084 (Max Lock Escaped / Space Limit): A utility (e.g., REORG, LOAD) has exclusive access to the tablespace, or maximum active threads (CTHREAD / MAXDBAT) were exhausted.
3 Step-by-Step Diagnostic & Code Resolution
# 1. Identify the Competitor Threads via DB2 Diagnostic Logs
Query the MVS Syslog or DB2 MSTR/DIST address space logs for message DSNT375I / DSNT376I:
DSNT375I PLAN=PLNPAY01 WITH CORRELATION ID=PAYJOB1 IS HOLDING A LOCK
DSNT376I PLAN=PLNWEB01 WITH CORRELATION ID=ODBC_SRV WAS WAITING
This isolates the exact two programs colliding on resource DSN8S13E.
# 2. Implement Retry Logic in Application Code
For online transactions and MQ/Kafka listeners, catch -911 and retry before failing:
EXEC SQL
UPDATE ACCOUNT_BALANCE
SET BALANCE = BALANCE - :WS-TX-AMOUNT
WHERE ACCOUNT_ID = :WS-ACCT-ID
END-EXEC.
IF SQLCODE = -911
ADD 1 TO WS-RETRY-COUNT
IF WS-RETRY-COUNT <= 3
* Small exponential backoff
CALL 'CEESGL' ...
PERFORM 1000-REEXECUTE-TX
ELSE
PERFORM 9900-LOG-DEADLOCK-ABEND
END-IF
END-IF.
# 3. Enforce Frequent Commits in Batch Jobs
Never run long-running batch updates without periodic COMMIT:
ADD 1 TO WS-RECS-SINCE-COMMIT
IF WS-RECS-SINCE-COMMIT > 1000
EXEC SQL COMMIT END-EXEC
MOVE 0 TO WS-RECS-SINCE-COMMIT
END-IF.
# 4. Optimize Concurrency Bind Options
Bind packages with:
-
ISOLATION(CS)(Cursor Stability) -
CURRENTDATA(NO) -
CONCURRENTACCESSRESOLUTION(USECURRENTLYCOMMITTED): Allows queries to read the previous committed version of a row rather than waiting for an in-flight update lock to release!
Diagnostic Reference Matrix
| Attribute | Specification |
|---|---|
| Target Subsystem | IBM z/OS 2.4 - 3.1 • DB2 SQL & Performance |
| Error Signature | DB2, SQLCODE -911, SQLCODE -904 |
| Resolution SLA | < 15 Minutes via Verified StackMF Runbook |
| Technical Reviewer | Anshu, Chief Technology Architect • StackMF Architecture Pod |
Architectural Prevention & Performance Tuning Checklist
- Bind all read-heavy queries with `CONCURRENTACCESSRESOLUTION(USECURRENTLYCOMMITTED)` to eliminate reader-writer contention.
- Always update tables in a uniform alphabetical and key order across all application components.
- Tune DSNZPARM `IRLMRWT` (Resource Wait Time) and `DEADLOK` sweep frequencies appropriately.
- Ensure batch programs commit every 500 to 2,000 updates to release row/page locks.
? Frequently Asked Questions
What is the root cause of Resolving DB2 SQLCODE -911 (Reason 00C90088 / Deadlock) and -904 Resource Unavailable? ↓
How do you resolve Resolving DB2 SQLCODE -911 (Reason 00C90088 / Deadlock) and -904 Resource Unavailable in production? ↓
How can teams prevent Resolving DB2 SQLCODE -911 (Reason 00C90088 / Deadlock) and -904 Resource Unavailable in enterprise pipelines? ↓
Recommended Technical Runbooks
DB2 z/OS Query Optimization: Converting Stage 2 (Residual) into Stage 1 (Indexable) Predicates
How Outdated RUNSTATS Cause Catastrophic Tablespace Scans in DB2 for z/OS
Preventing DB2 Lock Escalation: Tuning LOCKSIZE, Isolation Levels, and SKIP LOCKED DATA
Authoritative Reference Documentation
Official IBM manuals, Redbooks, and vendor technical advisories:
This diagnostic runbook is published by StackMF Technologies LLP for educational and architectural reference only. All code snippets, JCL, and procedures are provided "AS IS" without warranty of any kind. Always test changes thoroughly in non-production sysplex environments prior to production rollout.
IBM, z/OS, CICS, Db2, IMS, RACF, and IDz are registered trademarks of International Business Machines Corporation. Broadcom, CA-7, and Endevor are trademarks of Broadcom Inc. All other trademarks belong to their respective owners and are referenced under the Nominative Fair Use Doctrine (US Lanham Act 15 U.S.C. ยง 1125 / Section 30 of the Indian Trade Marks Act, 1999) solely for technology compatibility and diagnostic identification. StackMF Technologies LLP is an independent consulting entity not affiliated with or endorsed by these vendors. View Full Legal & IP Policy →
Struggling with Critical Mainframe Incidents or Vendor Renewal Pressure?
StackMF deploys certified Senior Mainframe Engineers fluent in both z/OS legacy internals (COBOL, DB2, CICS, VSAM, CA-7, Endevor) and modern cloud stacks (React, Kafka, AWS, Git). Onboard dedicated pods in 48 hours or cut Broadcom licensing by 60%.