Lock Escalation

Question

Lock Escalation

SQL*

SSCertifiable

Points: 6638
More actions
July 25, 2012 at 3:33 am

#259791

Hi,
Today i found something interesting point on lock escalation (from the concurrency prospective).
If the query is updating the rows, and the no.of rows updated by query is less than 5000 then there is no escalation ( from row to table level)
"When a query consumes more than 5000 locks per index / heap."
why this limitation exists in sql server?
Thanks
🙂

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

It's a trade-off between performance and concurrency.

Staying at row-level locking allows other operations to perform on parts of the table not affected by the current query, at the cost of the overhead of obtaining and holding multiple row-level locks.

Escalation to table-level minimises this overhead by only holding one lock on the table, at the cost that other operations on the table must wait until the current query has completed and released the table lock.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Locks take memory. Lots of locks take lots of memory and there's a point where it's just too expensive to hold and check lots and lots and lots of row or page locks.

If SQL didn't escalate it would be perfectly possible for the memory required by the locks to exceed the available memory on the server and when that happens no queries can run at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lokesh Vij SSChampion Points: 10836 More actions · Answer 3

Hi Gail

Just a quick one - Does lock escalation also depend upon the size of the table? Or it only depends upon the memory buffer allocated for holding locks?

Thanks in anticipation.

- Lokesh

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

No and no. 5000 locks (there is a memory threshold, but you shouldn't hit that unless escalation's been disabled)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lokesh Vij SSChampion Points: 10836 More actions · Answer 5

Ohk..got it now. Thanks

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

SQL Show SSCertifiable Points: 6143 More actions · Answer 6

Apart from 5000 locks threshold, in 32Bit, Lock escalation will happen if 24%of memory is used up by database engine,excluding AWE. In 64, its the over all memory by database engine.

SQL* SSCertifiable Points: 6638 More actions · Answer 7

Hi,

Thanks for more explanation,

Here memory means the RAM size right? If i have more memory then this limitation will be exceeded (more than 5000 locks).

My question was,

Why there is a limitation 5000 locks?

Is there any specific reason, on which base sql server is restricting this number?

Thanks,

🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

SQL* (7/25/2012)
Here memory means the RAM size right? If i have more memory then this limitation will be exceeded (more than 5000 locks).

Yes, memory=RAM in this case. No, 5000 locks (on a single index or table).

The memory threshold allows for escalation at less than 5000 locks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL* SSCertifiable Points: 6638 More actions · Answer 9

Thank you,

My question was,
Why there is a limitation 5000 locks?
Is there any specific reason, on which base sql server is restricting this number?

Can any one tell the reason behind this limitation?

Thanks,

🙂