Partager via

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

  • Article

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…

Comments

  • Anonymous
    August 27, 2009
    Well, why do we have to store sessions in the database anyway? for every user we will have to fetch sessions form DB? won't it make the application really slow, as a trip to DB is very expensive? not to mention that if we are storing the sessions in the database, we have a single point of failure in our application? what will happen in case of distributed environment? Why not go for a  session caching solution especially one for distributed environment, so that we can cache them and fetch them from memory instead of from DB? I think there are a few caching solutions available which also provide item expiration....

  • Anonymous
    November 20, 2009
    This is great. Thanks JAS, you Rock. :)

  • Anonymous
    July 19, 2010
    @Jim this is for load balanced environment, there db based sessions are the fastest when it comes to web farms.