Scenario: Three-way deadlock with three resources
When three agents contend for resources, the result is a deadlock in which one of the agents is rolled back. Three transactions and three resources are involved.
First, the three transactions each acquire a lock on a different resource. LOC3A then requests a lock on the resource held by LOC3B, LOC3B requests a lock on the resource held by LOC3C, and LOC3C requests a lock on the resource held by LOC3A.
The transactions execute as follows:
- LOC3A
-
- Declare and open a cursor for update on DEPT and fetch from page 2.
- Declare and open a cursor for update on PROJ and fetch from page 8.
- Update page 2.
- Update page 8.
- Close both cursors and commit.
- LOC3B
-
- Declare and open a cursor for update on PROJ and fetch from page 8.
- Declare and open a cursor for update on ACT and fetch from page 6.
- Update page 6.
- Update page 8.
- Close both cursors and commit.
- LOC3C
-
- Declare and open a cursor for update on ACT and fetch from page 6.
- Declare and open a cursor for update on DEPT and fetch from page 2.
- Update page 6.
- Update page 2.
- Close both cursors and commit.
Events take place in the following sequence:
- LOC3A obtains a U lock on page 2 in DEPT, to open its cursor for update.
- LOC3B obtains a U lock on page 8 in PROJ, to open its cursor for update.
- LOC3C obtains a U lock on page 6 in ACT, to open its cursor for update.
- LOC3A attempts to access page 8 in PROJ but cannot proceed because of the lock held by LOC3B.
- LOC3B attempts to access page 6 in ACT cannot proceed because of the lock held by LOC3C.
- LOC3C attempts to access page 2 in DEPT but cannot proceed, because of the lock held by LOC3A.
Db2 rolls back LOC3C and releases its locks. That allows LOC3B to complete and release the lock on PROJ so that LOC3A can complete. LOC3C can then try again.
The following figure shows the IBM® OMEGAMON® for Db2 Performance Expert on z/OS® Locking Trace - Deadlock report produced for this situation.
⋮
PRIMAUTH CORRNAME CONNTYPE
ORIGAUTH CORRNMBR INSTANCE EVENT TIMESTAMP --- L O C K R E S O U R C E ---
PLANNAME CONNECT RELATED TIMESTAMP EVENT TYPE NAME EVENT SPECIFIC DATA
------------------------------ ----------------- -------- --------- ----------------------- ----------------------------------------
SYSADM RUNLOC3C TSO 15:10:39.33061694 DEADLOCK COUNTER = 3 WAITERS = 3
SYSADM 'BLANK' AADE2CF16F34 N/P TSTAMP =04/03/95 15:10:39.31
LOC3C BATCH DATAPAGE DB =DSN8D42A HASH =X'01060312'
OB =PROJ ---------------- BLOCKER IS HOLDER------
PAGE=X'000008' LUW='BLANK'.EGTVLU2.AAD15D373533
MEMBER =DB1A CONNECT =BATCH
PLANNAME=LOC3B CORRID=RUNLOC3B
DURATION=MANUAL PRIMAUTH=JULIE
STATE =U
---------------- WAITER ----------------
LUW='BLANK'.EGTVLU2.AB33745CE357
MEMBER =DB1A CONNECT =BATCH
PLANNAME=LOC3A CORRID=RUNLOC3A
DURATION=MANUAL PRIMAUTH=BOB
REQUEST =LOCK WORTH = 18
STATE =U
---------- BLOCKER IS HOLDER --*VICTIM*-
LUW='BLANK'.EGTVLU2.AAD15D373533
MEMBER =DB1A CONNECT =BATCH
PLANNAME=LOC3C CORRID =RUNLOC3C
DURATION=MANUAL PRIMAUTH=SYSADM
STATE =U
---------------- WAITER ----------------
LUW='BLANK'.EGTVLU2.AB33745CE357
MEMBER =DB1A CONNECT =BATCH
PLANNAME=LOC3B CORRID =RUNLOC3B
DURATION=MANUAL PRIMAUTH=JULIE
REQUEST =LOCK WORTH = 18
STATE =U
---------- BLOCKER IS HOLDER -----------
LUW='BLANK'.EGTVLU2.AAD15D373533
MEMBER =DB1A CONNECT =BATCH
PLANNAME=LOC3A CORRID =RUNLOC3A
DURATION=MANUAL PRIMAUTH=BOB
STATE =U
---------------- WAITER -------*VICTIM*-
LUW='BLANK'.EGTVLU2.AB33745CE357
MEMBER =DB1A CONNECT =BATCH
PLANNAME=LOC3C CORRID =RUNLOC3C
DURATION=MANUAL PRIMAUTH=SYSADM
REQUEST =LOCK WORTH = 18
STATE =U