Managing Conflicts in LINQ to SQL

There are a couple of gotchas when managing concurrency issues with LINQ to SQL. First, when you catch a ChangeConflictException and allow the other user’s changes to win by calling ResolveAll on the DataContext’s ChangeConflicts property and passing RefreshMode.OverwriteCurrentValues, other non-conflictual changes will not be persisted because LINQ to SQL will roll back the transaction it started whenever there is an exception. To avoid this behavior, you need to wrap the call to SubmitChanges inside a “using” block that creates a new TransactionScope. Then right before the ending brace, you need to call Complete on the TransactionScope, which will ensure that the changes you tried to persist when calling SubmitChanges will in fact commit, even when you catch a ChangeConflictException.

// Persist non-conflict changes even when catching an exception
using (TransactionScope txScope = new TransactionScope())
{
    try
    {
        db.SubmitChanges
            (ConflictMode.ContinueOnConflict);
    }
    catch (ChangeConflictException)
    {
        Console.WriteLine("Re-submit changes? {Y/N}");
        ConsoleKey key = Console.ReadKey().Key;
        if (key == ConsoleKey.Y)
        {
            //Keep local data
            //db.ChangeConflicts.ResolveAll
                //(RefreshMode.KeepCurrentValues);
            foreach (ObjectChangeConflict conflict
                in db.ChangeConflicts)
            {
                conflict.Resolve
                    (RefreshMode.KeepCurrentValues);
            }
            // Overwrite other users' changes
            db.SubmitChanges();
        }
        else
        {
            // Refresh local data from the database
            db.ChangeConflicts.ResolveAll
                (RefreshMode.OverwriteCurrentValues);
        }
    }

    // Commit transaction
    txScope.Complete();
}

As you can see from this code, there are two ways you can resolve a change conflict. You can allow the other user’s changes to overwrite your own (shown in the ‘else’ block), or you can keep your current values and attempt to re-submit your changes to the database (as shown in the ‘if’ block), overwriting the other user’s changes with your own. As indicated, there is a problem lurking here. The ResolveAll method has a bug that refreshes database values, not just for the change conflicts, but for all updates in the change set, which results in an InvalidOperationException when you try to invoke SubmitChanges. The exception states that the timestamp property (which you are using for optimistic concurrency checking) is a computed value and cannot be changed. The answer is to iterate each change conflict and call Resolve on them individually. Thus, we avoid refreshing items for which there is not a change conflict, because those changes were applied the first time we called SubmitChanges.  You can download the full VS project here.

About Tony Sneed

Married with three children.
This entry was posted in Technical. Bookmark the permalink.

One Response to Managing Conflicts in LINQ to SQL

  1. Nick says:

    Your catch is executed one time – so if a seocond update happens then the method will still fail to update.

    What about using doWhile with a completed flag or even a loop count so you retry – thoughts?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s