lock escalation : how "expensive"?

[stefan.albert]

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

[Serge Rielau]

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

[stefan.albert]

On 21 Jun., 13:11, Serge Rielau <[e-mail]:

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

[Serge Rielau]

stefan.albert wrote:

On 21 Jun., 13:11, Serge Rielau <[e-mail]:

>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