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:

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.

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.

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):

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

1_SQL_Server_2008_Lock_Escalation_explained

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

Run the DMV again:

2_SQL_Server_2008_Lock_Escalation_explained

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.

Amit Bansal
SQL MCM, SQL MVP
Director – eDominer Systems, Peopleware India, SQLMaestros

Follow me on Twitter | Follow me on FaceBook | Connect on LinkedIn | Google+
FB page | Know more about me

SQL Server 2014 new DMV - Figure It Out Yourself
SQL Server 2014 New System Objects - Figure It Out Yourself