6

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;
  • Frame challenge: Your description of things makes it seem like you're using this table to hold line items of incomplete transactions, probably from a web frontend. If so, would storing the item list in the session information, or in a temp table, work? This would turn the system into a "bulk insert", in the sense that your line-item table is only inserted to once per transaction. If you're only writing the view to get "current cart" totals, note that having the view won't actually solve certain cases of concurrency issues, since by the time the result is reported it's already stale. – Clockwork-Muse Dec 23 '19 at 17:56
  • Your deduction is close. I am holding incomplete transactions but the data is coming from many android devices, not a web front-end. There is a business requirement to have the incomplete/in progress data visible, and the device connections cannot be guaranteed, so saving data in sessions doesn't work. The header table has start/end dates, and I did try to limit the indexed view to only include records where an end was set. This had the same problems as the view over the details only. It makes sense, as this issue only occurs when the total record is first inserted, not updated. – ScorpionJL Dec 26 '19 at 15:28
  • @Clockwork-Muse oh, and thanks for the suggestion. – ScorpionJL Dec 26 '19 at 15:30
7

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.

|improve this answer|||||
  • That's kinda what I thought. I am a little confused as to the need to use serializable and range locks when the engine knows the key to be updated is unique. Of course, I also have more knowledge of the data than the engine. In my scenario, there will never be concurrent updates on the detail table for the same header id group. The data is coming in from thousands of individual devices and each device uploads the details synchronously to their own ID. – ScorpionJL Dec 23 '19 at 14:23
  • @ScorpionJL As I say in the answer, the engine also needs to lock the absence of a row. That is only possible in SQL Server under serializable isolation. If you have more information, please put it in the question so everyone can benefit. Or ask a separate follow-up. – Paul White 9 Dec 23 '19 at 14:27
  • I think I can make this work with triggers based on my data pattern. As you said, it's It's just going to be more involved to handle the edge cases. Thanks for the confirmation and the options. – ScorpionJL Dec 23 '19 at 14:31
  • @ScorpionJL No worries. Personally I think writing an indexed view with triggers is something everyone should do once. It is a great learning experience, especially when the concurrency ramps up. It's not trivial at all. – Paul White 9 Dec 23 '19 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.