SQL Server Lock Escalation explained

Hi Friends,

I wrote a blog post on partition-level locking where I demonstrated how locks are escalated from row to partition. The blog post is here;

A few readers wrote back to me asking when does SQL Server escalate the row locks to table or partition level locks. So in this post, I will talk about SQL Server Lock escalation.

Well, the DB engine has a component called the Lock Manager, which escalates fine-grained locks (rows & page locks) to more coarse grained locks (partition or table locks) under 2 circumstances:

1. When the lock threshold is reached which as per BOL is 5000 (for a single instance of the objection in question)

2. When the memory threshold is reached for Lock Manager, even if the number of locks threshold of 5000 is not reached.

Let me quickly demonstrate this to you:

I wrote the following query:

USE AdventureWorks
GO
 
BEGINTRAN
UPDATETOP (5000) Person.Contact
SET EmailPromotion = 0

You can see I am placing 5000 ROW locks, but you when you run the sys.dm_tran_locks DMV, you can see that table lock has been acquired.

select*fromsys.dm_tran_lock

Well, this perfectly in line with BOL. However, I was playing around with the numbers and I changed the value to 4900. Before you run the modification query each time, do not forget to ROLLBACK TRAN.

ROLLBACKTRAN
 
BEGINTRAN
UPDATETOP (4900) Person.Contact
SET EmailPromotion = 0

I again ran the DMV and this time also, I saw a table lock. Probably, this time I expected 4900 ROW locks. But may be the memory threshold was reached, so I continued playing with the numbers until I found that the threshold on my system is 4707/4708; where if the number of ROW locks are 4708 or more, the DB engine escalates this to a table lock, else anything below that number is those many row locks.

So if I execute the following (ROLLBACK any earlier transaction):

BEGINTRAN
UPDATETOP (4707) Person.Contact
SET EmailPromotion = 0

And then check the DMV, you shall see those many ROW locks. You can run the following:

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();

However, when I change the value to 4708, escalation happens. (ROLLBACK the earlier transaction)

BEGINTRAN
UPDATETOP (4708) Person.Contact
SET EmailPromotion = 0

Run the DMV again:

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();

You can observe the table lock.

ROLLABCK the transaction to clean up.

It will interesting to see/hear your output when you run the same example on your system.