Apr
15
2016

Back To Basics, Lock Escalation in SQL Server

Today at work we’ve had a very common request, no big deal, just a couple of UPDATE which affect tens of thousands of rows in a couple of tables independently.

I can say I’ve been there, done that before so I passed this to my unfortunate junior, so I can learn enough from his walkthrough to write this post.

To accomplish this sort of task, we can take a simple approach, you write the SELECT statement and once you’re happy with the results, convert it to UPDATE to get the job done. Easy peasy.

Sure, and I’d do it that way if I were allowed to have downtime. Errr? Did I say downtime? Yes, indeed.

Most of you have realized already what I’m talking about, but if not, just read the title again 🙂

You’re right, LOCK ESCALATION.

 
The theory

According to Books Online

Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention

And for non partitioned tables also states that

Lock escalation is triggered when lock escalation is not disabled on the table and a single Transact-SQL statement acquires at least 5,000 locks

That in English means that if you try to update too many rows at once, you will lock the whole table until your updates finish.

Obviously in that process, no one will be allowed access that table. If you have users working while you’re planning to UPDATE thousands of rows, that is something to always keep in mind.

In a simple scenario, we can say that the threshold would be close to that number, although, that number can vary depending on other factors like available memory on the server. However for this example I’ll be alone in the server, so the threshold shouldn’t be much different than that stated in BOL.

First let me show you this phenomenon.

 
How it happens

Open a new tab in SSMS and run

USE AdventureWorks2014 

BEGIN TRAN

UPDATE TOP(100) Person.Address
SET AddressLine1 = AddressLine1

We can observe how SQL Server will stop anyone from accessing those rows, open another tab, and run

USE AdventureWorks2014

SELECT * FROM sys.dm_tran_locks

So we can see how SQL Server locks each row individually

01_hundred_locks

Also if we try to access those rows, we will have to wait, otherwise we are free to access other rows not affected by the transaction. If we run

USE AdventureWorks2014

SELECT TOP 100 * FROM Person.Address

We will have to wait until the current transaction ends by a COMMIT or a ROLLBACK.

But if we pick some other rows, there is no problem at all and rows are returned.

USE AdventureWorks2014

SELECT TOP 100 * FROM Person.Address
ORDER BY AddressID DESC

02_SELECT_TOP_100_DESC

Now it’s when we push it a bit further to see how LOCK ESCALATION happens; I’d love to have a magic number that always works, but as I said, it varies

I’ll play it safe by going a bit bigger than 5000 Updates, so you will see how rows are not locked anymore but the table is.

First let’s ROLLBACK our transaction and open a new one

USE AdventureWorks2014 

ROLLBACK 

BEGIN TRAN

UPDATE TOP(5500) Person.Address
SET AddressLine1 = AddressLine1

Now if we switch to our other tab and run again

USE AdventureWorks2014

SELECT * FROM sys.dm_tran_locks

03_Table_LOCKED

See there is an eXclusive lock on the object, not on each individual row anymore, which means no other user can access the entire object while the transaction is running, and that my friends, means downtime.

Any attempt to access the table will have to wait until the open transaction finish. You can see the output of sp_WhoIsActive if I try to do a SELECT

04_sp_WhoIsActive

 
Conclusion

So, now you’ve seen how something which seems completely harmless can indeed make your users (and boss) very upset.

LOCK ESCALATION is a feature to keep in mind when you work with big data sets. There is much more than what I’ve told you here, so please refer to BOL to get a deeper understanding.

I look forward to seeing how you guys usually approach this kind of scenarios, so please feel free to comment and thanks for reading
 
 
 

3 comments
  1. Shane O'Neill says:

    +1 this!

  2. SCOTT MCNITT says:

    Sweetness. I did not know that there was a fixed number of rows for the threshold to escalate. I thought it would depend on the resources available (memory, etc) such that if more locks could sometimes be acquired in some cases above as well as in some cases below that 5000 row noted.

    I feel the G-ness (which makes me think of beer).

Leave a Reply

Your email address will not be published. Required fields are marked *

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