SQL Server deadlock with Microsoft.Extensions.Caching.SqlServer
- Dominant language
- C#
- Stars
- 38.4k
- Forks
- 10.9k
- Avg merge
- 2d 6h
- Merged PRs (30d)
- 290
Description
We've been running into SQL Server deadlocks when using `Microsoft.Extensions.Caching.SqlServer` for Session State.
We went through this before in our ASP.NET apps. It comes down to this query:
https://github.com/aspnet/Caching/blob/c6a7f611fb537cbc58d9ac3bbe0b61fabe79ae8a/src/Microsoft.Extensions.Caching.SqlServer/SqlQueries.cs#L55
```cs
public const string DeleteExpiredCacheItemsFormat = "DELETE FROM {0} WHERE @UtcNow > ExpiresAtTime";
```
This query can easily escalate to a table lock. To reduce the potential of table locks the [recommendation](https://support.microsoft.com/en-us/help/323630/how-to-resolve-blocking-problems-that-are-caused-by-lock-escalation-in) is to perform the deletes in batches. Ex:
```sql
SET ROWCOUNT 50
delete_more:
DELETE FROM {0} WHERE @UtcNow > ExpiresAtTime
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
```
In ASP.NET apps this was very easy to change because these were stored procedures we could change ourselves. In ASP.NET Core the queries are in code and in classes that are not extensible (internal classes).
Potential for deadlocks is more common in ASP.NET Core than in ASP.NET apps because in ASP.NET apps the delete sessions query used to be a SQL Job performed by just one server, whereas in ASP.NET Core if we have 10 instances of the app running it will be 10 DELETE queries hitting the DB.
So my ask is that you make the queries easy to change. In the current state, we pretty much have to reimplement the `Microsoft.Extensions.Caching.SqlServer` if we want to change the queries. Maybe the queries can be overriden via `SqlServerCacheOptions` or at least just this one clean up query.
Contributor guide
Assessment
This issue has not been assessed yet.