Disable/prevent locking table in Entity Framework

Question

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?

Just because a few updates are happening, your table shouldn't be locked - SQL Server uses row-level locking as long as a given transaction doesn't have more than 5000 active locks..... — marc_s
– marc_s, Commented Nov 26, 2016 at 13:57

Dan Guzman · Accepted Answer · 2016-11-26 12:28:57Z

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.

Thank you for your answer, I will test READ_COMMITTED_SNAPSHOT some hours later and share the result here.
@SepehrEstaki it's been over 26,000 hours since you posted this comment. Can you share your results now?

Collectives™ on Stack Overflow

Disable/prevent locking table in Entity Framework

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related