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

lock escalation : how "expensive"?

P: n/a
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?

We don't care about concurrency - because the changes have to be done,
other SQL waiting for this is OK.

Anyhow - I think: A lock table in exclusive mode would be the the most
performant way. Correct?

Any hints or other points of view?

thx
Stefan

Jun 21 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
stefan.albert wrote:
Anyhow - I think: A lock table in exclusive mode would be the the most
performant way. Correct?
Yes. It's a pretty brutal approach though....
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 21 '07 #2

P: n/a
On 21 Jun., 13:11, Serge Rielau <srie...@ca.ibm.comwrote:
stefan.albert wrote:
Anyhow - I think: A lock table in exclusive mode would be the the most
performant way. Correct?

Yes. It's a pretty brutal approach though....
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Hi Serge,

can you give me a hint: What is better for performance: Early
escalation or late one (or none at all)?

thx
Stefan

Jun 21 '07 #3

P: n/a
stefan.albert wrote:
On 21 Jun., 13:11, Serge Rielau <srie...@ca.ibm.comwrote:
>stefan.albert wrote:
>>Anyhow - I think: A lock table in exclusive mode would be the the most
performant way. Correct?
Yes. It's a pretty brutal approach though....
can you give me a hint: What is better for performance: Early
escalation or late one (or none at all)?
Uhm.. I thought I was explicit enough. Anyway.
Lock escalation means a lot of effort is being wasted. I.e. teh
collecting of all the individual row locks. If you know you will update
the whole table (or you are de-facto alone on the system) you save all
that work by locking the table upfront.
Avoidance of lock-escalation is important to keep concurrency. Something
you say you don't care about.
So I would say:
1. LOCK table
2. Escalate
3. Avoid escalation
(exactly the opposite of what you would normally want)

For read queries in a read only database (perhaps a mart) i recommed
simply running UR.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 21 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.