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;
