CODEWITHSUNDEEP
sql-serverasp.net-mvcentity-framework
Sepehr Estaki
Sepehr Estaki

Reputation: 351

Disable/prevent locking table in Entity Framework

I'm using ASP.NET MVC and Entity Framework 6 for my online store. I am saving the count of views at the first line of my product page with this code : 

vmProduct.product.seenCount = Convert.ToInt32(vmProduct.product.seenCount) + 1;

db.SaveChanges();

and in the list of products page sort the list by seenCount column.

But when my site have many requests, product table locked because of adding count to product and any other queries goes to wait in SQL Server, so my site not load for some minutes, and if I comment the code my problem solved.

Is there any way to disable or prevent of locking product table when adding count to it? or what is the solution here?

Upvotes: 1

Views: 2386

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

One approach to improve concurrency is by enabling the READ_COMMITTED_SNAPSHOT database option so that SQL Server uses row versioning instead of locking to provide integrity in the READ COMMITTED isolation level. Row versioning improves concurrency but at the cost of additional tempdb usage (for the row version store) and database space (14 bytes per row for row version). See https://technet.microsoft.com/en-us/library/ms188277.aspx.

Instead of hitting the database every time to get the latest sorted product list, consider caching the data and refresh the list periodically in the background. This will improve both database and application performance considerably.

Upvotes: 1

Related Questions