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

[tsql]
USE AdventureWorks2014

BEGIN TRAN

UPDATE TOP(100) Person.Address
SET AddressLine1 = AddressLine1
[/tsql]

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

[tsql]
USE AdventureWorks2014

SELECT * FROM sys.dm_tran_locks
[/tsql]

So we can see how SQL Server locks each row individually

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

[tsql]
USE AdventureWorks2014

SELECT TOP 100 * FROM Person.Address
[/tsql]

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.

[tsql]
USE AdventureWorks2014

SELECT TOP 100 * FROM Person.Address
ORDER BY AddressID DESC
[/tsql]

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

[tsql]
USE AdventureWorks2014

ROLLBACK

BEGIN TRAN

UPDATE TOP(5500) Person.Address
SET AddressLine1 = AddressLine1
[/tsql]

Now if we switch to our other tab and run again

[tsql]
USE AdventureWorks2014

SELECT * FROM sys.dm_tran_locks
[/tsql]

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

 
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
 
 
 

Facebook

Google

Twitter

Linkedin

Reddit