Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Deadlock in Db2

  1. #1
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197

    Question Unanswered: Deadlock in Db2

    hi,

    I saw a thread already posted on Deadlock. but which din give me any clarity.

    here i have a few question can some help me.


    1.how to identify/detect a deadlock in db2
    2.suppose if the deadlock is not rectified by db2, how can i resolve the deadlock.
    3.In the db2detaildeadlock, there are some log files. how can it be reviewd.
    4.how to get the snapshot of a particular databse to find the deadlock.
    5.what is locktimeout & locklist ? when do we increase the locktimeout and lock list ? what is the use of increasing it.

    thanks !

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    1) DB2 automatically identifies a deadlock. It is built in and always works.
    2) deadlocks are always rectified by DB2. It will rollback one of the applications, and return the "Deadlock" error.
    3) Text editor
    4) The snapshot monitor will not give you much about deadlocks. Use the Event Monitor.
    5) LOCKTIMEOUT is the number of seconds a DB2 agent will wait for a lock to release before it returns an error back to the application. If it is zero, then wait time is not in effect and applications will wait forever. LOCKLIST is the size of the DB2 cache for storing lock information. Increasing LOCKTIMEOUT can cause longer response times for database access. Increasing locklist can prevent lock escalation, but at the expense of taking away RAM from other heaps that might have better use for it.

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Even though both deadlock and locktimeout give a -911 return code, they are different. Deadlock is reason code 2 and locktimeout is reason code 68.

    Basically a deadlock is a lock wait situation that DB2 determines will never be resolved, no matter how long you wait. Also known as a deadly embrace.

    You can turn on locktimeout monitoring to get reports on that (which is different than a deadlock). How you do this depends on the DB2 release you have.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    Thanks a lot Winner & Marcus...

    i got a clear view of it.

    one more question is ,
    if there is a deadlock and Db2 also throwed the error "Deadlock" , In such case how can i find which process is causing this situation ?

    for this case can we event monitor, if so how to use it ?

    Thanks!

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Recent versions of DB2 LUW automatically have a deadlock event monitor created when a database is created. You should be able to find out which event monitors have been created (and which are active) by querying the event monitor tables in the DB2 system catalog views.

    If you have a detailed deadlock event monitor created (recommended), it will show you the SQL statements involved in the deadlock.

    If you don't already have a deadlock event monitor created, then the DB2 documentation provides a lot of information on how to do it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Apr 2010
    Posts
    19

    Deadlock in Db2

    Hi,
    We are getting a wierd deadlock.
    Deadlock is happening between DB2RBIND process and java.

    Following is the mail which we got from db2pe tool.

    IBM (C) DB2 Performance Expert.
    A DB2 deadlock exception occurred at '2010-09-15 02:13:54.03628' on host 'dbpdadc60' on DB2 instance 'wcspi'.

    Deadlock details:

    Database name : WCSP
    Partition number : N/P
    Number of involved applications : 2
    Rolled back application ID : GA141A3D.AAAE.14CD35001000
    Rolled back agent ID : 932
    Rolled back participant : 2

    Application details:

    Participant : 2
    Application ID : GA141A3D.AAAE.14CD35001000
    Agent ID : 932
    Application name : java
    User login ID : wcuser
    Database alias used : WCSP
    Table name :
    Schema name :
    Tablespace name :
    Statement type : Non SQL
    Statement operation : Execute
    Statement text :

    Participant : 1
    Application ID : *LOCAL.wcspi.100915001310
    Agent ID : 33
    Application name : db2rbind
    User login ID : wcspi
    Database alias used : WCSP
    Table name :
    Schema name :
    Tablespace name :
    Statement type : Non SQL
    Statement operation : Rebind
    Statement text :

    *****************************************
    db2rbind process runs daily and we never got the deadlock before.
    Not sure how db2rbind process and java can lead to deadlock.
    Is it that when db2rbind is re-binding the packages , java application is also trying to use the same package.
    What should be done to avoid such deadlocks.

    Thanks
    Ravi

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by infyravi View Post
    Deadlock is happening between DB2RBIND process and java.
    It could be on some catalog table (ie. sysibm.sysplan). You should have a deadlock monitor called "db2detaildeadlock". You can format it using db2evmon. Check this link for some info about this event monitor:
    IBM - Default deadlock event monitor

  8. #8
    Join Date
    Sep 2010
    Posts
    1
    There is a deadlock event monitor called DB2DETAILDEADLOCK. Just format it to see the deadlock events and the SQL statements that are causing the issue. Set the event monitor state to 0 before using db2evmon to get the records. If you don't do this you will not get all the recoprds

  9. #9
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    I have a Table in my db, which will have all the data and people will normally only select the data from this table.

    often if i fire the select query it is just processing and the result is not produced.

    Is this some case of deadlock,

    how to come over this situation, because it happen often and after some time it comes back to normal.

    but i need to fix this issue...

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is the value of the DB configuration parameter LOCKTIMEOUT?

    Andy

  11. #11
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    Locktimout = -1,

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your lock time out value is set to "Turned off". This means that all requests for a lock that have to wait for another lock to be released will wait for as long as the other lock is held or until a deadlock occurs. What you are experiencing is not a deadlock. You are experiencing what is called a lock wait. Your query is waiting for a lock that eventually gets released and then the query proceeds.

    You can fix this one of two ways. You can set LOCKTIMEOUT to a positive value, which will be the maximum number of seconds you want the lock waits to occur. If, for example, you set it to 30, then a statement that ends up waiting for a lock will only wait up to 30 seconds for that lock to release. If the lock is not released, then an error will be returned to the application waiting for the lock that it timed out. Note, this error is the same error that is given when a deadlock occurs. The only difference is the "Reason Code". Deadlock is 2 and timeout is 68.

    The other way to fix this, and still may need to be done, is to determine what is holding the locks for so long and fix them so they do not hold the lock for a long period.


    Andy

  13. #13
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    i have changed the locktimout to 30 as you mentioned. but you

    when im deleting a table which has 12000 rows it is not deleting..it comes out after 30 second saying there is an deadlock / rollback..

    and also select query with conditions not working some times.

    any other suggestion to come over...

    Thanks!

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The error message you are getting is very often misread. You are not getting a deadlock, you had a timeout. Here is the error message:

    Code:
    SQL0911N  The current transaction has been rolled back because of a
          deadlock or timeout. Reason code "<reason-code>".
    
    Explanation:
    
    The current unit of work was involved in an unresolved contention for
    use of an object and had to be rolled back.
    
    The reason codes are as follows:
    
    2
    
             The transaction was rolled back due to a deadlock.
    
    
    68
    
             The transaction was rolled back due to a lock timeout.
    
    
    72
    
             The transaction was rolled back due to a DB2 Data Links Manager
             error during the transaction.
    
    
    73
    
             The transaction was rolled back because a queuing threshold
             such as the CONCURRENTDBCOORDACTIVITIES threshold caused two or
             more activities to reach a deadlock state. For more
             information, see "CONCURRENTDBCOORDACTIVITIES threshold" in the
             DB2 Information Center.
    
    The application was rolled back to the previous COMMIT.
    
    User response:
    
    The changes associated with the unit of work must be entered again.
    
    To help avoid deadlock or lock timeout, issue frequent COMMIT
    operations, if possible, for a long-running application, or for an
    application likely to encounter a deadlock.
    
    Federated system users: the deadlock can occur at the federated server
    or at the data source. There is no mechanism to detect deadlocks that
    span data sources and potentially the federated system. It is possible
    to identify the data source failing the request (refer to the problem
    determination guide to determine which data source is failing to process
    the SQL statement).
    
    Deadlocks are often normal or expected while processing certain
    combinations of SQL statements. It is recommended that you design
    applications to avoid deadlocks to the extent possible.
    
    For more detailed information about preventing deadlocks or lock
    timeouts search the DB2 Information Center
    (http://publib.boulder.ibm.com/infocenter/db2luw/v9) using phrases such
    as "deadlock prevention", and terms such as "deadlocks" and "lock
    timeouts".
    
    If a deadlock state was reached because of a queuing threshold such as
    the CONCURRENTDBCOORDACTIVITIES threshod, increase the value of the
    queuing threshold.
    
    sqlcode: -911
    
     sqlstate: 40001
    Your problem is that you have locks that are being held longer than 30 seconds. This usually indicates poor transaction design. You need to determine who is holding the locks for more than 30 seconds and fix the transaction to eliminate the situation.

    Andy

  15. #15
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    Andy, thanks for the info,

    i dont know how to determine which is acquring the lock for than 30seconds,

    what is the way to fix this ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •