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

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.

Thanks Marcus,

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

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.