Db2 On Premise and Cloud

Expand all | Collapse all

Db2 Lock Resolution

  • 1.  Db2 Lock Resolution

    Posted Tue November 20, 2018 02:07 PM

    I am reposting a question from our old dW forum here. Rizwan has the question below.

    Hi Gurus,

    My Environment:

    AIX 6.1, DB2 10.5.0.8

     

    I can see locking in my db2 database of around 50k to 60k in peak hours. i have been searching around, found DB2PD, DB2TOP etc but not able to fetch the statements holding the locks.

    I am looking for something like Oracle ADDM or AWR report to identify the Root Cause and something like Oracle v$locks Dynamic view to fetch the currently locking statements/ids to resolve the deadlocks.

     

    Please help me in this.

     

    Best Regards,

    Rizwan Ali



    ------------------------------
    Lynn Chou, posting on behalf of Rizwan Ali
    ------------------------------


  • 2.  RE: Db2 Lock Resolution

    Posted Wed November 21, 2018 12:52 AM
    There are a number of ways to get information about lock holders and locks available in Db2.

    There are the lower level table functions:
    • MON_GET_LOCKS
    • MON_GET_APPL_LOCKWAIT
    • MON_FORMAT_LOCK_NAME


    And there are the higher level MON_LOCKWAITS administrative views and LOCKING event monitor. You may get what you want from the first one....

    Just be aware that things can get more complicated if you have multiple SQL statements in a transaction and the transaction is using a higher isolation level or is issuing writes to the database or DDL, then the transaction can still hold locks even after the initiating statement is no longer executing. 

    E.g. If I issue an INSERT  followed by a SELECT in the same transaction, then the transaction would still have the X locks from the INSERT even though it is currently executing a SELECT...

    Also, it is not clear what you mean by "locking in my db2 database of around 50k to 60k in peak hours", you may expect to see lots of locks if you are doing lots of things. Locks themselves are not an issue, lock waits and lock escalations are.  You can see if they are occurring by looking at the DEADLOCKS, LOCK_ESCALS, and LOCK_TIMEOUT metrics in MON_GET_DATABASE() or any of the other levels of MON_GET_* functions.


    If you have deadlocks occurring, then the best tool to use is the locking event monitor which will get you all the parties involved in the deadlock.

    You could also consider using the Data Server Manager tool that ships with Db2 to help you capture and look at the locking information.

    Hope this helps,



    ------------------------------
    PAUL BIRD
    ------------------------------



  • 3.  RE: Db2 Lock Resolution

    Posted Thu November 22, 2018 03:19 AM
    Thank you Lynn for redirecting my question here.

    Hi Paul,

    Glad you took time to help me here. I am working on this as you suggested with MON_GET* functions. but what i am looking here is out of 50k 60k deadlocks how come i can identify the deadlocks app handle and force them to resolve the issue at the spot(quick resolution), when the temenos application hangs up and we have to restart our WAS Jvms one by one to resolve the issue, that is absolutely pathetic for us.

    i am looking for some dynamic veiw may be which show the culprit statement, which i can force to resolve the deadlock than and there.
    with the use of: DB2 "FORCE APPLICATION <id>"


    Thanks,
    Rizwan Ali

    ------------------------------
    Rizwan Joo
    ------------------------------



  • 4.  RE: Db2 Lock Resolution

    Posted Fri November 23, 2018 10:43 AM
    Hi,

    If you want to see the current statements that are in lockwait, then the MON_LOCKWAITS view or theMONREPORT.LOCKWAIT stored procedure (https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0056374.html) will show you all the information you need.

    If you look for the oldest lock wait time value, you should see the bottom of the pile and whatever it is waiting on is probably the other guilty party in the deadlock.  To find it quickly, you may want to write a recursive query or view. I took a quick look for existing examples and came across this old blog entry (http://db2performance.blogspot.com/2012/06/encounter-with-chain-of-lockwait.html) which may or may not be of use to you ( I did not have time to try it out myself).

    That said, trying to resolve deadlock issues in real-time is a tough proposition and I would ask if you are taking advantage of the other mechanisms discussed in the documentation (e.g. Resolving deadlock problems https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.trb.doc/doc/t0055074.html).

    I am specifically thinking of using the LOCKTIMEOUT parameter to force issues before the deadlock detector finds them.  I am thinking of this part of the above reference:

    A lock timeout is not much better than a deadlock, because both cause a transaction to be rolled back, but if you must minimize the number of deadlocks, you can do it by ensuring that a lock timeout will usually occur before a potential related deadlock can be detected. To do this, set the value of the locktimeout database configuration parameter (units of seconds) to be much lower than the value of the dlchktime database configuration parameter (units of milliseconds). Otherwise, if locktimeout is longer than the dlchktime interval, the deadlock detector could wake up just after the deadlock situation began, and detect the deadlock before the lock timeout occurs.

    I ask because the default for the LOCKTIMEOUT is -1 which means lock timeouts are not active unless you explicitly turn them on. The default value of the deadlock detection, DLCHKTIME, is also 10 minutes so if you want Db2 to help automatically resolve deadlocks before you get a serious backlog, you can set this value to a lower number.

    Hope this helps!

    ------------------------------
    PAUL BIRD
    ------------------------------



  • 5.  RE: Db2 Lock Resolution

    Posted Fri December 07, 2018 01:56 AM
    Hey,

    Thank you for the in-depth information about locking issues. That's really helped me a lot. i have found db2top most useful here as it is giving me run time locking status, lock waits and other information. This blog also helped me to understand it better:
    db2top Locks Screen - The K Guy

    Please let me know about db2top accuracy on locking and other views.

    Thanks and Regards,
    Rizwan A Joo

    ------------------------------
    Rizwan Joo
    ------------------------------