What is Lock Escalation?
SQL Server BOL describes
lock escalation as the process of changing multiple fine-grain locks into what are described as coarse-grain locks.
As there will be fewer coarse-grain locks this process reduces system overhead, however increases the likelihood of contention for concurrency.
When do Locks Escalate?
There are two main situations where a lock escalation will occur, this is assuming that lock escalation is not disabled on the table.
- A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.
A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table
and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.
- The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
How to disable Lock Escalation
If you want to you can disable lock escalation altogether on a table.
USE DBAFire_DB --Show current lock escalation SELECT lock_escalation_desc FROM sys.tables WHERE name = 'Employees'; --Alter to DISABLED ALTER TABLE dbo.Employees SET (LOCK_ESCALATION=DISABLE)
To set it back to the default (TABLE) run this
USE DBAFire_DB ALTER TABLE dbo.Employees SET (LOCK_ESCALATION=TABLE)