Why Indexes Reduce Locks for Update and Delete Queries

Indexes help queries run faster in SQL Server for several 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!