What is Lock Escalation in SQL SERVER?

Lock Escalation is the process of converting many fine-grain locks [rows or page locks] into fewer coarse-grain locks [Table Level].

While Database engine receives large amount of SQL update operation on particular table, it is more efficient to use fewer coarse-grain locks [Table lock] instead of having many fine-grain locks [rows/page]. So system overhead is reduced.

When it occurs?

Database engine has component called Lock Manager which escalates to fewer coarse-grain locks [Table] from many fine-grain locks [Rows\Page], in case of below mentioned 2 situations arises.

1.   When Lock threshold value reaches its maximum limit [5000].

2.   When memory threshold reaches Lock Manager.

How to test Lock Escalation?

Let’s execute these scripts to test the Lock Escalation process.

/* Lock Escalation does not occur on this code, as threshold value does not reach the limit*/

USE [EmployeeDB]

GO

BEGIN TRAN

UPDATE TOP (1000) Employee

SET salary = 45000

GO

SELECT

            resource_type,

            resource_database_id,

            resource_description,

            resource_associated_entity_id,

            request_mode,

            request_type,

            request_status

FROM

            sys.dm_tran_locks

WHERE

            resource_database_id = DB_ID('EmployeeDB');

GO

COMMIT;

/* Lock Escalation occurs on this code as threshold value reaches the maximum limit and we will find the OBJECT resource type on DMV’s result*/

USE [EmployeeDB]

GO

BEGIN TRAN

UPDATE TOP (5001) Employee

SET salary = 45000

GO

SELECT

            resource_type,

            resource_database_id,

            resource_description,

            resource_associated_entity_id,

            request_mode,

            request_type,

            request_status

FROM

            sys.dm_tran_locks

WHERE

            resource_database_id = DB_ID('EmployeeDB');

GO

COMMIT;

Let’s follow the execution results for the same.

Initially we don't find any locks from DMV's as we are not yet started the script execution.

 

Now we started the script execution.

Now we will see that many fine grain-locks occurred.

Now Commit the previous transaction. And start the new transaction by exceeding the lock escalation threshold value.

  

Now you will find that fewer Coarse-grain locks [OBJECT type] from DMV's, as LOCK Escalation occurs from database engine.