aspnet/Caching

SQL Server deadlocks with Microsoft.Extensions.Caching.SqlServer

epignosisx opened this issue · 1 comments

epignosisx commented

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:

Caching/src/Microsoft.Extensions.Caching.SqlServer/SqlQueries.cs

Line 55 in c6a7f61

public const string DeleteExpiredCacheItemsFormat = "DELETE FROM {0} WHERE @UtcNow > ExpiresAtTime"; 

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 is to perform the deletes in batches. Ex:

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.