Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    24

    Unanswered: Lock Wait Time in Select Query

    Hi,

    Is there way a Lock Wait Time can be addd in Select Query (Using table X) so that if there is an insertion going on in the same table X by other process, then the Select Query should not be deadlocked and wait for Lock Wait Time.

    There is a strange process happening in the system whereby on doing a SELECT, application is getting a deadlock.

    Gulshan

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can do the select with isolation level UR (uncommitted Read). Check out the manuals to make sure you understand what that means for the integrity of the data retrieved in the select statement.

    Lock wait time can be increased for the database, but not for a particular query (of course if UR is used, there is no locking). To maximize concurrency, have all applications use CS (Cursor Stability isolation level) and perform commits at a reasonable frequency if possible.

    Also, when trying to fix a lock contention problem, make sure you understand the difference between a lock time-out, and a true deadlock. Although both are due to lock contention, they are often different in how you resolve them.
    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
    Jun 2003
    Posts
    24
    Thanks Marcus,

    Can I get any reference document to the difference between a lock time-out, and a true deadlock

    Originally posted by Marcus_A
    You can do the select with isolation level UR (uncommitted Read). Check out the manuals to make sure you understand what that means for the integrity of the data retrieved in the select statement.

    Lock wait time can be increased for the database, but not for a particular query (of course if UR is used, there is no locking). To maximize concurrency, have all applications use CS (Cursor Stability isolation level) and perform commits at a reasonable frequency if possible.

    Also, when trying to fix a lock contention problem, make sure you understand the difference between a lock time-out, and a true deadlock. Although both are due to lock contention, they are often different in how you resolve them.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There is a good discussion of deadlocks on page 14 of the Administration Guide: Performance (version 8). You should be able to find a good general discussion of locking and isolation levels in the same manual. The DB2 manuals can be downloaded from the IBM website. See the Useful DB2 Stuff thread for a link.
    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
  •