Lock escalation in SQL Server

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)
Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.