Handling LINQ Concurrency Issues?

Question

I'm writing a program that can be used by multiple users and concurrency is a big concern of mine. I wrote the program and didn't even know about the subject, and now that I'm in multi-user testing, things are starting to blow up.

I want to be able to re-try the action if it fails, but don't know how. My code shows a message box if there is an error, but what I'd really like to do is have it wait for a second and trigger the Button.Click() event again.

How can I accomplish this?

Button.Click()
{
    var Result = 
        from a in DB.Table
        where a.Id == 1
        select new {Row = a}.Single();

    Result.Row.Value = "hulkSMASH!!!";

    try
    {
        DB.SubmitChanges();
    }
    catch
    {
        MessageBox.Show("Action failed");
    }
}

Answer 1

There's no built-in "LINQ way" of doing a retry, if that's what you're asking.

We use LINQ all of the time and don't run into concurrency issues like the ones you're describing. That's because we put a significant amount of time and effort into the design of the tables, views, and indexes, and transactions in our database. It sounds to me like instead of trying to provide for retries when you time-out or deadlock, you should instead be focusing your efforts on resolving these timeouts and deadlocks.

I suggest by starting with running a SQL Server Profiler trace against your database and seeing what types of queries are being run. If you see a OLTP type query which takes more than, say, 100ms, you probably should investigate and understand why that is. Verify that your indexes are proper. Understand the queries and the plans that they generate. Get your queries performing well and you won't have to worry about retries 99.999% of the time.

Answer 2

You should read:

• How to: Manage Change Conflicts (LINQ to SQL)
• Optimistic Concurrency Overview (LINQ to SQL)

There are a bunch of links on those pages that should point you in the right direction. For example, the How to: Resolve Concurrency Conflicts by Overwriting Database Values link shows the following example code where you are in fact trying to submit the changes again in case it fails:

try
{
    db.SubmitChanges(ConflictMode.ContinueOnConflict);
}

catch (ChangeConflictException e)
{
    Console.WriteLine(e.Message);
    foreach (ObjectChangeConflict occ in db.ChangeConflicts)
    {
        //No database values are merged into current.
        occ.Resolve(RefreshMode.KeepCurrentValues);
    }
}

Take a look at the other links for more options, such as retaining database values or merging them.