"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