By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,611 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,611 IT Pros & Developers. It's quick & easy.

Effects of Increasing the locklist parameter

P: n/a
Hi,

I was getting the error: sqlcode: -911 sqlstate: 40001 , which is
"The maximum number of lock requests has been reached for the
database."
So i increased the locklist size to 200 from the default value of 100.

I wanted to know what other effects it will have on the database?
Like, will the performance reduce, if the locklist size is 200 and 120
locks are on it as compared to when the locklist size is 130 and 120
locks requests.

If yes, how can i try to improve the performance degradation caused by
increasing the locklist?

Also, when i run the command "db2 list applications", the number of
applications running is much less than 100, still it gives the above
error of locklist getting exceeded.

Thanks

Rahul

Aug 22 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Aug 22, 10:25 am, Rahul B <rahul.babb...@gmail.comwrote:
Hi,

I was getting the error: sqlcode: -911 sqlstate: 40001 , which is
"The maximum number of lock requests has been reached for the
database."
So i increased the locklist size to 200 from the default value of 100.

I wanted to know what other effects it will have on the database?
Like, will the performance reduce, if the locklist size is 200 and 120
locks are on it as compared to when the locklist size is 130 and 120
locks requests.

If yes, how can i try to improve the performance degradation caused by
increasing the locklist?

Also, when i run the command "db2 list applications", the number of
applications running is much less than 100, still it gives the above
error of locklist getting exceeded.

Thanks

Rahul
It seems like you got reason code 68 with sqlcode -911 which is for
locktimeout.
In case there are too many lock escalations (which you may find using
the snapshot monitor), then you should try increasing locklist
parameter. Following URL should help you out more
http://publib.boulder.ibm.com/infoce...n/r0000267.htm

Regards
Saurabh

Aug 22 '07 #2

P: n/a
On Aug 22, 12:33 pm, spider007 <saurabh.saurabhj...@gmail.comwrote:
On Aug 22, 10:25 am, Rahul B <rahul.babb...@gmail.comwrote:
Hi,
I was getting the error: sqlcode: -911 sqlstate: 40001 , which is
"The maximum number of lock requests has been reached for the
database."
So i increased the locklist size to 200 from the default value of 100.
I wanted to know what other effects it will have on the database?
Like, will the performance reduce, if the locklist size is 200 and 120
locks are on it as compared to when the locklist size is 130 and 120
locks requests.
If yes, how can i try to improve the performance degradation caused by
increasing the locklist?
Also, when i run the command "db2 list applications", the number of
applications running is much less than 100, still it gives the above
error of locklist getting exceeded.
Thanks
Rahul

It seems like you got reason code 68 with sqlcode -911 which is for
locktimeout.
In case there are too many lock escalations (which you may find using
the snapshot monitor), then you should try increasing locklist
parameter. Following URL should help you out morehttp://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/c...

Regards
Saurabh
Yes,
I have increased it.
I wanted to know if there will be any performance issues/other issues
that might come up with increasing this parameter.

Rahul

Aug 22 '07 #3

P: n/a
"Rahul B" <ra***********@gmail.comwrote in message
news:11*********************@x40g2000prg.googlegro ups.com...
I have increased it.
I wanted to know if there will be any performance issues/other issues
that might come up with increasing this parameter.

Rahul
Unless you have a read only database (data warehouse), you should increase
it to 4096. The default is 100, but I believe that has not changed since
1990.

Having a larger locklist helps prevent lock escalation from row to table
level. Table level locking is more efficient in theory (because DB2 does not
have to keep track of locks on each row), but has poor concurrency if rows
are being inserted, updated, or deleted. Even though table level locking may
be faster than locking every row (strictly in terms of CPU utilization), DB2
can lock rows in locklist memory very efficiently and it is rarely a
problem.

Getting a -911 or having long lock wait times (which you may not be seeing
if the locktimeout parm is not exceeded) is usually a MUCH bigger
performance problem in terms of total execution time of SQL statements.
Increase it to 4096.
Aug 22 '07 #4

P: n/a
On Aug 22, 4:56 am, Rahul B <rahul.babb...@gmail.comwrote:
On Aug 22, 12:33 pm, spider007 <saurabh.saurabhj...@gmail.comwrote:


On Aug 22, 10:25 am, Rahul B <rahul.babb...@gmail.comwrote:
Hi,
I was getting the error: sqlcode: -911 sqlstate: 40001 , which is
"The maximum number of lock requests has been reached for the
database."
So i increased the locklist size to 200 from the default value of 100.
I wanted to know what other effects it will have on the database?
Like, will the performance reduce, if the locklist size is 200 and 120
locks are on it as compared to when the locklist size is 130 and 120
locks requests.
If yes, how can i try to improve the performance degradation caused by
increasing the locklist?
Also, when i run the command "db2 list applications", the number of
applications running is much less than 100, still it gives the above
error of locklist getting exceeded.
Thanks
Rahul
It seems like you got reason code 68 with sqlcode -911 which is for
locktimeout.
In case there are too many lock escalations (which you may find using
the snapshot monitor), then you should try increasing locklist
parameter. Following URL should help you out morehttp://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/c...
Regards
Saurabh

Yes,
I have increased it.
I wanted to know if there will be any performance issues/other issues
that might come up with increasing this parameter.

Rahul- Hide quoted text -

- Show quoted text -
In general, increasing the locklist parameter will not have any
negative performance impact. That being said, there are some rare
situations where we've seen increases in the locklist parameter which
cause performance degredations. Let my try to explain how this can
happen.

Increasing the locklist parameter increases the amount of locks that
are available to the database. This serves to reduce the probability
that a lock escallation will occur. Of course, if the number of rows
that need to be locked for any given query is larger than the locks
available in the locklist then lock escallation is inevitable.
Peformance problems can occur if the database thinks it can avoid an
escallation and tries to perform row locking for one or more tables.
Then, midway through acquiring row locks, it realizes that there
aren't enough locks available and so it must release all of the
already acquired row locks and acquire a table lock. If, with a
smaller locklist, it would have acquired a table lock directly, the
performance impact is in acquiring and releasing all of the row locks.

Adam

Aug 23 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.