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

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 开发者_JS百科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).

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.

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.

I would try capturing the Row not Found exception and triggering a retry of the whole operation. Requery your Views Row and update it and call submit changes again. Make sure you use a counter to ensure you only retry the operation five times or so, so that you are not caught in an infinite loop.

I strongly suggest that you look look at using a stored procedure as @Dmitry suggested and wrap your increment and select up into one operation. That will give you two benefits: 1) It will eliminate your contention issue and 2) it will put the entire operation into one call to the database. Here's the basic idea:

CREATE PROCEDURE spIdeasRetrieveAndLog
    @IdeaPK int
AS
BEGIN
    UPDATE Ideas SET Views = Views + 1 WHERE IdeaPK = @IdeaPK
    GO
    SELECT * FROM Ideas WHERE IdeaPK = @IdeaPK
END
GO

I would recommend you to use one of messaging frameworks (for example, NServiceBus, but there are other options - MassTransit, Rhino Service Bus). They will help you to solve this problem in very simple and elegant way.