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

Turning off lock escalation in DB2

P: n/a
Does DB2 have a setting to turn off lock escalation? I've just done
several hours of newsgroup googling, and have found only a couple
references to such a setting, for DB2 v4.1 back in the late 90's.

One of our apps is (fairly) regularly grabbing table locks instead of
row locks. I've bumped maxlocks up as high as 95% with locklist at
2048 (4kB chunks). I'm only dealing with a few hundred rows of data
in my test environment. And still, DB2 escalates the locks to table
locks.

Any ideas on how to prevent the escalation, through further tuning, or
with some kind of system wide 'never escalate' setting?

Any help (even negative answers) much appreciated!
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"Amy Woodward" <am*********@yahoo.com> wrote in message
news:95**************************@posting.google.c om...
Does DB2 have a setting to turn off lock escalation? I've just done
several hours of newsgroup googling, and have found only a couple
references to such a setting, for DB2 v4.1 back in the late 90's.

One of our apps is (fairly) regularly grabbing table locks instead of
row locks. I've bumped maxlocks up as high as 95% with locklist at
2048 (4kB chunks). I'm only dealing with a few hundred rows of data
in my test environment. And still, DB2 escalates the locks to table
locks.

Any ideas on how to prevent the escalation, through further tuning, or
with some kind of system wide 'never escalate' setting?

Any help (even negative answers) much appreciated!


You're on the right track.

I think what you might be suffering from is a bad query plan. You may just
have a few hundred rows in your database, but if one or more of your queries
are doing table scans instead of index scans, they will be locking every row
in that table which might be be causing DB2 to escalate the locks to table
locks.

In the lock escalation messages in db2diag.log, the name(s) of tables on
which locks are escalating are listed. You should obtain query plans for
every query using these table(s) using the db2expln or db2exfmt utilities.
For any plan that uses a table scan on these table(s), consider creating the
appopriate index, as this will convert the table scan (locking every row of
the table) into an index scan (locking only the rows that match.)

--
Matt Emmerton
Nov 12 '05 #2

P: n/a
Ian
Amy Woodward wrote:
Does DB2 have a setting to turn off lock escalation? I've just done
several hours of newsgroup googling, and have found only a couple
references to such a setting, for DB2 v4.1 back in the late 90's.

One of our apps is (fairly) regularly grabbing table locks instead of
row locks. I've bumped maxlocks up as high as 95% with locklist at
2048 (4kB chunks). I'm only dealing with a few hundred rows of data
in my test environment. And still, DB2 escalates the locks to table
locks.


You can use the LOCK TABLE statement which locks the table immediately
(no escalation), for the duration of the transaction;

Or you can set the lock size for the table (ALTER TABLE ... LOCKSIZE
TABLE).


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #3

P: n/a
You cannot disable lock escalation, but you an prevent it from occuring by
increasing locklist. Since yours is far too small for real (data warehousing
type) work, it is also highly recommended. Maxlocks should be returned to
its default value - make it too large and you end up with concurrency
problems.

OTOH, since you have tiny tables, you may have a "we don't commit" problem.
Try committing regularly and remembering that not executing
insert/update/delete is no reason not to commit (unless, of course, you
explcitly want lock escalations and/or deadlocks and timeouts).

"Amy Woodward" <am*********@yahoo.com> wrote in message
news:95**************************@posting.google.c om...
Does DB2 have a setting to turn off lock escalation? I've just done
several hours of newsgroup googling, and have found only a couple
references to such a setting, for DB2 v4.1 back in the late 90's.

One of our apps is (fairly) regularly grabbing table locks instead of
row locks. I've bumped maxlocks up as high as 95% with locklist at
2048 (4kB chunks). I'm only dealing with a few hundred rows of data
in my test environment. And still, DB2 escalates the locks to table
locks.

Any ideas on how to prevent the escalation, through further tuning, or
with some kind of system wide 'never escalate' setting?

Any help (even negative answers) much appreciated!

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.