Finding the needle in the Deadlock haystack

I'm reposting this from an excellent blog post here: -

Finding the needle in the Deadlock haystack

<snip>

How many people out there have enjoyed the hunt for the deadlock culprits? If you are very analytical and enjoy a good murder mystery then perhaps you enjoy tracking down the two lines of application code that caused a deadlock that you wanted to resolve. For those that have not had the pleasure, let me start from the beginning…what is a deadlock?

In any database system a deadlock occurs when two applications are both waiting on a resource (row lock usually) that the other holds. Neither can proceed because they are waiting on each other and neither will voluntarily release their locks so you are in a deadlock. All database systems have a deadlock detector that wakes up on occasion, looks for deadlocks and if it finds one, it kills one of the transactions (rolls back the current unit of work) which results in that application releasing its lock so the other application can proceed. In the case of DB2 the application that is the victim gets a SQL0911 telling the app that the transaction was rolled back due to a deadlock or lock timeout.

</snip>

<snip>

With DB2 there is a deadlock event monitor that will dump out information when a deadlock occurs. In fact with DB2 v8.2, a DEADLOCKS WITH DETAILS event monitor is created automatically when the database is created and is started every time the database is started. This detailed deadlock monitor will dump information about the applications involved in any deadlock, including userids, hostnames of client machines, the statement text of the currently executing statement, and a list of all locks held by the application. The information is stored in the default database path (which you can find from your dbm configuration) in a directory called db2event/db2detaildeadlock.

</snip>

This from my own DB2 LUW environment: -

$ db2level

DB21085I  Instance "db2inst1" uses "64" bits and DB2 code release "SQL09075" 
with level identifier "08060107".
Informational tokens are "DB2 v9.7.0.5", "s111017", "IP23292", and Fix Pack 
"5".
Product is installed at "/opt/ibm/db2/V9.7".

$ db2evmon -db bpmdb  -evm db2detaildeadlock

Reading /home/db2inst1/db2inst1/NODE0000/SQL00003/db2event/db2detaildeadlock/00000000.evt ...
--------------------------------------------------------------------------
                            EVENT LOG HEADER
  Event Monitor name: DB2DETAILDEADLOCK
  Server Product ID: SQL09075
  Version of event monitor data: 11
  Byte order: LITTLE ENDIAN
  Number of nodes in db2 instance: 1
  Codepage of database: 1208
  Territory code of database: 1
  Server instance name: db2inst1
--------------------------------------------------------------------------

--------------------------------------------------------------------------
  Database Name: BPMDB   
  Database Path: /home/db2inst1/db2inst1/NODE0000/SQL00003/
  First connection timestamp: 29/08/2013 13:02:20.966657
  Event Monitor Start time:   29/08/2013 13:02:21.275886
--------------------------------------------------------------------------

--------------------------------------------------------------------------
  Database Name: BPMDB   
  Database Path: /home/db2inst1/db2inst1/NODE0000/SQL00003/
  First connection timestamp: 29/08/2013 13:07:52.540448
  Event Monitor Start time:   29/08/2013 13:08:10.467783
--------------------------------------------------------------------------

--------------------------------------------------------------------------
  Database Name: BPMDB   
  Database Path: /home/db2inst1/db2inst1/NODE0000/SQL00003/
  First connection timestamp: 29/08/2013 13:26:22.695516
  Event Monitor Start time:   29/08/2013 13:26:28.356203
--------------------------------------------------------------------------

--------------------------------------------------------------------------
  Database Name: BPMDB   
  Database Path: /home/db2inst1/db2inst1/NODE0000/SQL00003/
  First connection timestamp: 29/08/2013 13:31:07.766264
  Event Monitor Start time:   29/08/2013 13:31:08.846314
--------------------------------------------------------------------------

--------------------------------------------------------------------------
  Database Name: BPMDB   
  Database Path: /home/db2inst1/db2inst1/NODE0000/SQL00003/
  First connection timestamp: 29/08/2013 13:35:22.084183
  Event Monitor Start time:   29/08/2013 13:35:22.574086
--------------------------------------------------------------------------

--------------------------------------------------------------------------
  Database Name: BPMDB   
  Database Path: /home/db2inst1/db2inst1/NODE0000/SQL00003/
  First connection timestamp: 29/08/2013 13:35:22.756913
  Event Monitor Start time:   29/08/2013 13:35:23.409371
--------------------------------------------------------------------------

--------------------------------------------------------------------------
  Database Name: BPMDB   
  Database Path: /home/db2inst1/db2inst1/NODE0000/SQL00003/
  First connection timestamp: 29/08/2013 13:37:11.460381
  Event Monitor Start time:   29/08/2013 13:37:22.980311
--------------------------------------------------------------------------