[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Detecting locks & deadlocks in DB2

Posted on 2007-07-21
Medium Priority
Last Modified: 2009-08-26
1) We have DB2 UDB 9.1 on linux.
2) One way to detect locks is monitoring. Will this detect deadlocks or we need to go for a snapshot. Actually inbuit deadlock monitor/detector in db2 detects deadlocks and rollsback the less important transaction. How do we detect & handle deadlocks.
3) There is a way to see applications & on NET I found queries on SYSIBMADM.LOCKWAITS & SYSIBMADM.SNAPAPPL_INFO & these are not recognized. Will this work only in DB2 on host systems?
4) How to query for locks and deadlocks in DB2 UDB?
5) Please suggest some good tools to do monitoring of various switches.
Question by:k_murli_krishna
  • 2
LVL 13

Accepted Solution

ghp7000 earned 500 total points
ID: 19540850
LVL 17

Author Comment

ID: 19586056
Thanks. It is good query & info that you have provided. Please answer to points 3), 4) & 5).
My main requirement is that I need to identify locks for a particular table, what kind they are i.e. read, write etc. & what operation caused them to occur or be held i.e. select/insert/update/delete and which application ID from list applications it belongs to.
I am able to identify more or less different types of applications and force them. Similarly I want to kill the locks after spotting them.
I thinking deadlocks occur when 2 write perations on same row(s) occur exactly at same time which is rare. Also when 2 or more SELECT FOR UPDATE are fired & thirdly when 2 applications do criss cross updates on 2 rows. Is there any other ways for deadlock to arise.
Also what I know is we cannot do anything for deadlocks & I have seen recently deadlock monitor rolling back the less important or later fired actions. Am I correct? Please advise.
LVL 17

Author Comment

ID: 19649054
I found lot of locks related information using link that you provided.
force applications all OR
force applications (id1,id2,...)
This will kill all the locks of the application(s) forced. One application may acquire many locks. I want to kill row/block/table level lock after having identified it. Is there any force locks (id1,id2,...).

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Loops Section Overview
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month20 days, 5 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question