Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Posts
    33

    Unanswered: lock timeouts - db snapshots

    What is the first / best thing to do , if u get lock timeouts (db snapshot) greater than 0.

    Thanks,
    mc

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try to determine which applications and/or utilities timed out waiting for a lock to be released that is held by another process. This will provide a starting point to determine which process is holding the needed lock(s) for such a long time, and whether there is a scheduling issue, commit frequency issue, lock escalation issue, etc that can be resolved.

  3. #3
    Join Date
    Dec 2002
    Posts
    134

    Re: lock timeouts - db snapshots

    Originally posted by mcdb2
    What is the first / best thing to do , if u get lock timeouts (db snapshot) greater than 0.

    Thanks,
    mc
    1. Check lock parameters - locklist, maxlocks, locktimeout
    2. Turn on statement and lock monitors and identify activities/sql that are responsible for the lock timeout.

    Once you know why you have lock timeouts, you can decide what to do. Some of the options are

    speed up transaction that is blocking
    increase locklist/locktimeout, if it's ok to wait or if you have lock escalations
    break transations into smaller parts
    create indexes (can help in both speed and lock granularity)

    P.S.
    you can use my monitor (free) to monitor applications for locks
    http://chuzhoi_files.tripod.com/

    regards,
    dmitri

  4. #4
    Join Date
    Aug 2003
    Posts
    33
    Marcus ,dmitri

    Thanks. I really appreciate yor help in explaining things in detail.

    mc

  5. #5
    Join Date
    Dec 2002
    Posts
    10
    The locktimeout value setting is based on what kind applications are running on the system. For OLTP environment, keep it at 10 seconds and for BI keep it at 60 seconds

    A high value of locktimeout is caused by

    - current locktimeout is low
    - a cuncurrency problem caused by lock escalations

  6. #6
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170

    Re: lock timeouts - db snapshots

    Hi

    Your snapshots of the tool looks very interesting.
    I tried to install your tool and it failed saying my system is not suitable to run that dll.
    What are the prerequisites for your tool

    Thanks
    Prasad

    Originally posted by chuzhoi
    1. Check lock parameters - locklist, maxlocks, locktimeout
    2. Turn on statement and lock monitors and identify activities/sql that are responsible for the lock timeout.

    Once you know why you have lock timeouts, you can decide what to do. Some of the options are

    speed up transaction that is blocking
    increase locklist/locktimeout, if it's ok to wait or if you have lock escalations
    break transations into smaller parts
    create indexes (can help in both speed and lock granularity)

    P.S.
    you can use my monitor (free) to monitor applications for locks
    http://chuzhoi_files.tripod.com/

    regards,
    dmitri

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: lock timeouts - db snapshots

    Prasad .. I'd suggest that you tell what system(OS) you were trying on ...

    this may help dmitri to understand/analyze the problem

    Thanks

    sathyaram

    Originally posted by koganti
    Hi

    Your snapshots of the tool looks very interesting.
    I tried to install your tool and it failed saying my system is not suitable to run that dll.
    What are the prerequisites for your tool

    Thanks
    Prasad
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: lock timeouts - db snapshots

    dmitri , I have listed your page in the 'Useful DB2 Stuff'

    Cheers

    sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Dec 2002
    Posts
    134

    Re: lock timeouts - db snapshots

    Originally posted by koganti
    I tried to install your tool and it failed saying my system is not suitable to run that dll.
    What are the prerequisites for your tool
    I trested the program on win2k - db2 v7 up to fixpack 8 and v8 up to fixpack 2.
    For v6 there is a special dll, you can download it separately

    What is the configuration of your system - os/db2 version?
    What message are you getting?
    If you are interested in resolving the problem, email me with the information I asked. I do not think it make sense to discuss the problem here.

    2 sathyaram:
    Originally posted by sathyaram_s
    dmitri , I have listed your page in the 'Useful DB2 Stuff'
    thanks for the recognition

    regards,
    dmitri

Posting Permissions

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