Scenario: Two-way deadlock with two resources
When two agents contend for resources, the result is a deadlock in which one of the agents is rolled back.
Two transactions and two resources
are involved. First, transaction LOC2A acquires a lock on one resource
while transaction LOC2B acquires a lock on another. Next, the two
transactions each request locks on the resource held by the other.
The transactions execute in the following order:
- LOC2A
-
- 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.
- LOC2B
-
- Declare and open a cursor for update on PROJ and fetch from page 8.
- Declare and open a cursor for update on DEPT and fetch from page 2.
- Update page 8.
- Update page 2.
- Close both cursors and commit.
Events take place in the following sequence:
- LOC2A obtains a U lock on page 2 in table DEPT, to open its cursor for update.
- LOC2B obtains a U lock on a page 8 in table PROJ, to open its cursor for update.
- LOC2A attempts to access page 8, to open its cursor but cannot proceed because of the lock held by LOC2B.
- LOC2B attempts to access page 2, to open its cursor but cannot proceed because of the lock held by LOC2A.
Db2 selects one of the transactions and rolls it back, releasing its locks. That allows the other transaction to proceed to completion and release its locks also.
The following figure shows the IBM® OMEGAMON® for Db2 Performance Expert on z/OS® Locking Trace - Deadlock report that is produced for this situation.
The report shows that the only transactions involved came from plans LOC2A and LOC2B. Both transactions came in from BATCH.
⋮
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 RUNLOC2A TSO 20:32:30.68850025 DEADLOCK COUNTER = 2 WAITERS = 2
SYSADM 'BLANK' AADD32FD8A8C N/P TSTAMP =04/02/95 20:32:30.68
LOC2A BATCH DATAPAGE DB =DSN8D42A HASH =X'01060304'
A OB =DEPT ---------------- BLOCKER IS HOLDER -----
PAGE=X'000002' LUW='BLANK'.EGTVLU2.AADD32FD8A8C
MEMBER =DB1A CONNECT =BATCH
PLANNAME=LOC2A CORRID=RUNLOC2A
DURATION=MANUAL PRIMAUTH=SYSADM
STATE =U
---------------- WAITER ----------------
LUW='BLANK'.EGTVLU2.AA65FEDC1022
MEMBER =DB1A CONNECT =BATCH
PLANNAME=LOC2B CORRID=RUNLOC2B
DURATION=MANUAL PRIMAUTH=KATHY
REQUEST =LOCK WORTH = 18
STATE =U
DATAPAGE DB =DSN8D42A HASH =X'01060312'
OB =PROJ ---------------- BLOCKER IS HOLDER -----
PAGE=X'000008' LUW='BLANK'.EGTVLU2.AA65FEDC1022
MEMBER =DB1A CONNECT =BATCH
PLANNAME=LOC2B CORRID=RUNLOC2B
DURATION=MANUAL PRIMAUTH=KATHY
STATE =U
---------------- WAITER -------*VICTIM*-
LUW='BLANK'.EGTVLU2.AADD32FD8A8C
MEMBER =DB1A CONNECT =BATCH
PLANNAME=LOC2A CORRID=RUNLOC2A
DURATION=MANUAL PRIMAUTH=SYSADM
REQUEST =LOCK WORTH = 17
STATE =UThe lock held by transaction 1 (LOC2A) is a data page lock on the DEPT table and is held in U state. (The value of MANUAL for duration means that, if the plan was bound with isolation level CS and the page was not updated, then Db2 is free to release the lock before the next commit point.)
Transaction 2 (LOC2B) was requesting a lock on the same resource, also of mode U and hence incompatible.
The specifications of the lock held by transaction 2 (LOC2B) are the same. Transaction 1 was requesting an incompatible lock on the same resource. Hence, the deadlock.
Finally, note that the entry in the trace, identified at A ,
is LOC2A. That is the selected thread (the victim
) whose work
is rolled back to let the other proceed. 