On Jul 2, 8:39 am, Racerx <p.paingan...@gmail.comwrote:
Hi All:
I use db2 v8 fp 14 on Aix.
I am facing problems with a stored procedure that is causing a lot of
locks and also goes in the lock wait state ending up hanging the
application.
Tried using the UR isolation mode but it did not help.
Can some one please suggest me how to debug this problem and any
specific step or areas that I need to look at..??
Thanks and Regards,
Racerx...
Not sure if you realize this, but isolation level only affects select
statements and how long locks are being held by those select
statements (except that select with UR will ignore other locks held by
others). Inserts, updates, and deletes will not ignore locks held by
others even with UR.
If your stored procedure recieves a lock-timeout (error -911 RC=68)
then it was waiting on some locks held by another applicaiton (which
is likely the culprit). If your applicaiton just hangs indefinitely,
then your locktimeout parmerter for the db is probably set to -1 (wait
indefinitely). Try changing it to about 30 seconds (db2 update db cfg
using LOCKTIMEOUT 30). this will not necessarily solve the problem,
but at least your apps will not hang. You need to find out which other
application is holding the locks for more than 30 seconds.
Generally, locks are released when an application issues a COMMIT. You
cannot be too rich, too thin, or commit too often.