DB2 Locks and Deadlocks

Useful stuff:

1. Lock Avoidance
2. Analyzing lockwait situations
3. New options for analyzing lock timeouts in DB2 9.5

DB2 should have a monitor looking for deadlocks active by default. To see what happened, use:

MYDB=MYDBNAME
db2 connect to $MYDB
db2 "FLUSH EVENT MONITOR db2detaildeadlock"
db2 connect reset
cd `db2 get db cfg for $MYDB |\
  awk '/Path to log files/{print $6 "../db2event"}'`
db2evmon -db $MYDB -evm db2detaildeadlock

If for some reason there isn't such a monitor, you can create your own (called dlmon in the following example) with:

-- Create event monitor (cfr Event types)
CONNECT TO MYDB
CREATE EVENT MONITOR dlmon FOR DEADLOCKS WITH DETAILS HISTORY VALUES WRITE TO FILE '/tmp/dlevents' BUFFERSIZE 8 NONBLOCKED MAXFILES NONE
 
-- Activate the newly created event monitor to enable it to collect information.
SET EVENT MONITOR dlmon STATE 1
 
-- To see if an event monitor is active or inactive:
SELECT EVMONNAME, EVENT_MON_STATE(EVMONNAME) FROM syscat.eventmonitors
 
-- To deactivate and delete  an event monitor:
SET EVENT MONITOR dlmon STATE 0
DROP EVENT MONITOR dlmon

Then, before dropping the monitor, issue:

$ db2evmon -db MYDB -evm dlmon

To test for deadlock detection, you can artificially create one this way:

-- Create tables
CREATE TABLE foo (id INT)
CREATE TABLE bar (id INT)
 
-- Populate them
INSERT INTO foo VALUES (1)
INSERT INTO bar VALUES (1)
COMMIT
 
-- In the FIRST session:
UPDATE COMMAND OPTIONS USING C OFF
UPDATE foo SET id=2
 
-- In the SECOND session:
UPDATE COMMAND OPTIONS USING C OFF
UPDATE bar SET id=2
UPDATE foo SET id=2
 
-- In the FIRST session:
UPDATE bar SET id=2
 
-- The second session will now DIE

db2pd -db MYDB -locks showlocks
db2pd -db MYDB -locks wait
db2pd -db MYDB -locks wait showlocks -transactions -agents -applications -dynamic \
  -file db2pd.out -repeat 15 40

For DB2 v9.5 or 9.1fp>4 you can enable DB2_CAPTURE_LOCKTIMEOUT

db2set DB2_CAPTURE_LOCKTIMEOUT=ON
db2stop
db2start

When DB2_CAPTURE_LOCKTIMEOUT is set to ON, DB2 automatically creates a report file for each lock timeout occurrence. The report file is written to the directory where the DIAGPATH database manager configuration (DBM CFG) parameter points, to and contains information about, the date and time of the lock timeout, the problematic lock, the lock requester, and the lock owner.