Results 1 to 11 of 11

Thread: DB2 Deadlocks

  1. #1
    Join Date
    Feb 2004
    Posts
    18

    Exclamation Unanswered: DB2 Deadlocks

    Hello List,

    I'm encountering Deadlock problems on my DB2 server 8.1 running on AIX. I'm trying to do a virtual test with 150 users running from Win-runner. After some time, It says the transaction failed due to Deadloacl. Sqlstate=40001. native error = - 911. The current transaction has been rolled back because of deadlock or timeout.

    I've reset modified the DB2 configuration by LOCKTIME_OUT set to 0. But this doesnt seem to work.

    Can somebody please advice, how can we get rid of this deadlock error?

    Thanks,

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    See if you can find the reason code to determine if it is a deadlock or timeout. These are two different conditions. Deadlocks are not helped by changing locktimeout.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by new_user1
    Hello List,

    I'm encountering Deadlock problems on my DB2 server 8.1 running on AIX. I'm trying to do a virtual test with 150 users running from Win-runner. After some time, It says the transaction failed due to Deadloacl. Sqlstate=40001. native error = - 911. The current transaction has been rolled back because of deadlock or timeout.

    I've reset modified the DB2 configuration by LOCKTIME_OUT set to 0. But this doesnt seem to work.

    Can somebody please advice, how can we get rid of this deadlock error?

    Thanks,
    Are you seeing any lock escalations in the db2diag.log?

    You can setup an event monitor to get some details on why the deadlock is occuring, ie. the statements that are being issued.

    create event monitor mydeadlockmon
    for DEADLOCKS WITH DETAILS
    WRITE TO FILE '/valid/directory'
    autostart global maxfiles 20 maxfilesize 512 buffersize 17 blocked append
    ;
    set event monitor mydeadlockmonstate 1;

    You can also try a different isolation level.
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Feb 2004
    Posts
    18

    DB2 Deadlock

    Hello,

    My application is placing a lock on one of the table which is heavily used. So I believe, Its trying to do insert/delete and update simantenously. thats why I assume, its happening.

    any suggestions how to avoid this?

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What do you mean "placing a lock on the table."?

    Is an SQL lock table statement issued? Is the lock level escalating to table level?

    If you are getting lock escalation, increase locklist (the number of pages used to store locks) and increase maxlocks (the percent of the locklist that any one application can use before escalation occurs). I would dramatically increase the locklist (and increase the dbheap at the same time). I would increase maxlocks to 60 if escalation is problem.
    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
    Feb 2004
    Posts
    18

    DB2 Deadlock

    Hello Fedman,

    I'm not that much familar with DB2. The error what i'm getting is Deadlock error. The other parameters what you have listed are i guess not related to my case. But again, If Deadlock can be avoided by switching to the parameters what you have suggested. then i'll be happy to go with it.

    Thanks for your help.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A deadlock is different from a timeout. The error message says deadlock OR timeout, which means you need to investigate further to see which one it is.

    However, a deadlock or a timeout can be caused by lock escalation (or other reasons), in which case the suggestions I gave may help.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Feb 2004
    Posts
    18

    DB2 Deadlock

    Hello Feldman,

    Thanks for your email.

    My error is pure "Deadlock". I did modified some of the parameters as you suggested. But as i said. I'm new to DB2 and not that much familiar with the configuration pages. I was wondering, what is the exact configuration name for locklist? Is this under database configuration or the instance configuration?

    Also the heapsize. I did see a heap size at the database configuration level and one at instance level? Dont know which one to modify??? Also whats the different between instance level heap and database level? any other

    Any other suggestion what you advice on this deadlock?

    Thanks for all your help.

  9. #9
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by new_user1
    Hello Feldman,

    Thanks for your email.

    My error is pure "Deadlock". I did modified some of the parameters as you suggested. But as i said. I'm new to DB2 and not that much familiar with the configuration pages. I was wondering, what is the exact configuration name for locklist? Is this under database configuration or the instance configuration?

    Also the heapsize. I did see a heap size at the database configuration level and one at instance level? Dont know which one to modify??? Also whats the different between instance level heap and database level? any other

    Any other suggestion what you advice on this deadlock?

    Thanks for all your help.
    Have you created an event monitor as I suggested? This will tell you what statements are involved... that will be very helpful I suspect!
    --
    Jonathan Petruk
    DB2 Database Consultant

  10. #10
    Join Date
    Feb 2004
    Posts
    18

    DB2 Deadlock

    I did not created the event monitor. I'll create it now and will see, whats going on.

    Will keep posted.

    Thanks,

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    locklist, maxlocks, and dbheap are in the database configuration (one for each database). See the "Administration Guide: Performance" on how to update these. Or you can use the Control Center GUI to change these.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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