473,324 Members | 2,166 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Turning off lock escalation in DB2

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
3 5799

"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: xixi | last post by:
hi, we are using db2 udb v8.1 on 64 bit windows, this message is in the db2diag.log file ADM5502W The escalation of "38200" locks on table "xxx.xxx" to lock intent "X" was successful. ...
9
by: Jane | last post by:
Our db2diag.log is full of messages like this: 2004-05-31-17.15.10.383766 Instance:tminst1 Node:000 PID:394948(db2agent (TMDB1) 0) TID:1 Appid:GA140956.EF26.03A4B1202647 data management ...
0
by: Bruce Pullen | last post by:
DB2 v7.2 (FP7 - DB2 v7.1.0.68) on AIX 5.2.0.0. We're seeing unexpected single row (then commit) insert locking behaviour. We're seeing Applications that already hold row-level W locks in...
3
by: db2group88 | last post by:
we are using db2 udb v8.1 on windows, the configure parameter for locks is locklist 1000, maxlocks 60, but somehow i still have the error message ADM5502W The escalation of "1" locks on table...
2
by: Trent | last post by:
Hello, all. I have the following production DB2 environment. DB2 8.1.4 (fp4) WG edition with 2 production databases on Windows 2003 standard edition. My first question is regard with...
9
by: kavallin | last post by:
I receives the following in the db2diag.log file many times / day : 2007-03-05-14.55.24.836553+060 E12415C457 LEVEL: Warning PID : 2785 TID : 1 PROC :...
3
by: stefan.albert | last post by:
Hi folks, we have a little discussion about lock escalation... What is better for performance: To have an escalation "early" (smaller locklist) or aviod the escalation with a big lock list? ...
0
by: cburnett | last post by:
Have a peculiar problem regarding lock escalation. Originally lock escalation was occurring because of an application changing most of the rows in a large table. After setting locksize to table to...
1
by: clilush | last post by:
I'm getting entries in the windows event log saying "DB2 is performing lock escalation" which of course is followed by another entrie saying "The escalation of 'xxxx' locks on table 'SYSIBM ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.