Detecting locks & deadlocks in DB2

Posted on 2007-07-21
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
    LVL 13

    Accepted Solution

    LVL 17

    Author Comment

    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

    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,...).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    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…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now