Frankenblog
This post has been nagging at me for a while, because I had seen it hinted about in several other places, but never written about beyond passing comments.
A long while back, Conor Cunningham wrote:
This same condition applies to indexed view maintenance, but I’ll save that for another day :).
AFAIK he hasn’t written about it or typed an emoji since then.
There’s also a passing comment from Paul White in this Stack Exchange answer:
Range locks taken when maintaining an indexed view referencing more than one table.
Just what are Range locks? Great question!

So what causes Range Locks? Just ask Sunil. He knows everything (this assumes the serializable isolation level):
Equality Predicate
If the key value exists, then the range lock is only taken if the index is non-unique. In the non-unique index case, the ‘range’ lock is taken on the requested key and on the ‘next’ key.
If the ‘next’ key does not exist, then a range lock is taken on the ‘infinity’ value. If the index is unique then a regular S lock on the key.
If the key does not exist, then the ‘range’ lock is taken on the ‘next’ key both for unique and non-unique index.
If the ‘next’ key does not exist, then a range lock is taken on the ‘infinity’ value.
Range Predicate (key between the two values)
‘range lock on all the key values in the range when using ‘between’
‘range’ lock on the ‘next’ key that is outside the range. This is true both for unique and non-unique indexes. This is to ensure that no row can be inserted between the requested key and the one after that. If the ‘next’ key does not exist, then a range lock is taken on the ‘infinity’ value.
First, Testing an Indexed View with One Table
I’m going to use the small version of Stack Overflow for expediency. Here’s a test setup and update for an indexed view with just one table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
USE StackOverflow2010; GO CREATE OR ALTER VIEW dbo.UserPostScore WITH SCHEMABINDING AS SELECT u.Id, u.DisplayName, SUM(CONVERT(BIGINT, u.Reputation)) AS TotalRep, COUNT_BIG(*) AS ForSomeReason FROM dbo.Users AS u WHERE u.Reputation > 1000 GROUP BY u.Id, u.DisplayName; GO CREATE UNIQUE CLUSTERED INDEX cx_ups ON dbo.UserPostScore (Id); GO BEGIN TRAN UPDATE u SET u.Reputation += 100 FROM dbo.Users AS u WHERE u.Id BETWEEN 22656 AND 25656; ROLLBACK GO |
After that runs, I’m going to use this query to see what locks are held. I know it’s ugly.
But most DMV queries are.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT dtl.request_mode, CASE dtl.resource_type WHEN 'OBJECT' THEN OBJECT_NAME(dtl.resource_associated_entity_id) ELSE OBJECT_NAME(p.object_id) END AS locked_object, dtl.resource_type, COUNT_BIG(*) AS total_locks FROM sys.dm_tran_locks AS dtl LEFT JOIN sys.partitions AS p ON p.hobt_id = dtl.resource_associated_entity_id WHERE dtl.request_session_id = 54 AND dtl.resource_type <> 'DATABASE' GROUP BY CASE dtl.resource_type WHEN 'OBJECT' THEN OBJECT_NAME(dtl.resource_associated_entity_id) ELSE OBJECT_NAME(p.object_id) END, dtl.resource_type, dtl.request_mode; |
Here’s what shows up for me:

Some rather expected locks, I think. Exclusive and Intent Exclusive for the indexed view and the Users table.
But what if we change the indexed view?
Double Down
I’m going to join Users to Posts here, to fulfill the prophecy, then run my update.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE OR ALTER VIEW dbo.UserPostScore WITH SCHEMABINDING AS SELECT u.Id, u.DisplayName, SUM(CONVERT(BIGINT, u.Reputation)) AS TotalRep, SUM(p.Score) AS TotalScore, COUNT_BIG(*) AS ForSomeReason FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId WHERE u.Reputation > 1000 GROUP BY u.Id, u.DisplayName; GO CREATE UNIQUE CLUSTERED INDEX cx_ups ON dbo.UserPostScore (Id); GO BEGIN TRAN UPDATE u SET u.Reputation += 100 FROM dbo.Users AS u WHERE u.Id BETWEEN 22656 AND 25656; ROLLBACK |
Now when I look at the locks, I see something new!

My indexed view has exclusive range locks taken out on it.
There’s nothing you can do about this either. Beyond normal lock escalation, the isolation level has been escalated to Serializable.
If I try to query the view, I’ll get blocked unless I add a where clause to specifically avoid the locked range of keys, like this:
1 2 3 |
SELECT COUNT(*) FROM dbo.UserPostScore AS ups WHERE ups.Id < 22656; |
What’s The Point?
There’s no such thing as a free index, and that applies to indexed views as well.
To learn more about how to interpret indexed view maintenance, check out this post by Paul White.
Thanks for reading!
Brent says: I’ve always been a little nervous doing indexed views across multiple tables, but now I’m even more hesitant. It’s the kind of trick that’s absolutely amazing for selects when it works, but completely terribad for concurrency.
8 Comments. Leave new
Erik, you are awesome. Figuring out that little note in Conor’s post has been on my mental backlog ever since I started presenting on indexed views, and I never got off my butt to track it down. Thanks for digging in!
Aw, thank you Kendra. I went back and tested your post about the locks taken when one is created with multiple tables (instead of just the one), but didn’t notice any difference. At least creation is still safe 🙂
[…] Erik Darling looks at the kinds of locks taken when updating an indexed view: […]
I’ve encountered four separate performance/deadlock/blocking issues in the past two weeks. All of these issues have indexed views in common (different indexed views for each issue).
Last week I removed an indexed view and really felt like I earned my salary.
“but completely terribad for concurrency” <– true words.
I hear if you create two separate indexed views and then join them together in a view that solves everything.
That sounds like a terriworse idea.
[…] Brent/a> M$/a> […]
[…] mean that, in some cases locks are serializable, and that if you don’t mind your indexes you may find run-of-the-mill modifications taking […]