Locks Taken During Indexed View Modifications

Last Updated 5 years ago

Locking, Blocking, and Isolation Levels, SQL Server

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.

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

USE StackOverflow2010;

CREATE OR ALTER VIEW dbo.UserPostScore

WITH SCHEMABINDING

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;

CREATE UNIQUE CLUSTERED INDEX cx_ups ON dbo.UserPostScore (Id);

BEGIN TRAN

UPDATE u

SET u.Reputation += 100

FROM dbo.Users AS u

WHERE u.Id BETWEEN 22656 AND 25656;

ROLLBACK

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.

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;

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.

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

CREATE OR ALTER VIEW dbo.UserPostScore

WITH SCHEMABINDING

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;

CREATE UNIQUE CLUSTERED INDEX cx_ups ON dbo.UserPostScore (Id);

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:

SELECT COUNT(*)
FROM dbo.UserPostScore AS ups
WHERE ups.Id < 22656;

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.

Single-Column-Key Missing Index Recommendations are Usually Wrong.

It’s Okay If You Don’t Create Statistics.

8 Comments. Leave new

Kendra Little
September 6, 2018 2:20 am

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!

Reply
- Erik Darling
  September 6, 2018 5:49 am
  
  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 🙂
  
  Reply
Range Locks On Multi-Table Indexed Views – Curated SQL
September 6, 2018 7:10 am

[…] Erik Darling looks at the kinds of locks taken when updating an indexed view: […]

Reply
Michael J Swart
September 6, 2018 2:19 pm

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.

Reply
- Erik Darling
  September 6, 2018 3:49 pm
  
  I hear if you create two separate indexed views and then join them together in a view that solves everything.
  
  Reply
  - Douglas Holt
    September 10, 2018 2:26 pm
    
    That sounds like a terriworse idea.
    
    Reply
Attentes SQL Server – SQL Server et Oracle
November 5, 2018 5:25 am

[…] Brent/a> M$/a> […]

Reply
Indexed View Maintenance Is Only As Bad As Your Indexes | Erik Darling Data
July 7, 2020 4:49 pm

[…] 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 […]

Reply