Using table locks

[thegeezza]

Morning,

If you have a query that you know update every row in a table, is there a significant performance benifit in aquireing an exclusive table lock within the query (as opposed to SQL Server escalating various lower level locks)?

Hope this makes sense.

Cheers,
TheGeezza

 

[vongrunt]

Significant - it depends on query itself. If there are some cursors in action then answer is very probably "yes". If query is atomic by nature (e.g. single mass UPDATE) then optimizer might know what should be locked in advance and immediately attempt table lock (= no performance benefits).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[thegeezza]

Ok, thank you.