Resolving deadlocks on DB2/UDB - Aix

Mr K S

Resolving deadlocks on DB2/UDB - Aix
Hi,

I am trying to trace the occurrence of lots of deadlocks within a newly developed application when used by multiple users. It uses DB2/UDB on Aix.

The application has been developed using a tool called Hibernate, which generates and executes queries dynamically. I have turned on a couple of event monitors on locks and transactions, and also wading thru heaps of o/p from my event monitors and explains, trying to pinpoint the exact cause, but in the meantime I wanted to check with others on the list to see if they can suggest the best way to go about troubleshooting the problem.


TIA.

Regards,
Kals

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark Horrocks

Re: Resolving deadlocks on DB2/UDB - Aix
(in response to Mr K S)
Hi,

If using UDB V8 then there is an deadlock event monitor called
DB2DETAILEDEADLOCK that should be tunrned on automatically. you should then
be able to use db2evmon to dump the trace.

Have you tried db2pd -locks

Also increase your diaglevel to 4 so you can see the deadlocks.

Also look at your instance.nfy log to see the thresholds being hit.

From this you should be able to identify resources and connections that are
causing the deadlocks.

Hope this helps.

Many Thanks,

Mark Horrocks.



>From: teldb2kals <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: [DB2-L] Resolving deadlocks on DB2/UDB - Aix
>Date: Wed, 30 Nov 2005 21:20:51 +1100
>
>Hi,
>
>I am trying to trace the occurrence of lots of deadlocks within a newly
>developed application when used by multiple users. It uses DB2/UDB on Aix.
>
>The application has been developed using a tool called Hibernate, which
>generates and executes queries dynamically. I have turned on a couple of
>event monitors on locks and transactions, and also wading thru heaps of o/p
>from my event monitors and explains, trying to pinpoint the exact cause,
>but in the meantime I wanted to check with others on the list to see if
>they can suggest the best way to go about troubleshooting the problem.
>
>
>TIA.
>
>Regards,
>Kals
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Stritzinger

Re: Resolving deadlocks on DB2/UDB - Aix
(in response to Mark Horrocks)
Kals,

In addition to the suggestions from Mark let me also remind you of
the SNAPSHOT_LOCKWAIT table function. This will only show those applications
that are currently waiting on locks and not all locks that are being held (
This can help to reduce the o/p you are looking at).

If you are on the system you can run the following syntax:

db2 "select * from table (sysproc.snapshot_lockwait ('DBNAME', -1)) as sl".

In the above put your data base name where DBNAME is and -1 will
give you the current partition (If you are non-DPF that is good enough. If
you are DPF (EEE) then -2 gives all the partitions or you can get only the
one partition you are interested in. Search IBM InfoCenter for more detail).

What I did was to create a table with the same definition as the
output from the snapshot and then run a script that does an insert into the
my table with the output from the select so that you can go back later to
see if anything is having a lock wait problem.

Also I a 99% sure that the DB2DETAILEDEADLOCK only will pick up
"true" dead locks (In the old days we called them deadly embraces) and not
lock time outs waiting for a lock.

Dave S.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Mark Horrocks
Sent: Wednesday, November 30, 2005 5:41 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Resolving deadlocks on DB2/UDB - Aix


Hi,

If using UDB V8 then there is an deadlock event monitor called
DB2DETAILEDEADLOCK that should be tunrned on automatically. you should then
be able to use db2evmon to dump the trace.

Have you tried db2pd -locks

Also increase your diaglevel to 4 so you can see the deadlocks.

Also look at your instance.nfy log to see the thresholds being hit.

From this you should be able to identify resources and connections that are
causing the deadlocks.

Hope this helps.

Many Thanks,

Mark Horrocks.



>From: teldb2kals <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: [DB2-L] Resolving deadlocks on DB2/UDB - Aix
>Date: Wed, 30 Nov 2005 21:20:51 +1100
>
>Hi,
>
>I am trying to trace the occurrence of lots of deadlocks within a newly
>developed application when used by multiple users. It uses DB2/UDB on Aix.
>
>The application has been developed using a tool called Hibernate, which
>generates and executes queries dynamically. I have turned on a couple of
>event monitors on locks and transactions, and also wading thru heaps of o/p

>from my event monitors and explains, trying to pinpoint the exact cause,
>but in the meantime I wanted to check with others on the list to see if
>they can suggest the best way to go about troubleshooting the problem.
>
>
>TIA.
>
>Regards,
>Kals
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home

>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select

>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at

>http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm