Posted on 2 Comments

Why Indexes Reduce Locks for Update and Delete Queries

I have a cat. Well, she’s Jeremiah’s cat, really, and you’ve even seen her: she’s in the logo of this website! She supervises my work most days at home from a cat bed in my office. I’d love to hug her all the time. But kitty has claws, and she makes it clear that she’s not a fan of hugs. She’d rather be free.

If you have blocking on your SQL Server, your queries would probably rather be free, too.

Indexes can help with that for all sorts of reasons. One of those reasons is that indexes can help your update and delete statements lock fewer rows. And I’m not only talking about shared locks, either.

Good indexing can reduce the number of modification locks your update and delete queries acquire

You can see this if you create a trace and look at locks_acquired for the following two queries against a test instance. Set a filter for the session_id you’re using to run the query, and add sql_text so you can see which query required which locks.

USE WideWorldImporters;
GO

/* This will do an index seek on the nonclustered index [FK_Sales_Orders_CustomerID] */
BEGIN TRAN
UPDATE Sales.Orders
SET InternalComments = 'Hiya'
WHERE CustomerID = 2;
ROLLBACK
GO

/* This will do an index scan on the forced index */
BEGIN TRAN
UPDATE Sales.Orders
SET InternalComments = 'Hiya'
FROM Sales.Orders WITH (INDEX ([PK_Sales_Orders]))
WHERE CustomerID = 2;
ROLLBACK
GO

The first query can go straight to the data that it’s going to update using an existing nonclustered index on CustomerID.

The second query uses an index hint to force SQL Server to use the clustered index of the table. This simulates what would happen if we didn’t have a viable nonclustered index to find the rows quickly.

How many locks do they take out?

Well, they take out a lot of locks. Locks are very granular, and if you do trace this you’ll find you get a lot of rows. After I traced this, I grouped the results by the mode column (lock mode), and the sql_text of the query to quickly see how many locks of which type each query took out.

By “grouped”, I mean I opened the XEvents trace results in SSMS and used the Grouping function in the GUI. It was fast and easy with this amount of rows, but if it was a larger table SSMS would probably have fallen over crying.

Both queries updated the same amount of rows (165). That would sure be weird if they didn’t!

  • The query that did the NC index seek took out 330 update key locks and 165 exclusive key locks.
  • The query that did the clustered index scan took out 104,184 update key locks and 165 exclusive key locks.

They also take out lots of other types of locks, but it’s the update locks I want to talk about here.

Whoa, more than 104K update key locks?

Yep. It is not a coincidence that there are 104,184 rows in this table. (If you see it take out more update key locks than rows in the table, some are probably from metadata objects. The simple grouping on just mode and sql_text isn’t perfect.)

This isn’t quite as terrible as it might sound because update locks are compatible with shared locks. However, update locks are NOT compatible with other update locks, or intent exclusive locks, or exclusive locks. (When in doubt about things like this, I like to check the official Lock Compatibility Matrix.)

This is one of the reasons that an OLTP database may work well in the early days, when it’s small and has a few users, but blocking can really shoot up when the data grows and our user counts grow.

Update locks matter for deletes, too

Oddly enough, an update lock isn’t just for updates. The name is weird.

If you run deletes and a trace for CustomerID=2 you will see something similar with lots of update locks, because deletes also use update locks.

Update locks are a mechanism that SQL Server uses in the background to keep your database from exploding with deadlocks when you have multiple sessions modifying the same table at once. Sometimes blocking is better than having your queries getting killed off!

More reading

Kalen Delaney wrote more details about how update locks help avoid deadlocks, and shows an example of measuring them using the sys.dm_tran_locks DMV here.

Thanks to Mike, who asked what was up with this

Mike was curious about the answers to one of the quiz questions in Troubleshooting Blocking and Deadlocks for Beginners, and wrote in with a great question.

I think this behavior is far from obvious, and I don’t have sample code or details in the course explaining this, so I’m going to link to this post from the course to help make this more clear for others.

Thanks, Mike!

Posted on 2 Comments

2 thoughts on “Why Indexes Reduce Locks for Update and Delete Queries

  1. Hi Kendra, great post, I’d often wondered if that was the case.

    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?

    1. I was just thinking I wanted to write a post on lock escalation. So here is an IOU to answer your question next Tuesday morning!

      Update: lock escalation post will be a little later than next Tues but I promise it’s coming. I got busy working on getting the new SSMS Secrets & Shortcuts course this week and haven’t had time to give the post the right amount of attention yet.

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.