How to handle intermittent connection errors?

In LightSpeed, how do would you handle intermittent connection errors, e.g. timeout or server unavailable,?

In other database code that needed to succeed, I have used the ExceptionGuard pattern that catches the SqlException and retries after a few seconds and eventually fails after x attempts.

Would be required to extend LightSpeed to use a similar query strategy? I would imagine that it would implemented as class that I could specify (e.g. like ConnectionStrategy.cs).

Yes, you would use a connection strategy and I believe some of our customers are already doing this -- see http://www.mindscapehq.com/forums/thread/3061 for some discussion of this.

From what initial look at the ConnectionStrategy class and from the examples you linked to, it appeared that it only allow to manually manage the opening/closing of a connection, e.g. force closing, or reuse. As all of my units of work objects are short-lived, the default LS connection strategy should be sufficient.

What I was looking for, was a way to wrap all the queries to the database in my own exception handling code so that if a query fails due to a intermittent network issue (a time-out or internal server offline), then it would sleep for ~300 ms and try again until it either a) it succeeds, or b) it fails x number of times, e.g 2 or perhaps 3.

Edit: or have I misunderstood the use of ConnectionStrategy and the examples?

You've understood the use of ConnectionStrategy, and you're right that it doesn't solve the 'wrap all queries' requirement. What it does do is provide a way to recover the connection without creating a new unit of work. So it is a necessary component of an "unreliable connection" strategy, but not the sole component.

As I mentioned, you can wrap database writes by overriding UnitOfWork.SaveChanges(bool). Wrapping all database reads is a bit trickier, as there's no one place you can do it. However I think you might be able to tackle it by:

1. Overriding the relevant UnitOfWork.Find, Calculate, etc. methods (this will handle user Find and Count queries)

2. Tweaking the code generation templates for association properties to perform exception handling and retries within the property getters. (The Get(EntityCollection) and Get(EntityHolder) methods don't go through the Find methods so overriding Find won't handle these cases.)

I'll have a look at whether we can provide a pluggable command execution strategy so you can isolate this into one place instead of having to do all this -- all our database access does go through one central point so it shouldn't be too difficult (famous last words).

I've added a method to the Interceptor class which you can override to intercept command execution. This will be in the next nightly build. Sample usage:

public class MyInterceptor : Interceptor
{
  public override object ExecuteCommand(Func<object> baseExecutor, IUnitOfWork unitOfWork, IDbCommand command)
  {
    try
    {
      return baseExecutor();
    }
    catch (SqlException)
    {
      // spit the dummy and try again
    }
  }
}

myLightSpeedContext.Interceptor = new MyInterceptor();

One possible issue with this design is that since the IDbCommand has already been bound to a connection at this point, you can't just call baseExecutor() again -- assuming you need to recreate the connection after an Azure error, which I have to admit I haven't checked -- because that would just retry the command on the old connection. Assuming that's the case, I think you will need to do something like:

ResetConnection();  // however you do this
unitOfWork.PrepareCommand(command);
return baseExecutor();

The baseExecutor has a reference to the IDbCommand object so re-preparing the command object should suffice -- you shouldn't need to call command.ExecuteXxx() directly. However, if this turns out not to work, let us know and we'll work with you on a more suitable design.

Excellent. Thanks Ivan for the quick-turn around on implementing the Interceptor feature.

...

I assume that this is in LightSpeed 4.0 only? If so I will have wait until I solve that upgrade problem before trying it out.

This is great thanks

Jeffrey