LINQ to SQL: Concurrency resolution

Question

Given this LINQ to SQL:

using (var db = Database.Context)
{
    var root = (from post in db.Post
                where post.Id == rootPostId
                select post).Single();

    root.LastActivityUtc = DateTime.UtcNow;

    db.SubmitChanges();
}

What will happen if the same record is concurrently being changed by another call to the same method (where this code lives) with the same rootPostId? Will an exception be thrown?

In such an event--concurrency conflict--I'd like to handle it by simple discarding the change so that just one update to LastActivityUtc is submitted instead of both, which will probably have the same value anyway.

Answer 1

This seems like a good candidate for a stored procedure. Rather than select/update, create a simple stored procedure that executes the following:

UPDATE Post SET LastActivityUtc = GETUTCDATE() WHERE Id=@id

Pass the id to the stored procedure and call it whenever you want to update the last activity of the post.

Answer 2

You can detect and resolve your concurrency issues, by catching a ChangeConflictException:

using (var db = new MyDataContext())
{
    var root = (from post in db.Post
                where post.Id == rootPostId
                select post).Single();

    root.LastActivityUtc = DateTime.UtcNow;

    try
    {
        db.SubmitChanges();
    }
    catch (ChangeConflictException)
    {
        db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
        db.SubmitChanges();
    }
}

With RefreshMode.KeepChanges you will keep all changes of your client objects, and the changes from other users on other fields will be merged.

Recommended articles:

• Optimistic Concurrency Overview
• LINQ To SQL Samples - Optimistic Concurrency
• Resolve Concurrency Conflicts by Merging with Database Values (LINQ to SQL)