why is the second DatabaseConflictException being thrown?

Question

I have some linq2sql stuff which updates some rows. Then when I submit I do this:

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

Now the second submit(the one in the catch) is throwing yet again a ChangeConflictException

How is that possible? And if it is possible. How would one need to do the query? (I can not put yet another try/catch around that one? When would I stop?)

I want only the changed values to be put in the database.

EDIT: let me rephrase the intent of the question: When I say 'ResolveALL(keepchanges)', I would think that I say: "I don't care..just use my values". Instead, it throws yet again the same exception.

I was surprised by this behaviour since the examples on MSDN don't have a second try catch around the second SubmitChanges

So how many times can these exceptions be thrown(As many times as there are columns?), and can I avoid them altogether somehow (after saying ResolveAll)?

EDIT: Last edit before I start the bounty:

I've made it into a neat loop as suggested by 1 of the commenters. But it doesn't matter how many times I retry. The moment it starts throwing exceptions, it will never do it without an exception! So either it works the first time, or it won't work at all.

Now my linq update has some 20 or 50 rows in it which need updating (I work with batches to speed things up). Is every resolveall only fixing one issue in 1 column ion 1 row? or is it smart enough to fix everything it encountered?

To recap: the values I just changed (only 1 or 2 columns) are the ones which need to go into the database no matter what. How can I do this using linq (or should I really resort to opening an SqlConnection for this? (If so why Linq in the first place?)

My code up till now:

 int retry;
 for (retry = 0; retry < 10; retry++)
 {
      try
      {
           database.SubmitChanges();
           //submit succeeded... break loop
           break;
      }
      catch (ChangeConflictException)
      {
           database.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);

           if (retry > 0)
           {
                Thread.Sleep(retry * 10);  //introduce some wait, to see if this helps
           }
      }

EDIT: Found it!

Thanks to the link to the blog in the accepted answer I now cycle through all the conflicts and log them to see what is causing this.

And I'm glad I did, since as it turned out that one of the DB fields contains a trigger which updates something else in certain conditions. So I could resolve as much as I liked, every time the trigger would fire again, causing the next conflict.

This trigger obviously I was not aware off, since my DB admin put it in place there to track something or the other. Triggers can be a great tool, but if you are not aware of them they can cause major headaches!

Answer 1

If this is something you need to keep retrying, then an obvious reordering of the code is:

bool success = false;
while (!success)
{
  try
  {
    database.SubmitChanges();
    success = true;
  }
  catch (ChangeConflictException)
  {
    database.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
  }
}

I don't know much about databases, so I'll stay away from theorising about what your actual problem is, but maybe this will fix it :/

Answer 2

It may not be picking up on all your conflicts when you call SubmitChanges() (and therefore not resolving them all), because the default behavior is to stop when it reaches the first one.

Try changing

database.SubmitChanges();

to

database.SubmitChanges(ConflictMode.ContinueOnConflict);

See http://arun-ts.blogspot.com/2009/08/linq-to-sql-concurrency-conflicts.html for more info. He also nests two levels of try/catch in his code sample for ResolveAll(), so the second time SubmitChanges() is tried, he's able to log any exception before exiting. That seems like a reasonable model to follow.