Diagnose a DB2 deadlock

Diagnose a deadlock

Think of two people who each hold one key the other person needs. Neither will put a key down until they get the missing key. That circle is a deadlock. In DB2, locks take the place of keys. Transaction A holds a lock on resource X and waits for Y. Transaction B holds Y and waits for X. IRLM watches for that pattern on its deadlock detection interval, picks a victim, and returns control so the survivor can continue.

Diagnosis is therefore detective work, not guesswork. You need to prove that the failure was a deadlock rather than a long lock wait, name the resources in the cycle, identify both participants, and then change either application design or operational timing so the cycle cannot form again. Live DISPLAY commands help when the wait is still happening. After the victim has already rolled back, historical messages and monitor data become the primary evidence.

Beginners often treat every -911 as "deadlock." That is incomplete. The same SQLCODE can mean timeout. The reason code separates the stories: 00C90088 for deadlock and 00C9008E for timeout. Your remediation differs. A timeout usually points at one long holder or an aggressive timeout value. A deadlock points at a circular lock order between two or more units of work.

Prerequisites

Before you start, make sure you can reach the subsystem where the failure happened and that you have authority for the displays and catalog queries your site allows. You also need a recent failure sample with enough detail to avoid fishing blindly through the entire day.

• Access to the DB2 subsystem (or data sharing group member) that returned the SQLCODE
• Permission to issue -DISPLAY THREAD, -DISPLAY DATABASE ... LOCKS / CLAIMERS, and to read SYSLOG or a mirrored console file
• The failing SQLCODE, SQLSTATE, reason code, resource type, resource name, plan or package, correlation ID, and approximate timestamp
• Optional but valuable: accounting or statistics monitors, IFCID 172 deadlock detail, or a product view of SQL errors for -911 / -913
• A test path where you can reproduce concurrency safely after you propose a fix

Steps

Step 1: Confirm the SQLCODE and reason code

Start with the application SQLCA or the batch / CICS message that recorded the failure. Confirm whether you have -911 (unit of work rolled back) or -913 (statement unsuccessful for deadlock or timeout). Then read the reason code. On many sites that value appears in SQLERRD(3) and in the human-readable message text. Record resource-type and resource-name at the same time. Those three fields are more important than the SQLCODE alone.

1
2
3
4
5
6
7
8
Illustrative tokens after a deadlock victim is chosen:
  SQLCODE      = -911
  SQLSTATE     = 40001
  reason-code  = 00C90088          <- deadlock
  resource-type / resource-name = (from message / SQLCA)
 
If reason-code = 00C9008E, treat the case as lock timeout first,
then continue with holder analysis rather than cycle analysis.

If the statement was a cursor FETCH, remember that DB2 may close the cursor as part of the failure. A later -501 can be a secondary symptom. Always diagnose the first deadlock or timeout code in the unit of work.

Step 2: Collect the console and message window

Search SYSLOG around the failure time for DSNT376I and related lock messages. Those messages often name both the timed-out or deadlocked requestor and at least one holder, including correlation IDs, connection names, LUW IDs, and in data sharing the member names. Copy a few minutes before and after the event so you can see whether a utility start, STOP DATABASE, or surge of batch work lined up with the cycle.

In a data sharing group, also note whether the partners ran on different members. Global contention and false contention can amplify waits, but a true deadlock still shows as a cycle of incompatible lock requests. Do not skip member identity when you write the incident notes.

Step 3: Identify live holders and waiters if the wait is still active

If users are still hung or a batch job is still waiting, issue displays immediately. Start with active threads, then lock and claimer detail for the objects named in the message.

1
2
3
4
5
6
-DISPLAY THREAD(*) TYPE(ACTIVE)
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS) LOCKS
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS) CLAIMERS
 
Optional IRLM status (use your IRLM procedure name):
F irlmproc,STATUS

LOCKS output distinguishes holders (H) from waiters (W) and shows lock modes such as IS, IX, S, U, X, and SIX. CLAIMERS matter when a utility or drain is involved: a drain waits for claim counts to drop, which can look like a hang even when ordinary row locking is not the whole story. IRLM STATUS confirms the deadlock interval and whether each DBMS is responsive.

Step 4: Map the cycle between participants

From the messages and displays, build a small table: participant A, locks held, locks waited; participant B, locks held, locks waited. A deadlock exists when those waits form a circle. Typical production patterns include:

• Program 1 updates ACCOUNT then ORDER; program 2 updates ORDER then ACCOUNT
• Parent and child tables updated in opposite foreign-key order by two jobs
• Hot parent rows updated while many child inserters escalate or take conflicting page or table locks
• A long CS or RR reader holding claims while a utility drains, while another updater waits on related locks

If you only know the victim, you are only halfway done. The surviving partner is equally important because that is the workload you must redesign or reschedule with the victim.

Step 5: Use historical deadlock detail when the cycle is already gone

After IRLM breaks the cycle, DISPLAY LOCKS may look quiet. That does not mean the incident was imaginary. Use IFCID 172 deadlock traces if your site collects them, or use a monitor that retains SQL error history for -911 / -913. Extract the SQL text or package section numbers for both sides when available. Then open the source or bind inventory and list the objects each path updates inside one unit of work.

1
2
3
4
5
6
7
8
9
Investigation notes template:
  Time:          2026-08-15-14.22
  Victim:        PLAN=PAYROLL CORR=JOB1234 reason=00C90088
  Partner:       PLAN=ORDERS  CORR=CICSABCD (from DSNT376I)
  Objects:       APP.ACCOUNT, APP.ORDER_HEADER
  Suspected order conflict:
    PAYROLL: ACCOUNT -> ORDER_HEADER
    ORDERS:  ORDER_HEADER -> ACCOUNT
  Next action:   enforce shared update order ACCOUNT then ORDER_HEADER

Step 6: Choose and apply a fix

Prefer design fixes over timeout tuning. Raising IRLMRWT or CURRENT LOCK TIMEOUT can hide timeouts for a while, but it does not remove a deadlock cycle. Useful fixes include:

• One documented lock order for every application that updates the same object set
• Shorter units of work with COMMIT after a controlled number of rows or business units
• Avoiding locks held across terminal think time, remote calls, or long report formatting
• Reviewing isolation level, WITH HOLD cursors, and SKIP LOCKED DATA only where the business rules allow
• Relieving hot-spot rows, oversized pages, or lock escalation that turns row fights into page or table fights

After the change, rerun a concurrency test that intentionally overlaps the two paths. Success means the SQLCODEs stop (or drop dramatically) under the same load profile, not merely that a single-thread smoke test still works.

Verify results

Verification has two layers: incident closure and lasting prevention. For the incident, confirm that your written cycle matches the captured reason code and message partners. For prevention, confirm that the redesigned lock order is present in every related program and that monitoring no longer shows the same pair of correlators fighting over the same resources.

1. Recheck the original failure tokens and label the case deadlock (00C90088) or timeout (00C9008E) explicitly in the ticket.
2. Confirm both participants and the contested resources are documented with evidence from DSNT376I, DISPLAY output, or IFCID / monitor detail.
3. After the fix, run an overlapping concurrency test and capture SQLCODE counts for -911 and -913.
4. Spot-check SYSLOG during the test window for absence of the previous DSNT376I pattern.
5. Update the application standard for lock order so the next developer does not reintroduce the opposite sequence.

Common errors

Another frequent mistake is retrying the same statement in a tight loop after -911 without understanding that the unit of work already rolled back. Any retry must redo the whole business unit from the last successful commit, with backoff, and with a clear limit. Blind retries can amplify contention and create a self-inflicted storm.

Finally, do not confuse claim/drain delays with classic row-lock deadlocks. Utility drains and long readers need DISPLAY CLAIMERS and utility scheduling analysis. The SQLCODE may still be -911 or -913, but the repair path is operational rather than "swap two UPDATE statements."

Explain it like I'm 5

Imagine you and a friend each have one crayon the other person needs. You will not give up your crayon until you get theirs, and they will not give up theirs until they get yours. You both wait forever until a teacher says one of you must put a crayon down and start over. That teacher is IRLM. Diagnosing the fight means writing down which crayons each of you held, then agreeing that next time everyone picks crayons in the same order so nobody gets stuck.

Exercises

1. Given SQLCODE -911, SQLSTATE 40001, and reason 00C90088, write the first five questions you would ask the on-call developer and the operator.
2. Draft DISPLAY commands for database BILLING and table space INVOICE that show locks and claimers. Explain when each display is useful.
3. Two programs update tables CUSTOMER and PAYMENT. Program A updates CUSTOMER then PAYMENT. Program B updates PAYMENT then CUSTOMER. Draw the deadlock cycle and propose a single shared order.
4. Explain how your retry logic should differ after -911 versus after -913.
5. Build an incident template that captures victim, partner, reason code, resources, and the agreed permanent fix.