OS : AIX,Solaris
UDB 8.2
Porloblem lock escations are causing the deadlocks.


Guys , I am seeing so many lock escations in the database. The diag log
shous thousands of lock esacalation statements in the log.

I have pasted an example of the lock escalation statement below..
Out of these 1000s of statements few turned out to be deadlocks.

I don't have any clue how to solve this problem. Could you guys will help me in finxing this problem?

btw, the Lock list and maxlocks usage is pretty much under control.
I believe these deadlocks are happening because fo too many lock escalations. Is there a way to fix this prblem





2005-11-29-10.34.30.626343-300 E10266661C479 LEVEL: Error
PID : 8161 TID : 1 PROC : db2agent (K065PPN) 0
INSTANCE: instance1 NODE : 000 DB : DBname
APPHDL : 0-1059 APPID: GA214279.P5F3.05F039152831
FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:4
MESSAGE : ADM5503E The escalation of "102407" locks on table "schema.Tabname"
to lock intent "S" has failed. The SQLCODE is "-911".

I beg to differ...

If you're having escalations it's because an application is taking up more than MAXLOCKS of your LOCKLIST, and DB2 decided it was time to escalate to a table lock.

You either need to track down the statement that's causing the problem and rewrite it / tune it / etc so it doesn't require as many locks, or increase the LOCKLIST/MAXLOCKS parameters to accomidate it, if possible.

What's the exact error you're seeing BTW, sql0911 rc=2 (deadlock) or rc=68 (timeout)?

Its rc=68.

1010 COM.ibm.db2.jdbc.DB2Exception:
[IBM][CLI Driver][DB2/SUN] SQL0911N The current transaction has been
rolled back because of a deadlock or timeout. Reason code "68".
SQLSTATE=40001

at


The transactions are timing out.

OK, that's slightly different.

Track down the statement and make sure:
- The app commits as often as possible
- The app has the lowest isolation level possible given your business requirements
- The statements are well written and the access plans efficient
- The app can't be run off-hours (ie. if it's a bulk delete or report)

If you want the other apps to wait for it to complete, you can always increase LOCKTIMEOUT... but be really confident the long-running statement will complete in some reasonable amount of time, or that could just make the situation worse as all the other users line up waiting for locks.

enlarge LOCKLIST size

I believe lock list memory usage is pretty much under control..

Max storage for lock list (4KB) (LOCKLIST) = 2500
Percent. of lock lists per application (MAXLOCKS) = 40
Lock timeout (sec) (LOCKTIMEOUT) = 120



Time database waited on locks (ms) = 91873265
Lock list memory in use (Bytes) = 16840
Deadlocks detected = 1063
Lock escalations = 6911
Exclusive lock escalations = 295
Agents currently waiting on locks = 0
Lock Timeouts = 204
Number of indoubt transactions = 0

If you have the memory, you could go higher.

But first make sure those locks are neccessary by tracking down the problem statement/application... Sorry to harp on it, but I don't think increasing values without understanding the root of the problem is a good idea, and in the end it may not even solve the issue.

Petruck..
I did not still understand why we have to increase the lock list.
The lock list usage under control and max locks is 40 percent.

So the locklist is not even 10 percent used.

At the moment. That's not the high water mark, though, and the escalation suggests that the 40% is indeed being hit.

If the usage of locklist is not more than 10%, why was there a lock escalation in the first place ?

DB2 would have started acquiring row locks and the locklist would have gone upto 40% (1000 in your case). This will have caused the lock escalation to occur, ie , all row locks are released and a table lock is taken, bringing down the locklist usage to 10% …

The very purpose of lock escalation is to release the locklist space for other applications to use …

Ok..you mean to say at the time i have taken the snapshot the lock list usage is not showing the max.?

Yes

Cheers
Sathyaram