dotnet / dotnet/aspnetcore

SQL Server deadlock with Microsoft.Extensions.Caching.SqlServer

Open
#28,380 7 comments 0 reactions 0 assignees View on GitHub
area-middleware bug feature-caching
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.