Deadlock when storing Asp.net sessions in SQL server during peak load

When there is more than 1 server in web farm scenario it is an obvious choice to go for session out-of-proc either with SQL server or State server. While storing session with SQLServer if you see issues with SQL deadlock during peak loads involving DeleteExpiredSessions . It goes like this…

Looking at the current implementation of the DeleteExpiredSessions stored procedure:

CREATE PROCEDURE DeleteExpiredSessions
AS
DECLARE @now DATETIME
SET @now = GETUTCDATE()
DELETE ASPState.dbo.ASPStateTempSessions
WHERE Expires < @now
RETURN 0

GO

This would take the locks on all the expired records for deletion and these locks may be promoted to page locks. This can give rise to deadlocks with other ‘session state write statements’ when the number of records marked for deletion increases. By default this stored procedure is supposed to run every minute.

For Example: In one of the case. Looking at no. of records which are marked for deletion

Select count (*) from ASPState.dbo.ASPStateTempSessions WHERE Expires < getdate()

The number of record marked for expiry was 138,000 out of 146,000 records and the size of “ASPStateTempSessions” table was crossing ~20 GB!!

Next step was to check the session timeout value which can play a vital role in mitigating the problem.

If we have timeout value set to a high value (Which in my case was set at: 360 min) then no matter how many times “DeleteExpiredSessions” runs, it won't be able to delete records resulting in a pile-up and hence deadlock.

In a different case I found the session timeout value was set to 60 mins and we could not reduce to default 20 mins due to business requirement .Then we suggested to change the “DeleteExpiredSessions” stored procedure implementation as mentioned in the blog. (Tweaking is not recommended as you would run out of support boundaries. )

Another option is to reduce amount of data stored in Session (sessions are meant to store small things like ID’s) and lastly increase the number of servers in SQL environment to handle high load that can help in reducing chances of deadlock.

In ASP.NET 4.0 the “DeleteExpiredSessions” stored procedure has already been modified using the same approach so that it does not hold any locks on table level and we delete the records individually.

Also it introduces a new compression option for both the “out-of-process session-state” providers. When the “CompressionEnabled” configuration option is set to true, ASP.NET will compress (and decompress) serialized session state by using the .NET Framework “System.IO.Compression.GZipStream” class and that would reduce the table size.

<UPDATE>

KB 973849  fix for ASP.NET 2.0 is out :)

</UPDATE>

Till then bye bye…