467,907 Members | 1,154 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,907 developers. It's quick & easy.

lock escalation : how "expensive"?

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
  • viewed: 1908
Share:
3 Replies
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
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
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.

Similar topics

24 posts views Thread by Hardy | last post: by
45 posts views Thread by Gregory Petrosyan | last post: by
93 posts views Thread by jacob navia | last post: by
36 posts views Thread by Pat | last post: by
350 posts views Thread by Lloyd Bonafide | last post: by
30 posts views Thread by Medvedev | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.