Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: Concurrancy/locking troubleshooting

    LUW 9.1 on AIX

    Hey all,

    I'm trying to track down some lock wait/timeout issues. I'm operating under the following premise:

    I have applications that take out some exclusive locks on a table. I then have selects against those tables. The Optimizer is choosing to ignore the index on the predicate of this table and thereby doing a table scan.
    This tablescan is blocked by the lock...

    Does this sound realistic? If so how can I force the optimizer to use the index (I have no control over the SQL).

    Any other tips?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as always - make runstats current - check if reorg needed -
    do db2advis to check objects needed - analyze sql
    exclusive lock - why - batch or online - long running job ??
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Like Guy, I would start with the exclusive lock as well. What do you need this for? We are asking because typically, such constructs are used due to inexperience with database systems and not because there is a real requirement behind it.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Couple of points from me as well

    1> resize LOCKLIST and MAXLOCKs (if it is still set as the default).
    2> ask your SQL developers to use WITH UR (if possible and the business allows that).
    3> Run a Full REORG on the TABLE and the key indexes followed by a RUNSTATs.
    4> Check the CLUSTERFACTOR/CLUSTERRATIO of the Indexes to see whether they are good enough (> 80%) so that the queries prefer to use them.
    5>use db2advis to see if db2 is suggesting significant percentage gain over using of any particular recommended index.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what makes you think DB2 is using tablescan rather than the index? Have you run explain on the SQL to see if it is using the index or not? Also, if the table is under an exclusive lock then you aren't going to get anywhere by using an index, the table is still locked. You have to go after the process(es) that are locking the table and get them changed to allow transactional processing.

    Dave

  6. #6
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by dav1mo
    what makes you think DB2 is using tablescan rather than the index? Have you run explain on the SQL to see if it is using the index or not? Also, if the table is under an exclusive lock then you aren't going to get anywhere by using an index, the table is still locked. You have to go after the process(es) that are locking the table and get them changed to allow transactional processing.

    Dave
    Yes the EXPLAIN showed a table scan despite an apparently suitable index being in existence. I'm not asking if my scenario is reasonable with TABLE X Locks but with Exclusive ROW level locks preventing a TABLESCAN from being performed.

    So my thinking is that if I'm not doing a table scan that I'm far less likely to get into locking contention with the relatively small number of exclusive row locks.

  7. #7
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by JAYANTA_DATTA
    Couple of points from me as well

    1> resize LOCKLIST and MAXLOCKs (if it is still set as the default).
    2> ask your SQL developers to use WITH UR (if possible and the business allows that).
    3> Run a Full REORG on the TABLE and the key indexes followed by a RUNSTATs.
    4> Check the CLUSTERFACTOR/CLUSTERRATIO of the Indexes to see whether they are good enough (> 80%) so that the queries prefer to use them.
    5>use db2advis to see if db2 is suggesting significant percentage gain over using of any particular recommended index.
    1> Both are set to AUTOMATIC
    2> The SQL is automatically generated by an application, changes to the SQL generation is not possible at least in the short-med term
    3> I'll do this today
    4> db2advis doesn't suggest any additional indexes

    Cheers

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure why there is a table scan being done, but try to alter the table to volatile and see if that helps.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by stolze
    Like Guy, I would start with the exclusive lock as well. What do you need this for? We are asking because typically, such constructs are used due to inexperience with database systems and not because there is a real requirement behind it.
    The application generates pretty poor SQL at the best of times. I also see things like applications sitting idle for like 10+ mins while still holding IX table locks and X row locks :/

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The the poor performing SQL may be because of lockwaits. There are a number of ways to determine that, but for starters turn on the lock monitor at dbm level, and look at lockwait time in db snapshot.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by przytula_guy
    as always - make runstats current - check if reorg needed -
    do db2advis to check objects needed - analyze sql
    exclusive lock - why - batch or online - long running job ??

    Will try some reorgs, db2advis gives me nothing... the locks are being generated by normal OLTP activity

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by meehange
    Will try some reorgs, db2advis gives me nothing... the locks are being generated by normal OLTP activity
    Holding locks for 10 minutes is not normal OLTP activity.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    What is the application that is holding LOCK is doing?

    Just because you have an index does not mean it will be used. Based on what you said there could be couple reasons why it is not used.

    Care to share the structure of your table?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  14. #14
    Join Date
    Jul 2004
    Posts
    306
    I believe the application updates some rows in a unch of tables, then it does a select on one of those tables before it does a commit. The select does a tablescan and I THINK fails because a table scan can't complete if there is an exclusive row lock.... am I correct in this assumption?

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to update your db config to change from infinite lockwait (the default) to some reasonable number appropropriate for OLTP. You may be getting more locktimeouts (-911 reason code 68) but it will alert you to the problems when they occur.

    db2 update db cfg for <db-name> using locktimeout 30

    You may have disconnect all applications before it takes effect.

    While you are at it, you should update your locklist size to avoid automatic lock escalation from row level to table locks:

    db2 update db cfg for <db-name> using locklist 4096
    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
  •