How do I concurrently increment a page view count using linq-to-sql?

Question

I have an ASP.NET MVC 3 Web Application using Linq-to-SQL for my data access layer. I'm trying to increment a Views field every time the Details action is called, but I'm receiving a "Row not found or changed" error on db.SubmitChanges() if two people happen to hit the action at the same time.

public ActionResult Details(int id)
{
    DataClassesDataContext db = new DataClassesDataContext();

    var idea = db.Ideas.Where(i => i.IdeaPK == id).Single();

    idea.Views++;

    db.SubmitChanges();

    return View(new IdeaViewModel(idea));
}

I could set the UpdateCheck of the Views field to "Never" in my .dbml (Data Model), which would get rid of the error, but then the idea record could be updated twice with the same Views count. i.e.

First instance of Details action gets idea record with Views count of 1.
Second instance of Details action gets idea record with Views count of 1.
First instance increments Views to 2
First instance commits
Second instance increments Views to 2
Second instance commits

Result: Views field is 2 
Expected Result: Views field should be 3

I looked into using a TransactionScope, but I got the following deadlock error from one of the two calls:

Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

when I updated my action to look like:

public ActionResult Details(int id)
{
    DataClassesDataContext db = new DataClassesDataContext();

    using (var transaction = new TransactionScope()){
        var idea = db.Ideas.Where(i => i.IdeaPK == id).Single();

        idea.Views++;

        db.SubmitChanges();

        return View(new IdeaViewModel(idea));
    }
}

I also tried increasing the TransactionScope timeout using the TransactionScopeOptions and that didn't seem to help (but I may have to set it elsewhere as well). I could probably solve this example by doing the increment in a single SQL command using db.ExecuteQuery, but I was trying to figure out how to make this work so I'll know what to do in more complex scenarios (where I want to execute multiple commands in a single transaction).

Answer 1

I think you should make a stored procedure which will atomically increment the field you want and call it through LINQ2SQL.

Other option is to wrap your operation into a transaction with appropriate isolation level.

Answer 2

You should not need transactions or stored procedures. Just use DataContext.ExecuteCommand:

db.ExecuteCommand("UPDATE Ideas SET Views = Views + 1 WHERE IdeaPK = {0}", id);

This will execute it as one SQL statement, and is thus atomic.