ASP.NET | Entity Framework, race condition

Question

I have a web application where users are allowed to like items. Each item have a property of the total likes he got.

The issue happens when a lot of users like the same item at the same time, then I am receiving incorrect values in SQL (caused by race condition).

As a temporary solution, I have created a worker thread in the controller constructor that works against a queue, when an item like/dislike request is receiving, I am queuing this request. The worker thread is dequeing the values and updating a dictionary that maps itemid to totalcount.

The worker thread then updates the database once every one minute with the result.

Side question: does context.SaveChanges() save only what changed in an object? Or does it save all of the properties of the objects?

I have a feeling that this solution is not the right one, what is the best way to deal with such an issue?

You should update the database using a syncronized isolation level (in sql server its called Serializable). However you do not mention your provider so it might not be possible (ie. if you are using Ms Access). — Igor, Aug 04 '16 at 16:03
`Does context.savechanges saves only what changed in an object? or it saves all of the properties of the objects` - it depends. If you set the objects state to modified everything is saved because the whole object is perceived as dirty. If you get the existing object from the context and update one or more properties and then save only what you changed is saved back to the databatabase — Igor, Aug 04 '16 at 16:06
Hey Igor! what do you mean when saying synchronized isolation level? how do i define one? i am using Sql database (hosted in azure) — Hay Zohar, Aug 04 '16 at 16:06
See this [previous so answer](http://stackoverflow.com/a/12795274/1260204) and check out the link for IsolationLevel. You can wrap your entity framework creation/call in an ambiant transaction using isolation level serializable. What you should do though is use a stored procedure if you are dealing with increment updates (existing likes +1) and handle the update in the stored proc and that update should be also wrapped in a begin transaction. — Igor, Aug 04 '16 at 16:08
Is there any other way to handle this issue without changing the isolation level? i know that changing the isolation level will slow down the application so i am trying to avoid that, Hey Win, do you mean the Item class? what part do you need to see? — Hay Zohar, Aug 04 '16 at 16:19
@user6079392 yes. For example, create a many-many table called `ItemLikes` and insert a row each time a `User` likes an item. Instead of having "number of likes" be a property on the item, you would count how many people like it. — Eric Hotinger, Aug 04 '16 at 16:45
Hey Eric Hotinger, but than when a user query for a list of items, i will than have to go through each item in the list (avg list size is 100) and query for the number of likes this item has, — Hay Zohar, Aug 04 '16 at 16:49
@user6079392 - correct but with a good PK/FK relation and a proper index this is still very fast. — Igor, Aug 04 '16 at 16:50

Александр Алькевич · Accepted Answer · 2016-08-04T17:20:04.540

You can use RepeatableRead transaction scope witch locks data when read and write occurs:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead }))
{
    ...
    context.SaveChanges();
    scope.Complete();
}

Entity Framework track entity fields change and generates sql code to update only those that have been modified. You may explicity specify that you want to update only one entity property and ignore other changes:

context.Entry(entity).State = EntityState.Unchanged;
context.Entry(entity)
        .Property(c => c.entityField).IsModified = true;

Also it can be useful when you attach an object into context:

context.Attach(entity);
context.Entry(entity)
        .Property(c => c.entityField).IsModified = true;

Because Attach puts entity and it's properties into the context in Unchanged state.

score 1 · Answer 2 · answered Aug 04 '16 at 17:19

for performance and concurrency issues is better that use ExecuteSqlCommand:

string sql = "UPDATE Table SET LikeCount = LikeCount+1 WHERE Id={0}";
using (var context = new BloggingContext()) 
{ 
    context.Database.ExecuteSqlCommand( sql, new SqlParameter("@Id", id)); 
}

ASP.NET | Entity Framework, race condition

2 Answers2