Search code examples
sql-serverasp.net-mvciisweb-farm

What is life cycle of SQL session state?

I searched about SQL session state life cycle and how it work but i didn't found any website explain it. All I found is how to configure it.

What I have tried: I made web farm with two server and apply SQL session on it, but i don't know how SQL session work? Does every request from user need to read his session id from db?
How does .Net events handle SQL session? Is SQL session saved temporary in memory?

Solution

  • I searched about SQL session ,

    The SQL session is concurrency, and here is Explanation about how SQL session work: https://msdn.microsoft.com/en-us/library/aa478952.aspx

    To test concurrency ,I made MVC application use SQL session, the application contain two controller: Home, Account. I applied two trial to test concurrency:

    Trial 1: Two controller access SQL session.

    • Made Home controller contain action write to SQL session then sleep 30 seconds as shown: enter image description here

    • Made Account controller contain action read from SQL session as shown: enter image description here

    • Start the application and open two tab: First tab for home and second tab for account. When I open second tab it opened! And not waiting for first tab to end his request, and ViewBag.Name have null value; this is occurs because no SQL session saved on DB until now.

    • After the home control web request end the session state saved on DB, then I refresh tab1 then tab2 and notes that tab 2 waiting for tab 1 to end his web request.

    Trial 2: One controller access to session.

    • Use Home Controller code on trial 1.

    • Modify the account controller and remove the read code from session as shown: enter image description here

    • I open two tabs: tab 1 for home and tab 2 for account; tab 2 wait for tab 1 to end his web request.

    • To make tab 2 not wait for tab 1, I make account controller session state disabled as shown: enter image description here

    For read write SQL session ,ASP.NET worker process stores the objects that belong to the client session collection in SQL Server at the end of each Web request.

    I use SQL profiler tool to trace when read/write on SQL session .