Locks Taken During Indexed View Modifications


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!

Ranger Things

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.

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.

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.

Now when I look at the locks, I see something new!

Ranger Danger

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:

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.

Previous Post
Single-Column-Key Missing Index Recommendations are Usually Wrong.
Next Post
It’s Okay If You Don’t Create Statistics.

8 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.