Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: resolving deadlock issues with stored procedures

    We are faced with several stored procedures that kick off nitely batch processing loads and they seem to be deadlocking on a few key tables in our production DB2 UDB database environment. How can I find a long term solution to our deadlocks to prevent our nitely batch jobs from failing? Often times these stored procedures must access the same tables and run inserts, updates and appends to several key tables during batch nitely processing. When these fail it brings production to a halt. Thanks again to the DB2 UDB gurus

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: resolving deadlock issues with stored procedures

    Hi,

    you can either

    a) redesign those stored procs and make them commit faster. It is good practice to make commit counts/levels configurable. Also, you may want to have your batches retry on error. While this option is perfectly sound, it can be laborious especially if it was not being considered up front during batch programming.

    or

    b) foresake parallelism and have them batches run on their own. This is sometimes possible if you tune your system, like e.g. do away with batches/procs you don't need any more, throw away old data, clean your db and statistics using reorg and runstats, monitor performance bottlenecks and take appropriate action.

    From the administrative point of view you can experiment with lock list sizes ( db2 get db cfg for ... | grep -i lock will get you started ), and LOCK TABLE IN EXCLUSIVE MODE may actually help sql dml performance in some situations - in others it will hurt. This option can help to live along with the problems at the cost of considerable effort.

    Johann

    Originally posted by mixxalot
    We are faced with several stored procedures that kick off nitely batch processing loads and they seem to be deadlocking on a few key tables in our production DB2 UDB database environment. How can I find a long term solution to our deadlocks to prevent our nitely batch jobs from failing? Often times these stored procedures must access the same tables and run inserts, updates and appends to several key tables during batch nitely processing. When these fail it brings production to a halt. Thanks again to the DB2 UDB gurus

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    First, it is important to find out if you are experiencing a true deadlock problem, or just a timeout due to lock contention.

    One way to prevent true deadlocks (as opposed to just minimizing lock contention) is to make sure that all programs updating multiple tables in a single unit of work, access the tables in the same sequence.

    For example, one of the traditional ways that deadlocking occurs is if program 1 and 2 both update tables A and B.

    Program 1 selects table A first for update, and then tries to select table B for update, then both updates are committed together.

    Program 2 selects table B first for update, and then tries to select table A for update, then both updates are committed together.

    The problem occurs when the first locks on both programs are obtained, but the second locks can never be obtained while the programs wait on each other to release the first locks.

    But if both tables acquired the update locks in the same order, deadlocks can be prevented, although there may still be some significant wait time for lock contention (but at least not deadlock).

    As mentioned in other threads, dynamic lock escalation to table level may also a problem for concurrency. If DB2 does not have enough storage defined in the LOCKLIST parameter (database performance configuration), then escalation to table level locking may be occurring, which although not technically a deadlock, can cause significant timeout problems.

Posting Permissions

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