What it means
Db2 uses locks to stop two users from changing the same data at the same time. If a program needs a lock someone else holds, it waits. A wait can end badly in two ways.
- A timeout, reason 00C9008E. The program waited longer than the subsystem allows, which is set by the IRLMRWT parameter.
- A deadlock, reason 00C90088. Two programs each hold a lock the other needs. IRLM finds the cycle at its next deadlock check and picks one of them as the victim.
With -911, Db2 has already rolled back every change since the last COMMIT and closed every cursor, WITH HOLD cursors included. The program must not carry on as if one statement had failed. The resource type and name in the message, such as 00000302 and ORDDB.ORDTS01.X'000412', show what was locked. SQLERRD(3) in the SQLCA also holds the reason code, as 13172872 for a deadlock or 13172878 for a timeout.
Common causes
- A long-running batch job updates many rows without committing, and online transactions wait on its locks until they time out.
- Two programs update the same tables in a different order, so each ends up waiting for the other.
- A program reads with repeatable read (RR) or read stability (RS) isolation and holds many share locks for a long time.
- A hot spot, such as a control table with a single next-number row, that many users update at once.
- Page-level locking on a busy table, so users who change different rows on the same page still block each other.
How to find the problem
- Check the reason code first. 00C90088 means deadlock and 00C9008E means timeout.
- Find the matching message in the Db2 master address space log (xxxxMSTR) at the same time. DSNT375I reports a deadlock and DSNT376I a timeout. Both name the plans and correlation IDs, often the job names, of the programs involved, including the one holding the lock.
- Use the resource name to find the object. Look up the database and table space in SYSIBM.SYSTABLES (DBNAME and TSNAME) to see which tables live there. The hex number at the end is the page.
- Check whether the lock holder is a batch job that runs a long time between commits, and whether the timeouts line up with its schedule.
- For problems that keep coming back, a performance monitor or the IFCID 172 (deadlock) and IFCID 196 (timeout) trace records give full detail.
How to fix it
- Retry the unit of work from the start. Db2 has rolled everything back, so reopen cursors and repeat every change since the last commit. Repeating only the failing statement isn't enough. Put a limit on the retries.
- Commit more often in batch, for example every few hundred or few thousand updates, with checkpoint and restart logic.
- Have every program that updates a set of tables access them, and their rows, in the same order.
- Bind with ISOLATION(CS) and CURRENTDATA(NO) unless the program really needs RR or RS.
- For a hot table, the DBA can consider LOCKSIZE ROW or a lower MAXROWS. Both mean more locks, so test the change first.
- Move heavy batch updates out of the online day where you can.
What it looks like
ORDUPD3 - UPDATE ORDER_HDR FAILED, ORDER 00915532
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 ORDDB.ORDTS01.X'000412'
DSNT418I SQLSTATE = 40001 SQLSTATE RETURN CODE