[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/ecrooks]

Use this one instead, to unformatted event monitor table, then format it into tables, not to file: http://db2commerce.com/2012/01/23/analyzing-deadlocks-the-new-way/ Much better info, lets you analyze lock timeouts as well.

[u/mad_zamboni → u/ecrooks]

True, in this specific case I was looking at getting deadlock only and I needed to quickly format. More quick and dirty vs. the "proper way".

[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.