patternsqlModerate
SQL Server - Is there a way to prevent shared range locks to 'infinity' when using an indexed view?
Viewed 0 times
preventsharedinfinitysqlrangewayviewindexedusingwhen
Problem
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
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
Solution
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
For interest, the hints added to the read of the indexed view are:
The
In your example, the engine also adds hints to the read of the parent table to validate the foreign key relationship:
The
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.
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.
Context
StackExchange Database Administrators Q#256149, answer score: 10
Revisions (0)
No revisions yet.