Results 1 to 7 of 7

Thread: lock issue

  1. #1
    Join Date
    Dec 2008
    Posts
    44

    Unanswered: lock issue

    Hi All,
    I have lock issue in a non-prod environment (DB2 9.5 in AIX), the issue is that a core table (application's user authentication table) gets locked by a process and all other subsequent requests time out. This lock issue happens once in a while (once in 3 to 4 weeks). The same application code runs fine in all other environments.

    Though I can see the tables, id involved in locking, I am not able to come up with explanation why the agent is not releasing the lock and why it is happening in only one environment. I have checked db2set/dbm cfg/db cfg values and identical to another env that is working fine.

    Please throw some ideas what should I look for/what should I capture when next time this issue comes up. Appreciate any help on this.

    Thanks.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    How many rows in the table compared to other environments.
    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
    Dec 2008
    Posts
    44
    Table has less than 20 rows. Number of rows are same across all environment. It's a table that the application uses for initial connection/authentication. This table is one in the set of tables provided by application software vendor

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It might be best if DB2 used an index to access the rows, even though it is not necessary for performance reasons. Index access can reduce lock contention in cases where a table scan is always being done. To encourage index access (assuming the appropriate indexes have been created) alter the table to volatile.
    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
    Oct 2007
    Posts
    246
    we faced same locking issue, for table which was having type 1 index when we converted to type 2 the problem solved, is by any chance you db is been upgraded from older version to 9.5, coz in 8.0 and above the default is type 2 index. if its upgraded then u need to convert this index to type 2.

    regds
    Paul

  7. #7
    Join Date
    Dec 2008
    Posts
    44
    Thanks for all the tips, appreciate it.

Posting Permissions

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