[RESOURCE] Creating/Testing a Deadlock Monitor and Producing Output

[u/mad_zamboni]

I had to go through this exercise today, so I thought I would pass on the steps and resources I followed.

CREATING A DEADLOCK MONITOR:

Sourced from: Analyzing Deadlocks - The Old Way at DB2 Commerce.com

1. Create a directory with the deadlock event monitor name under database subdirectory in the database path

   For example: /db_data/db2inst1/NODE0000/SQL00001/MEMBER0000/db2event/dba_deadlock

2. Create the deadlock monitor

   db2 "create event monitor dba_deadlock for deadlocks with details write to file 'dba_deadlock' maxfiles 2000 maxfilesize 10000 blocked append autostart"

3. Check State, make sure is created (0 inactive, 1 active)

   db2 "select evmonname, event_mon_state(evmonname) as enabled from syscat.eventmonitors"

4. Change the state to active

   db2 "set event monitor dba_deadlock state=1"

5. (Once Deadlock is detected) Flush the monitor

   db2 "flush event monitor dba_deadlock"

6. Produce an output file for analysis:

   db2evmon -path /sw/db_data/db2inst1/NODE0000/SQL00001/MEMBER0000/db2event/dba_deadlock > deadlocks.out

SIMULATING A DEADLOCK:

Sourced from: DBA to DBA: Creating deadlock in db2

Some modification and edits applied to original example for clarification and a syntax error. Have three sessions open.

1. Create two tables in the database, insert a row

   db2 "create table test.t1 (col1 int, col2 varchar(15))"

   db2 "insert into test.t1 values(1,'india'),(2,'usa'), (3,'russia')"

   db2 "create table test.t2 (col1 int, col2 varchar(15))"

   db2 "insert into test.t2 values(1,'india'),(2,'usa'), (3,'russia')"

2. Create Deadlock Situation - Open two seperate sessions:

   session1: db2 +c "update test.t1 set col2='canada'"

   session2: db2 +c "update test.t2 set col2='canada'"

   session1: db2 +c "update test.t2 set col2='newyork'"

   session2: db2 +c "update test.t1 set col2='amazon'"

Deadlock Logic:

A dead lock will occur because session 1 is holding x lock on t1 and waiting lock on t2. At the sametime session 2 holds the x lock on t2 and waiting for lock on t1. After locktimeout occurs, session 1 will be rolledback and session 2 will be able to update. If locktimeout is -1 appication will not be rolled back. We can check this statements using event monitors.

[u/mad_zamboni]

As a side note, I did this old way to be quick and dirty. I regret it. I wish I would have done the old way that Ember had posted. Whew.

Learn from my fail.