# Sql-server – SQL Server – Is there a way to prevent shared range locks to ‘infinity’ when using an indexed view

lockingmaterialized-viewsql server

I have your basic header/details table (think orders and order details). The header table has an identity column as the clustered key, the detail has the header id and a line number column as the clustered key. The header id is an ever-incrementing identity value and the line number is also an incrementing value.

I was attempting to add an indexed view over the details to aggregate the data so we didn't have to do this in code or via triggers, which has its own set of concurrency issues in the existing system.

Everything looks and works fine until we started to load test it. It is expected there will be ~1500 details/sec (90,000/min) added to the table.

When a row is inserted into the detail table the indexed view is also updated. During the insert, it appears a shared range lock (RangeS-U) is taken on the indexed view. The range taken is the current key to the next key, similar to how locks would be taken under the serializable isolation level. The connection is setup under read committed. The bottleneck seems to occur when the 'next' key does not exist in the table. In this situation, the shared lock is taken to the 'infinity(ffffffff)' key.

This basically describes the behavior that I see but doesn't provide any workarounds.
https://www.brentozar.com/archive/2018/09/locks-taken-during-indexed-view-modifications/

Under the above load, the server just cannot keep up with the inserts, and things start to back up pretty fast. 500 out of 600 concurrent connections are blocked at a given time. It doesn’t seem an aggregate indexed view on an ever-increasing key can keep up with our concurrency requirements.

We are using SQL Server 2012 Standard Edition, and are upgrading to 2019 soon.

Is there any way to change this locking behavior on indexed views or is this a futile effort on my part, in which case I'll need to go down the road of code/trigger based aggregates, or am I missing something? If 2019 does not exhibit the same behavior that works for me as the database will be upgraded prior to the work being finished.

The scripts included represent the tables involved, but are obviously not the actual tables. The behavior is reproduceable using them.

Setup

if object_id(N'dbo.LockTest') is null
begin
create table dbo.LockTest
( LockTestID    int not null primary key
, LockTestValue int     null
);
insert into dbo.locktest values(1, 1), (2, 2), (3, 3), (7, 7), (8, 8);
end;

if object_id(N'dbo.LockTestDetails') is null
begin
create table dbo.LockTestDetails
( LockTestID  int not null
, LineNumber  int not null
, Val         int not null
, PRIMARY KEY(LockTestID, LineNumber)
, foreign key(LockTestID) references dbo.LockTest(LockTestID)
);
insert into dbo.LockTestDetails values(2, 1, 5), (2, 2, 4);
end

if object_id(N'dbo.LockTestTotals') is null
begin
exec sp_executesql N'
CREATE VIEW dbo.LockTestTotals with schemabinding as
SELECT d.LockTestID, Lines = COUNT_BIG(*), Val = SUM(Val)
FROM dbo.LockTestDetails d
GROUP BY d.LockTestID';
exec sp_executesql N'
create unique clustered index PK_LockTestTotals on dbo.LockTestTotals(LockTestID)';
end


Example 1

-- run in session 1.
-- range lock taken from 1 to the next key, 2.
begin transaction
insert into dbo.LockTestDetails values(1, 1, 1);
waitfor delay '00:00:20';
rollback

-- run in session 2
-- record is inserted.  not blocked by session 1 range lock.
-- range lock taken from 7 to next key, 'infinity(ffffffff)'
-- no other details can be added with an id higher than 7.
begin transaction
insert into dbo.LockTestDetails values(7, 1, 1);
waitfor delay '00:00:20';
rollback


Example 2

-- run in session 1.
-- range lock taken from 7 to next key, 'infinity(ffffffff)'
-- no other details can be added with an id higher than 7.
begin transaction
insert into dbo.LockTestDetails values(7, 1, 1);
waitfor delay '00:00:20';
rollback

-- run in session 2
-- record is blocked by session 1 range lock.
begin transaction
insert into dbo.LockTestDetails values(8, 1, 1);
waitfor delay '00:00:20';
rollback


Script to view locks

declare @session int = null;

select
l.request_session_id
, l.resource_type
, resource_description = rtrim(l.resource_description)
, [object_name] = CASE
WHEN resource_type = 'OBJECT'
THEN OBJECT_SCHEMA_NAME(l.resource_associated_entity_id) + '.' + OBJECT_NAME(l.resource_associated_entity_id)
ELSE OBJECT_SCHEMA_NAME(p.[OBJECT_ID]) + '.' + OBJECT_NAME(p.[object_id])
END
, index_name = i.[name]
, l.request_mode
, l.request_status
, l.resource_subtype
, l.resource_associated_entity_id
from sys.dm_tran_locks l
left join sys.partitions p
ON p.hobt_id = l.resource_associated_entity_id
LEFT JOIN sys.indexes i
ON i.[OBJECT_ID] = p.[OBJECT_ID]
AND i.index_id = p.index_id
where resource_database_id = db_id()
and request_session_id between  isnull(@session, 0) and isnull(@session, 5000)
and request_session_id <> @@spid
order by
[object_name]
, CASE
WHEN i.[name] is null then 0
WHEN LEFT(i.[name], 2) = 'PK' THEN 1
WHEN LEFT(i.[name], 2) = 'UK' THEN 2
ELSE 3 END
, index_name
, case resource_type
when 'DATABASE' then 0
when 'OBJECT' then 1
when 'PAGE' then 2
when 'KEY' then 3
when 'RID' then 4
else 99 end
, resource_description
, request_session_id;


There's nothing you can do about this. SQL Server automatically takes steps to ensure the indexed view always stays synchronized with the base tables.

When reading the indexed view to see if the data associated with the changed key(s) exists or not, SQL Server needs to ensure that data does not change until the view maintenance is complete. This includes the case where the key does not exist - it must continue to not exist until inserted. The engine meets this requirement by accessing the indexed view under serializable isolation. This local isolation escalation occurs regardless of the session's current isolation level.

For interest, the hints added to the read of the indexed view are:

UPDLOCK SERIALIZABLE DETECT-SNAPSHOT-CONFLICT


The DETECT-SNAPSHOT-CONFLICT hint directs SQL Server to check for write conflicts under snapshot isolation.

In your example, the engine also adds hints to the read of the parent table to validate the foreign key relationship:

READ-COMMITTEDLOCK FORCEDINDEX DETECT-SNAPSHOT-CONFLICT


The READ-COMMITTEDLOCK hint ensures shared locks are taken when running under read committed snapshot isolation.

These hints are required for correctness and cannot be disabled.

### Workarounds

You might think of making the clustered index descending instead of ascending, but this would introduce additional issues (for ascending inserts), and only moves the point of contention from the one end of the structure to the other.

If you try to write the same logic using triggers, or code outside of the database, you will end up missing an edge case (leading to an inaccurate summary) or using much the same hints SQL Server does. This sort of logic is notoriously difficult to get right first time, and requires extensive testing under high concurrency to validate. On the other hand, rough totals might be good enough in some cases.

If you can tolerate some latency, you could batch the inserts and apply them to the indexed view in bulk on a single session/thread. For example, by holding inserted rows in a staging area, then updating the base tables from in one insert statement from time to time. The meaning of 'bulk' here need not be terribly large, just enough to comfortably keep up with the expected peak workload. This will complicate error reporting.

Fundamentally, indexed views aren't well-suited to very rapid base table updates in general, and end-of-range inserts in particular.