Alternatives to LINQ To SQL on high loaded pages

Question

To begin with, I LOVE LINQ TO SQL. It's so much easier to use than direct querying.

But, there's one great problem: it doesn't work well on high loaded requests. I have some actions in my ASP.NET MVC project, that are called hundreds times every minute.

I used to have LINQ to SQL there, but since the amount of requests is gigantic, LINQ TO SQL almost always returned "Row not found or changed" or "X of X updates failed". And it's understandable. For instance, I have to increase some value by one with every request.

var stat = DB.Stats.First();
stat.Visits++;
// ....
DB.SubmitChanges();

But while ASP.NET was working on those //... instructions, the stats.Visits value stored in the table got changed.

I found a solution, I created a stored procedure

UPDATE Stats SET Visits=Visits+1

It works well.

Unfortunately now I'm getting more and more moments like that. And it sucks to create stored procedures for all cases.

So my question is, how to solve this problem? Are there any alternatives that can work here?

I hear that Stackoverflow works with LINQ to SQL. And it's more loaded than my site.

Answer 1

This isn't exactly a problem with Linq to SQL, per se, it's an expected result with optimistic concurrency, which Linq to SQL uses by default.

Optimistic concurrency means that when you update a record, you check the current version in the database against the copy that was originally retrieved before making any offline updates; if they don't match, report a concurrency violation ("row not found or changed").

There's a more detailed explanation of this here. There's also a fairly sizable guide on handling concurrency errors. Typically the solution involves simply catching ChangeConflictException and picking a resolution, such as:

try
{
    // Make changes
    db.SubmitChanges();
}
catch (ChangeConflictException)
{
    foreach (var conflict in db.ChangeConflicts)
    {
        conflict.Resolve(RefreshMode.KeepCurrentValues);
    }
}

The above version will overwrite whatever is in the database with the current values, regardless of what other changes were made. For other possibilities, see the RefreshMode enumeration.

Your other option is to disable optimistic concurrency entirely for fields that you expect might be updated. You do this by setting the UpdateCheck option to UpdateCheck.Never. This has to be done at the field level; you can't do it at the entity level or globally at the context level.

Maybe I should also mention that you haven't picked a very good design for the specific problem you're trying to solve. Incrementing a "counter" by repeatedly updating a single column of a single row is not a very good/appropriate use of a relational database. What you should be doing is actually maintaining a history table - such as Visits - and if you really need to denormalize the count, implement that with a trigger in the database itself. Trying to implement a site counter at the application level without any data to back it up is just asking for trouble.

Use your application to put actual data in your database, and let the database handle aggregates - that's one of the things databases are good at.