Which Locks Count Toward Lock Escalation?

A little while back I wrote about Why Indexes Reduce Locks for Update and Delete Queries.

I got a great question on the post from Zac:

What’s not super clear is why it takes out a lock on the whole table, is this because it does a lock escalation as a result of the Full Scan?
Will this always happen, or is there a threshold of record update counts where this will occur?

This was tough to answer in just a comment, so I promised a full post on the topic.

SQL Server attempts lock escalation to simplify locks

It can be tricky to manage a lot of little fine grained locks. If I take out 50,000 row locks on a single table, it would be easier for SQL Server to manage that by just giving me one table level lock. But this may not be possible if others are using the table.

When you take out a lot of modification locks, SQL Server will attempt to “escalate” them. If it can’t escalate and I keep taking out locks, it will keep trying.

Books Online has a good article about this, which explains a lot of the details about how many locks you need to take out to trigger lock escalation. Here are the (simplified) basics:

  • The ‘magic’ number to trigger escalation for the first time is 5,000 locks on a single table reference
  • Locks do NOT escalate from row level to page level. Row locks escalate to table. Page level locks also escalate to table level. In other words, forcing row level locking will not make it less likely to escalate locks to the table level, but rather it will do the opposite.
    • Note: for partitioned tables, you have the option to enable partition level escalation
  • If you’re modifying data, the escalated table lock will be exclusive. That means nobody else can party with the table if lock escalation succeeds while you’re doing your work.

But which locks cause escalation? All those update locks don’t cause escalation, do they?

No, the update locks do NOT cause escalation. Just as a reminder, “update” locks are weirdly named– these are a special kind of lock (not just a type of lock associated with an “update” statement). Read more in my post on update locks.

Let’s look at a simple test I ran on a VM to show update locks not triggering the “escalator”.

I’m using the WideWorldImporters sample database again. To make sure I get the most update locks possible for my test, I dropped the index on CustomerID:

DROP INDEX [FK_Sales_Orders_CustomerID] ON [Sales].[Orders]

This forces a clustered index scan on all the sample queries I’m going to run below.

I’m running two Extended Events Traces

The first trace is looking at the sqlserver.lock_escalation event. Note that I’m using NO_EVENT_LOSS and MAX_DISPATCH_LATENCY=5 in this trace— that’s because I’m running this against a totally private test instance, and it doesn’t matter if I impact performance.

ADD EVENT sqlserver.lock_escalation(SET collect_database_name=(1),collect_statement=(1)
ADD TARGET package0.event_file(SET filename=N'S:\XEvents\Lock_Escalation.xel')

The second trace is counting the locks used used by session_id=56. It’s looking for locks against a particular object (I don’t care about metadata locks), and it’s putting the output in a histogram target bucketed by the lock mode:

CREATE EVENT SESSION [locks_count_spid_56] ON SERVER 
ADD EVENT sqlserver.lock_acquired(
    WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(56)) AND [associated_object_id]=(72057594047234048.)))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.lock_acquired',source=N'mode',source_type=(0))

Note: this article helps decode the integer that represents the lock mode in this trace.

Looking at this, I’m embarrassed by how inconsistent I am at naming traces. But I’m consistently bad at that, so… yeah.

Test query #1 modifies only 165 rows, no lock escalation

Here’s our first contender…

UPDATE Sales.Orders
SET InternalComments = N'Hiya'
WHERE CustomerID = 2

This does a clustered index scan, but modifies only 165 rows. I roll back the transaction just for further testing.

What do the traces have to say?

  • The LockEscalation trace doesn’t say anything. We had NO lock escalation for this trace.
  • The locks_count_spid_56 trace shows
    • 104,184 update locks
    • 2,591 intent update locks
    • 165 exclusive locks
    • 153 intent exclusive locks

We had way more than 5K update locks on this object, but they don’t count toward lock escalation.

Test query #2 modifies 4,951 rows and DOES escalate locks

Before starting this test, I restarted my locks_count_spid_56 trace to reset it. Then I ran this query:

UPDATE Sales.Orders
SET InternalComments = N'Hoya'
WHERE CustomerID > 1013

This also does a clustered index scan, and modifies just under 5,000 rows. So it would seem like this wouldn’t escalate. Let’s see!

  • The LockEscalation trace has a row! This escalated.
    • escalation_cause = Lock threshold
    • escalated_lock_count (number of locks converted) = 6,248
    • hobt_lock_count (number of locks at time of escalation) = 6,247
    • The statement collected matches this query. (The recompile hint in the query is there to prevent auto-parameterization in this simple, so it’s very clear which query was run.)
  • The locks_count_spid_56 trace shows
    • 97,842 update locks
    • 2,340 intent update locks
    • 4,475 exclusive locks
    • 1,772 intent exclusive locks

Time for a little math

4,475 exclusive locks + 1,772 intent exclusive locks = 6,247 locks at the time of escalation.

The update and intent update locks don’t count toward escalation.

What does this all mean?

Good indexing can reduce the number of update locks that queries take out — that can reduce blocking, because update locks block one another. And besides, good indexing can make queries faster.

Lock escalation converts exclusive and intent exclusive locks. The initial threshold to trigger lock escalation is 5,000 locks used in a single table reference, but you might hit that threshold even if you’re modifying less than 5,000 rows.

If you have to modify a lot of rows in a table that’s being used by others where performance matters, lock escalation is one of multiple factors that makes it desirable to break the modifications up into smaller transactions.

Having lock escalation isn’t necessarily a bad thing. If escalation succeeds, it’s possible that the query with escalated locks doesn’t end up blocking anyone else. You need to monitor the SQL Server to know whether or not you’ve got a blocking problem, and who is blocking whom.

5 Comments. Leave new

  • Hi Kendra,

    I have a follow up Question on how to determine the number and the kind of locks taken using extended events. In the above post you have mentioned the following:
    • The locks_count_spid_56 trace shows
    • 104,184 update locks
    • 2,591 intent update locks
    • 165 exclusive locks
    • 153 intent exclusive locks

    How can we deduce the above information using the trace. Is this available in the trace itself or do we have to run an XQuery to determine this information.

    Thanks much,

    • Hi Meher,

      I set up the trace locks_count_spid_56 with a histogram target:

      ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.lock_acquired',source=N'mode',source_type=(0))

      So that target is grouping all the lock_acquired events based on the mode, and counting them by the lock mode. I just viewed the histogram in SSMS — behind the scenes I’m sure there was some XQuery running to read the trace data, but all the grouping / counting was done by the histogram target itself.

      Hope this helps!

      • Meher S MMalakapalli
        October 16, 2017 9:39 am

        Oh ok, Got it. You did the Grouping. Excellent, now I know where to look. Thank you so much for providing the valuable informaton.

        • Nevermind, I got what I wanted. you had another blog post for the histogram query. I was able to deduce the lock modes taken using the query and the lock mode values.

          Excellent Blog post.

  • Wonderful post, thank you! I did not know that 5000 was at the lock level and not row, I just always assumed 5k rows would be safe!


Share a Comment

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

%d bloggers like this: