Recovering From Unknown Exceptions

In my last post I talked about using the Execute Around Method pattern to implement a common Big Outer Try Block to handle exceptions that propagate up as far as the service handler. But what do you do with them? Can you do anything with them? After all, if they’re unhandled you clearly never expected them so you need to recover from something you never anticipated...

Recoverability

The point of an exception handler is to allow you to recover from an exceptional condition. Now, there is much debate about what “exceptional” means but in this post I’m talking about scenarios for which no recovery has been attempted. That could be due to lack of knowledge about how to recover, or more likely, because the scenario never came out during testing.

There are really only two ways to behave after catching an unhandled exception - continue or terminate yourself. The former is often classified as the “best effort” approach, while the latter is described as “fail fast”. These are often seen as two opposing views, but in reality they are both valid approaches so long as you can decide how to loosely classify the error to ensure that the stability of the service isn’t compromised as a result of an unanticipated failure.

Systemic vs Domain Exceptions

What we are concerned with at the service handler entry point is trying to decide if the exception we caught indicates that the service has become unstable and will therefore cause problems with subsequent requests or if there will be no residual effects and we can carry on as normal (perhaps after logging a message to notify someone etc). For example a divide by zero exception is not likely to indicate that the process is stuffed whereas an access violation probably is[*].

We can split these two types of errors into two broad categories - Systemic and Domain. The former implies that there is something technically wrong with the process while the latter implies that there was something wrong with the request. We can then create two exception hierarchies rooted on them (ignoring the ultimate base such as System.Exception in .Net) - ServerException and DomainException. Then our Big Outer Try Block will look something like this:-

try
{
  . . .
}
catch (ServerException e)
{
  // Process borked - shutdown service
}
catch (DomainException e)
{
  // Dodgy request - return error
}

3rd Party Exceptions

Of course .Net and the rest of the world won’t know about our exception hierarchy and so we’ll need to categorise any known 3rd party exceptions into the Systemic or Domain groups and then extend the list of catch handlers appropriately. Taking a leaf out of my last post you can also encapsulate the exception handlers into a separate method that will catch and translate so you reduce to amount of cutting-and-pasting:-

public void TranslateCommonErrors(Action<T> method)
{
  try
  {
    method();
  }
  catch (OutOfMemoryException e)
  {
    throw new ServerException(e);
  }
  . . .
  catch (ArgumentOutOfRangeException e)
  {
    throw new DomainException(e);
  }
  . . .
}

We can use nested exceptions to ensure the caller has a chance to react appropriately. One of the first extension methods I (and no doubt many others) wrote was to flatten an exception hierarchy so it could be logged in all its glory or passed across the wire.

Remote Services

If we’re saying that a ServerException signifies the unstable nature of the service process then we need to translate that when it crosses a remote boundary. Due to the requirement that exception types must be serializable (at least with .Net & WCF) to cross the wire and we will be catching everything, including 3rd party exceptions which may not be serializable, we need to marshal the exception chain ourselves.

So, for remote service calls I like to have a mirror pair of exceptions - RemoteServerException and RemoteDomainException. These act as both the container for the marshalled exception chain and more importantly a signal to the client that the service is unstable. This gives the client a chance to perform recovery such as retrying the same request via a different server:-

while (!serverList.Empty())
{
  try
  {
    m_connection.DoTrickyStuff(parameters);
  }
  catch (RemoteServerException e)
  {
    // Out of servers!
    if (serverList.Empty())
      throw new ServerException(“No servers left”, e);

    // Server borked - try another one
    m_connection.Close();
    m_connection.Open(serverList.NextServer());
  }
  catch (RemoteDomainException e)
  {
    // Request stuffed - never gonna work...
    throw new DomainException(e);
  }
}

Another approach could be to immediately translate the RemoteServerException into a DomainException and throw that because the stability of the remote service does not indicate any instability within the client. However, as I mentioned last time, you need to be careful here because a technical error can grow from being a local problem on one server to a bigger one affecting the entire system when all the load balancing and retry logic starts kicking in for many failed requests.

If there is one thing the recent Amazon outage shows it’s that even with lots of smart people and decent testing you still can’t predict how a complex system is going to behave under duress.

The Lazy Approach to Exception Discovery

The eagle-eyed will have noticed that I’ve managed to avoid writing one particular catch block - the ‘catch all’ handler (catch(...) in C++ and catch(System.Exception) in C#). This is the ultimate handler and it’s going to handle The Unknown Unknown, so what do you do? Experience has probably taught you what you would consider a systemic error (e.g. Out of Memory) and so you’ll likely have those bases already covered leaving you to conclude that you can apply the “best effort” rule for everything else. And that is the approach I’ve taken.

In theory (given adequate documentation[#]) for every line of code you write you should be able to determine what the set of exceptions are and come up with a plan for recovering from all of them. Or deciding not to recover. Either way you can make an informed choice about what you’re going to do. But quite frankly who actually has the time to do this (except maybe for programmers writing software where lives are at stake)? What is more likely is that you’ll know the general pitfalls of what you’re invoking, such as when dealing with file-systems, networks or databases, and you’ll explicitly test those scenarios up-front but leave out the crystal-ball gazing. I think the effort is better spent ensuring the integration test environment runs continuously and is suitably enabled for injecting plausible faults than trying to second guess what is going to go bang under any given scenario.

This lazy approach also serves a secondary purpose and that is to see if your diagnostic tooling is up to the job when the time comes to use it. Have you just logged some simple message like “it failed” or do you have a message with the entire chain of exceptions, and do they tell you enough? Did you remember to log the stack trace or create a memory dump? Did the event register on your “system console” so you get a heads-up before it goes viral?

When a new scenario does come up that you realise you can recover from more gracefully that’s the time you’ll be glad you spent the effort mocking the underlying file-system, database and networking API’s so that you can write an automated test for it.

 

[*] Although I’d argue that an attempt to de-reference a NULL pointer probably is recoverable because it is the most common default initialisation state. In my experience access violations involving NULL pointers have nearly always been due to some logic error and not an indication of a wayward service that has started doing random stuff that eventually ended up with a NULL pointer. On the contrary an arbitrary memory location nearly always signals something pretty bad has gone before.

[#] Exception specifications don’t appear to have alleviated that.

Execute Around Method - The Subsystem Boundary Workhorse

I first came across the pattern Execute Around Method at an ACCU London talk by Paul Grenyer. The talk was about factoring out all the boilerplate code you need when dealing with databases in Java at the lower level. The concept revolves around the ubiquitous Extra Level of Indirection[*] with you passing either an object interface (Java/C++) or free/member function (C++) into another function so that the callee can perform all the boilerplate stuff on your behalf whilst your method can get on with focusing on the real value. The original article, like Paul’s talk, looks at resource management but many see it as a more general purpose pattern than that. There is also some correlation with the classic Gang of Four pattern Template Method but without the use of inheritance. In my mind they are two sides of the same coin it just depends on whether the discussion focuses on the encapsulation of the surrounding code or the inner behaviour being invoked.

Going The Extra Mile

Here’s a C++ example from the start-up code I’ve been employing for many moons[+]:-

typedef int (*EntryPoint)(int argc, const char* argv[]);

int bootstrap(EntryPoint fn, int argc, const char* argv[])
{
  try
  {
    return fn(argc argv);
  }
  catch (const std::exception& e)
  {
    // Log it...
  }
  . . .
  catch (...)
  {
    // Log it...
  }

  return EXIT_FAILURE;
}

int main(int argc, const char* argv[])
{
  return bootstrap(applicationMain, argc, argv);
}

int applicationMain(int argc, const char* argv[])
{
  // Parse command line, run app etc.
  . . .
  return EXIT_SUCCESS;
}

The physical process entry point (main) invokes the logical process entry point (applicationMain) via the start-up code (bootstrap). This leaves applicationMain() to do application specific stuff whilst leaving the grunge work of dealing with unhandled exceptions and other common low-level initialisation to bootstrap().

Naturally the advent of Boost means that you can adapt the code above to pass member functions or free functions with the same construct (Boost::Function). You could also do it with interfaces and the Command pattern but it’s even more verbose. Either way the extra level of indirection is still in your face.

A Brave New World

Now that I’m firmly entrenched in the world of C# I have the opportunity to write exactly the same code once more. And right back at the beginning that’s exactly what I did. Of course they’re called Delegates in C# and not Function Pointers, but the extra level of indirection was still there... Until I realised that I was finally working with a language that supported Closures. So now I can eschew the extra method and do it all inline:-

public static class ConsoleApplication
{
  protected int bootstrap(Func<string[], int> main,
                          string[] args)
  {
    try
    {
      return main(args);
    }
    catch
    . . .
    return ExitFailure;
  }
  public const int ExitSuccess = 0;
  public const int ExitFailure = 1;
}

public static class Program : ConsoleApplication
{
  public static int Main(string[] args)
  {
    return boostrap(args =>
    {
      // Parse command line, run app etc. 
      . . .
      return ExitSuccess;
    });
  }
}

The use of a closure with Execute Around Method has made many of those ugly parts of the system (most notably the subsystem boundaries) far more readable and maintainable. What follows are the scenarios where I find it’s been put to constant use…

SQL execution

On the face of it executing a SQL query is quite trivial, but when trying to write a robust and scalable system you soon start to deal with many thorny issues such as transient errors and performance problems. These are the behaviours I tend to wrap around SQL queries:-

• Instrument the query execution time
• Drop and reconnect the connection on a cluster failover
• Catch and retry when a timeout (or other recoverable transient error) occurs

Dealing with transient errors is particularly tricky because you can make matters worse if you keep retrying a query when the database is already under heavy load. It’s also important to ensure you’re dealing with the error that you think you are by catching specific exception types; SQL exceptions come through as one exception type and so you also need to inspect its properties carefully.

Remote Service Stub

Working back up the call stack we end up at the server-side stub to the remote service call. The caller of that method will probably be some framework code (e.g. WCF) and so it’s our last chance saloon to handle anything. Once again duties are in the realms of performance and error recovery:-

• Instrument the remote call
• Use a Big Outer Try Block to catch all exceptions and handle as appropriate

As a rule of thumb you shouldn’t let exceptions propagate across a “module boundary” because you don’t know what the callee will be able to do with it. In practice this is aimed at COM components which can be written in, say C++, while the application may be written in the “exception-less” C. It also applies to services too because they have to marshal anything you pass (or throw) back to them and if they can’t do that then you’ll get a significantly less helpful error to work with.

The Big Outer Try Block in the remote stub is also the point where you get to decide what “best effort” means. It may mean anything from “catch everything and do nothing” to “catch nothing and let everything through” but I would suggest you pick some middle ground. You want to avoid something trivial like an invalid argument causing the server to fail whilst at the same time not letting a systemic issue such as an access violation cause the server to enter a toxic state. If possible I like systemic issues to cause the server to take itself down ASAP to avoid causing more harm, but that can be worse than the disease[$].

Remote Service Proxy

Stepping back once more across the wire we come to the client-side invocation of a remote request. This is really just a generalised version of the SQL execution scenario but with one extra facility available to us:-

• Instrument the request invocation time
• Drop and reconnect to the service on a transport failure
• Catch and retry when a timeout (or other recoverable transient error) occurs

One obvious question might be why you would instrument the service request from both the client proxy and service stub? Well, the difference tells you how much overhead there is in the transport layer. When talking using a low-level transport like a point-to-point socket there’s not much code in-between yours, but once you start using non-trivial middleware like DCOM, MSMQ and 3rd party grid computing products knowing where the bottleneck is becomes much harder. However if you can measure and monitor the request and response latencies automatically you’ll be in much better shape when the inevitable bottleneck appears.

Aspect Orientated Programming?

Not that it really matters, but is Execute Around Method just an implementation vehicle for AOP? I’ve never really got my head around where AOP truly fits into the picture, it always seems like you need external tooling to make it work and I’m not a big fan of “hidden code” as it makes it harder to comprehend what’s really going on without using the debugger. The wrappers listed above tend to have all the aspects woven together rather than as separate concerns so perhaps that’s a code smell that true AOP helps highlight. I’m not going to lose sleep over it but I am aware it’s a technique I don’t understand yet.

 

[*] And I don’t mean Phil Nash’s blog :-)

[+] You would have thought this problem would have been solved by now, but most runtimes seem to insist on letting an unhandled exception be treated as a successful invocation by the calling application!

[$] In a grid computing system you can easily create a “black hole” that sucks up and toasts your entire workload if you’re not careful. This is created by an engine process that requests work and then fails it very quickly either because itself or one of its dependent services has failed thereby causing it to request more work much quicker than it’s siblings.

Unit Tests Obscure Dead Code

I’ve been having a bit of a clean-up in our codebase as I know of various large bits of functionality that for one reason or another never made it into production and now they are just becoming a distraction. Even though we are using the thoroughly useful ReSharper, which can point out some obvious dead code, it doesn’t perform enough analysis to see past code that is only referenced by unit tests.

Oh the irony… I spend all that time banging on about writing unit tests and now I’m cursing their existence because they stop me from seeing dead code! But my team is using NUnit, not some obscure framework, and ReSharper has native support for NUnit, so it’s not out of the realms of possibility that JetBrains could “join the dots” and provide a facility to highlight dead code that only has unit tests for it. If you’re writing a library I presume you’d want this feature off by default :-) but if you’re writing an application you’d probably like it on. Of course if you have a really large codebase and you’ve split it to ensure you only build what you need then I guess it becomes less accurate and false positives are not exactly a confidence builder. I’d still prefer to have the choice of enabling a “be aggressive” flag when I’m in cleanup mode.

Clearly there are other analysis tools, like NCover, that could help determine dead code, but I’m not talking about squeezing every last line of redundant code out (which would probably involve actually running it and then deciding whether you covered enough scenarios), but just pointing out the really obvious stuff like unused classes, methods, interface  implementations etc.

Anyway, until such time as JetBrains (or a suitable alternative) comes entirely to my rescue I’m using the following semi-automatic ReSharper assisted approach:-

1. Load the solution and remove all unit test projects from it
2. Use ReSharper to show up dead code
3. Delete dead code
4. Goto 2 to find what new dead code ReSharper has unearthed[+]
5. Reload the solution with unit test projects
6. Delete redundant unit tests
7. Fix any false positives[*]

Of course having said all that someone will go and point out that I should RTFM or show me how FxCop already does this… Please do!

 

[+] ReSharper uses the term “heuristically determined unused method”, and yet it still doesn’t point out a lot of dead code - you often have to go and delete the unused callers before it will mark something as dead. And these aren’t methods with deep call graphs, maybe only 1 or two deep. Perhaps it’s more pessimistic when interfaces are involved?

[*] Yes we have other dependent code (such as various PowerShell scripts) that means you can’t just carelessly swing the axe about but it’s pretty obvious what would be called externally.

User Defined Types in SQL Server

In my recent post The Public Interface of a Database I talked about using User Defined Types and how I believe they help make the interface more maintainable. Now being in production and having real data stored in our tables means that changing them is going to be more difficult. The other day I needed to do just that and I found it’s not quite as simple as I had hoped…

Domain Types (Theory)

Although I’ve never studied database theory in any formal way I can see how User Defined Types are probably meant to be used - to allow you to express the commonality between data of the same nature and also to attach constraints to deal with restrictions that the underlying storage type may not possess.

For example, if you were storing a percentage (as an integer) you could use any of the integer types - tinyint, smallint, int etc, however it makes sense to use the smallest amount of storage required, i.e. tinyint (1 byte). But this type has a range of 0 - 255 which is wider than a percentage (0 - 100) so you need to bind a rule to say the value is actually limited to just (0 <= value <= 100).

Domain Types (Practice)

The problem is that the rule (at least under SQL Server) only applies to a column in a table; it is effectively a Check Constraint[+]. This means that declaring them in your stored procedure parameter list and using them within your SQL code only has a partial effect - you get the effects from abstracting the underlying storage type, but not the additional validation rules.

To make matters worse when you try to change the UDT and go searching for the ALTER TYPE statement you’ll find it doesn’t exist[*]. The only way you can change a UDT is to drop it and then create it again. But, to drop it you need to have nothing referencing it - no tables, functions or procedures.

Putting aside the difficulties in changing them for the moment let’s see what benefits they can bestow…

Type Aliases

Even though they do not enforce their constraints when as variables or arguments, they still have a marked effect on the maintainability of your code. Using the kind of example I cited in my previous post, which do you think is more expressive?

declare @name varchar(100)
vs
declare @name dbo.CustomerName_t

With good variable naming it is arguable how much the type adds in this example. If I changed the variable name to “@customerName” then they would both be just as explicit; but at least in the latter case you have the choice to let the type name enhance the readability.

There is also the temptation, especially with varchar columns, to just pick a size that is “big enough”. This means you might see varchar(100) in some places, varchar(128) in others and perhaps a very specific varchar(33) elsewhere. User defined types as simple aliases take all that guesswork away so that you don’t have to think nearly so hard. Other common sources of discrepancy are the various numbers used in financial scenarios that have a habit of being stored with different scales and precision, e.g. float(n), decimal(s, p) depending on the paranoia level of the developer.

Abstracting Identities

Entities, such as customers and products, are commonly referred to by some integer based surrogate key (perhaps generated by an IDENTITY column), but they may also be based on a natural key if, say, a short code exists. Once again, using a type alias means that you don’t have to think so hard; the example below could be based on an underlying integer or varchar type:-

declare @productId dbo.ProductId_t

This is a big win because unlike some cases where you could argue that you need to know what the underlying storage type is (perhaps you’re going to perform some manipulation of its contents) an entity ID is (in my experience) nearly always a unique & opaque value - it’s contents are unimportant. I’ve come to find this construct very unsatisfactory (in any language):-

declare @id int

The Integer type supports so many operations and allows for unnecessary promotions that it’s an accident waiting to happen[#] when used for the identity of an entity. Sadly, using UDT’s will not disable the undesirable arithmetic operations, but it should help avoid any unexpected type mismatches by virtue of not being able to get it wrong in the first place.

Mechanisms for Change

Let’s come back to that gnarly issue about how to change them later because that’s what prompted this post in the first place…

As mentioned earlier there is no magical ALTER TYPE statement and so that means you have two real choices:-

1. Drop every referenced object, drop the old type and then recreate everything
2. Introduce a new type, migrate everything across to that and then drop the old type

The second option may sound simpler, and it is, slightly; but only because you can recreate objects in an order that is less susceptible to dependency problems. But functions and procedures are the easy part anyway, what’s hard is altering a tables’ schema, especially big ones!

Ultimately your choice for updating a UDT that is used in a table depends very much on the change you need to make to the UDT and whether it requires data in the existing table to be touched. For example, if you widen a column from varchar(10) to varchar(20) SQL Server only has to change the table metadata to say the maximum length has increased because the actual length of each value does not change. Shrinking the column size or changing an integral type will likely cause data to be touched, or even just examined to ensure it still conforms. According to one of the Inside SQL Server books you can tell a priori whether data will need to be touched just by looking at the estimated execution plan.

So far my needs to change a UDT have only revolved around varchar columns and so I’ve been lucky that the table change has been the simple one. The changes to the functions and procedures were also pretty easy because we have the ability to rebuild the database from scratch (our Continuous Integration process does this each build) so I could ensure I caught and checked every place where the type was used. It’s a somewhat laborious process, but with the right development process around you it can be pretty easy.

Updating Tables

As I understand it the most efficient way to update an existing table schema (where you’re not just appending a new column) is to rename it, create the new one and then copy the data back over. When I first came to change my UDT I knew that I could either try and fix-up the existing one or create a new “version” of it. When I considered what actually needed to be done to fix the type and how big the table was I buckled and created a new type, called “MyType2_t”. This was because it felt like there were far less ordering issues and I didn’t have to generate scripts to drop things first. So, instead of this lengthy tirade:-

1. Drop dependent functions and procedures
2. Create copies of tables without the UDT to be modified
3. Copy data to backup tables
4. Recreate the UDT with the new definition
5. Create new tables with the new UDT
6. Copy the data back again
7. Recreate the functions and procedures

…I could just use ALTER TABLE and switch the types, taking whatever hit was necessary to adjust the data in the tables (which was none in this case):-

1. Create the new version of the UDT
2. Alter the tables to use the new version of the UDT
3. Recreate the functions and procedures[$]
4. Drop the old version of the UDT

Yes, the deployment was easier, but we’re now left with a wart - a type with a version suffix (e.g. MyType2_t). After having gone through all this it dawned on me soon after that if you could use ALTER TABLE to switch types then surely you can just do this instead:-

1. Drop dependent functions and procedures
2. Alter the table to use a non-UDT based equivalent type
3. Recreate the UDT with the new definition
4. Alter the table to use the new UDT
5. Recreate the functions and procedures

If SQL Server is smart enough to just apply a metadata change when growing a varchar(n) then it should be able to just replace the metadata when switching from a UDT that is effectively a varchar(n) to a non-UDT alias of a varchar(n). This would remove the dependency on the UDT without any upheaval at all. Sadly it’s an experiment I cannot run on this netbook, but I intend to.

Unnecessary Context Switching

Having worked for some time on a codebase that uses UDTs virtually everywhere, now, when I do see an explicit type I begin to question whether it’s correct or not and I find that very distracting. I begin to question whether any thought was put into it or whether the author was just being lazy. Perhaps it’s a new type and they just didn’t have time to create a UDT for it? Or maybe they did but missed fixing up this case? Any way you look at it I’m now being diverted from my real goal - but that’s just the age old argument about being consistent I suppose.

 

[+] According to the documentation for CREATE RULE that these are now deprecated anyway in favour of using Check Constraints. Was that because they just weren’t as useful as expected?

[*] There was a “petition” on Microsoft Connect about the lack of an ALTER TYPE statement which I discovered whilst trawling StackOverflow for alternate solutions.

[#] This is one area where C++ enumerations are a boon because they disallow the arithmetic operations and also give the value a type-safe holder. This jolly useful use of enumerations was pointed out to me by fellow ACCU member Jonathan Wakely.

[$] Each function and procedure script has the common “if (object_id(Xxx) is not null) drop Xxx” prologue.

Out vs Ref For TryXxx Style Methods

None of the popular programming languages that I know of allow you to overload a method based on error semantics. A common pattern to workaround this is to provide two overloads – one named normally that throws an exception on failure and another no-throw version who’s name is prefixed with “Try” and returns a bool instead (with any additional return values handled by output parameters). A classic example is the parsing functions on the C'# DateTime type:-

DateTime Parse(string value);
bool     TryParse(string value, out DateTime result);

In principle it’s easy enough to move from the exception throwing form:-

{
  DateTime output = DateTime.Parse(input);
  . . .
}

…to the alternate non-throwing form when you decide you need the different error handling semantics:-

{
  DateTime output;

  if (DateTime.TryParse(input, output))
  {
    . . .
  }
}

But what about when you don’t care if the method succeeded or not? On a number of occasions I have used a TryXxx style method and have not cared about the boolean return code, I just want it to use my default value if it fails:-

{
  DateTime output = DateTime.Now; // default

  DateTime.TryParse(input, output);
  . . .
}

Unfortunately this won’t have the desired effect (it actually won’t compile as is, but hold on) because your default value gets clobbered. Consider the following method on my IConfiguration interface that attempts to retrieve a configuration setting, if it exists:-

bool TryGetSetting(string key, out string value);

If I use the ‘out’ keyword as part of the interface I am forced to provide a value for all code paths. Consequently the implementation will probably look like this:-

bool TryGetSetting(string key, out string value)
{
  // Attempt to retrieve the setting 
  if (. . .)
  { 
    value = . . .; 
    return true;
  }
  else
  {
    // Must initialise the output value on all paths
    value = null;
    return false;
  }
}

The only general default value you can provide for a reference is null. Yes, for a string (or any other class that defines it) you could use the ‘Empty’ value, but that still clobbers any input from the caller. And so you force the caller to acknowledge the failure and write the slightly more verbose:-

{
  string value;

  if (!config.TryGetSetting("setting", out value))
    value = "my default value";
  . . .
}

The alternative is to use ‘ref’ instead, which allows the caller to provide a default value and you no longer have to clobber it in your implementation:-

bool TryGetSetting(string key, ref string value)
{
  // Attempt to retrieve the value
  if (. . .)
  { 
    value = . . .; 
    return true;
  }
  else
  {
    // Leave caller’s value untouched
    return false;
  }
}

Finally, as a caller I can now just write this:-

{
  string value = "my default value";

  config.TryGetSetting("setting", ref value)
  . . .
}

So, I wonder why the C# designers picked ‘out’ over ‘ref’ in the first place? Perhaps they felt it was safer. But is it that much safer? If you use ‘out’ and don’t check the return code you’ll probably end up either accessing a null reference or continuing with the equivalent of 0 for a value type, i.e. whatever default<type>) returns. OK, this is far superior to the C++ world where an uninitialized variable could be anything[*]. If you use ‘ref’ then you let the caller choose the uninitialised value, which, if they are following best practice will result in the same effect because they won’t be reusing existing variables for other purposes.

There is of course a semantic difference between ‘out’ and ‘ref’, but I think what I’m suggesting blurs the line between them. If you look at ‘out’ and ‘ref’ through COM’s eyes and put a network in the middle then it’s all about whether you need to marshal the value to the callee and this is not the behaviour we want. The callee doesn’t need the value and we certainly don’t want to allow it to be able to modify it, so ‘ref’ is out (if you’ll pardon the pun). What we want ‘out’ to mean in this scenario is “don’t clobber the existing variable if no output value was provided, and don’t bother marshalling the value into the callee either”.

It’s great that C# points out where you have attempted to use an uninitialised variable, but sadly I think it’s that same mechanism that also gets in the way sometimes.

[*] I once got bitten by an uninitialized ‘bool’ during my C++ days. Somewhat ironically it was exactly because we were using all the debug settings during development and they very cleverly initialise stack variables and heap memory to a non-zero value that it went unnoticed. This is because the “uninitialized” value was always reinterpreted for a ‘bool’ as ‘true’. There is a reason why you always write a failing unit test first…

Testing Drives the Need for Flexible Configuration

If you look at our system’s production configuration settings you would be fooled into thinking that we only need a simple configuration mechanism that supports a single configuration file. In production it’s always easier because things have settled down, but during testing is when the flexibility of your configuration mechanism really comes into play.

I work on distributed systems which naturally have quite a few moving parts and one of the biggest hurdles to development and maintenance in the past has been because the various components cannot be independently configured so that you can cherry-pick which services you run locally and which you draw from your integration/system test environment. Local (as in on your desktop) integration testing puts the biggest strain on your configuration mechanism as you probably can only afford to run a few of the services that you might need unless your company also provides those big iron boxes for developer workstations[#].

In the past I’ve found the need to override component settings using a variety of criteria and the following list is definitely not exhaustive, but gives the most common reasons I have encountered:-

Per-Environment

The most obvious candidate is environmental as there is usually a need to have multiple copies of the system running for different reasons. I would hazard a guess that most teams generally have separate DEV, TEST & PROD environments to cover each aspect of the classic software lifecycle. For small systems, or systems with top-notch test coverage the DEV & TEST environments may serve the same purpose. Conversely I have worked on a team that had 7 DEV environments (one per development stream), a couple of TEST environments and a number of other special environments used for regulatory purposes, all in addition to the single production one.

What often distinguishes these environments is the instances of the external services that you can use. Generally speaking all production environments are ring-fenced so that you only have PROD talking to PROD to ensure isolation. In some cases you may be lucky enough to have UAT talking to PROD, perhaps to support parallel running. But DEV environments are often in a sorry state and highly untrusted so are ring-fenced for the same reason as PROD, but this time for the stability of everyone else’s systems.

Where possible I like the non-production environments to be a true mirror of the production one, with the minimum changes required to work around environmental differences. Ideally we’d have infinite hardware so that we could deploy every continuous build to multiple environments configured for different purposes, such as stress testing, fault injection, DR failover etc. But we don’t. So we have to settle for continuous deployment to DEV to run through some basic scenarios, followed by promotion to UAT to provide some stability testing. What this means is that our inputs are often the same as for production, but naturally our outputs have to be different. But you don’t want to have to configure each output folder separately, so you need some variable-based mechanism to keep it manageable.

The Disaster Recovery (DR) environment is an interesting special case because it should look and smell just like production. A common technique for minimising configuration changes during a failover is to use DNS Common Names (CNAMEs) for the important servers, but that isn’t always foolproof. Kerberos delegation in combination with CNAMEs is a horribly complicated affair. And that’s when you have no control over the network infrastructure.

Per-Machine

Next up is machine specific settings. Even in a homogenous Windows environment you often have a mix of 64-bit and 32-bit hardware, slightly different hard disk partitioning, or different performance for different services. Big corporations love their “standard builds” which helps minimises the impact but even those change over time as the hardware and OS changes – just look at where user data has been stored in Windows over the last few releases. The ever changing security landscape also means that best practices change and these will, on occasion, have a knock-on effect on your system set up.

By far the biggest use for per-machine overrides though is during development, i.e. when running on the developers workstation. While unit testing makes a significant contribution to the overall testing process you still need the ability to easily cobble together a local sandbox in which you can do some integration testing. I believe the DEV environment cannot be a free-for-all and should be treated with almost the same respect as production because if the DEV environment is stable (and running the latest code) you can often reduce the setup time for your integration testing sandbox by drawing on the DEV services instead of running them locally.

Per-Process-Type

Virtually all processes in the system will probably share the same basic configuration, but certain processes will have specific tasks to do and so they may need to be reconfigured to work around transient problems. One of the reasons for using lots of processes (that share logic via libraries) is exactly to make configuration easier because you can use the process name as a “configuration variable”.

The command line is probably the default mechanism most people think of when you want to control the behaviour of a process, but I find it’s useful to distinguish between task specific parameters, which you’ll likely always be providing, and background parameters that remain largely static. This means that when you use the “--help” switch you are not inundated with pages of options. For example a process that always needs an input file will take that on the command line, as it might an optional output folder; but the database that provides all the background data will be defaulted using say an .ini file.

Per-User

My final category is down to the user (or service account) under which the process runs. I’m not talking about client-side behaviour which could well be entirely dynamic, but server-side where you often run all your services under one or more special accounts. There is often an element of crossover here with the environment as there may be separate DEV, TEST and PROD service accounts to help with isolation. Support is another scenario where the user account can come into play as I may want to enable/disable certain features to help avoid tainting the environment I’m inspecting, such as using a different logging configuration.

Getting permissions granted is one of those tasks that often gets forgotten until the last minute (unless DEV is treated liked PROD). Before you know it you switch from DEV (where everyone has way too many rights) to UAT and suddenly find things don’t work. A number of times in the past I’ve worked on systems where a developer’s account has been temporarily used to run a process in DEV or UAT to keep things moving whilst the underlying change requests bounce around the organisation. Naturally security is taken pretty seriously and so permissions changes always seem to need three times as many signatures as other requests.

Hierarchical Configuration

Although most configuration differences I’ve encountered tend to fall into one specific category per setting, there are some occasions where I’ve had cause to need to override the same setting based on two categories, say, environment and machine (or user and process). However because the hardware and software is itself partitioned (e.g. environment/user) it’s usually been the same as overriding on just the latter (e.g. machine/process).

What this has all naturally lead to is a hierarchical configuration mechanism, something like what .Net provides, but where <machine> does not mean all software on that machine, just my system. It may also take in multiple configuration providers, such as a database, .ini files, registry[*] etc. My current system only uses .config style[$] files at present and on start-up each process will go looking for them in the assembly folder in the following order:-

1. System.Global.config
2. System.<environment>.config
3. System.<machine>.config
4. System.<process>.config
5. System.<user>.config

Yes, this means that every process will hit the file-system looking for up to 5 files, but in the grand scheme of things the hit is minimal. In the past I have also allowed config settings and the config filename to be overridden on the command line by using a global command line handler that processes the common settings. This has been invaluable when you want to run the same process side-by-side during support or debugging and you need slightly different configurations, such as forcing them to write to different output folders.

Use Sensible Defaults

It might appear from this post that I’m configuration mad. On the contrary, I like the ability to override settings when it’s appropriate, but I don’t want to be forced to provide settings that have an obvious default. I don’t like seeing masses of configuration entries just because someone may need to use it one day – that’s what source code and documentation is for.

I once worked on a system where all configuration settings were explicit. This was intentional according to the lead developer because you then knew what settings were being used without having to rummage around source code or find some (probably out-of-date) documentation. I understand this desire but it made testing so much harder as there was a single massive configuration object to bootstrap before any testable code ran. I shouldn’t need to provide a valid setting for some obscure business rule when I’m trying to test changes to the messaging layer – it just obscures the test.

Configuration Formats

I’m a big fan of simple string key/value pairs for the configuration format – the old fashioned Windows .ini file still does it for me. Yes XML may be more flexible but it’s also far more verbose. Also, once you get into hierarchical configurations (such as .Net .config files), its behaviour becomes unintuitive as you have to question whether sections are merged at the section level, or individual entries within each section. These little things just make integration/systems testing harder.

I mentioned configuration variables earlier and they make a big difference during testing. You could specify, say, all your input folders individually, but when they are related that’s a real pain when it comes to environmental changes, e.g.

[Feeds]
SystemX=\\Server\PROD\Imports\SystemX
SystemY=\\Server\PROD\Imports\SystemY

One option is to generate your configuration from some sort of template, but I find that a little too invasive. It’s pretty easy to emulate the environment variable syntax so you only have one setting to change:-

[Variables]
SharedData=\\Server\PROD
FeedsRoot=%SharedData%\Imports

[Feeds]
SystemX=%FeedsRoot%\SystemX
SystemY=%FeedsRoot%\SystemY

You can even chain onto the environment variables collection so that you can use %TEMP% and %ProgramFiles% when necessary.

 

[#] Quite how anyone was ever expected to develop solid, reliable, multi-threaded services with a machine with only a single or dual hyper-threaded CPU is quite beyond me. I remember 10 years ago when we had 1 single dual-CPU box in the corner of the room which was used “for multi-threaded testing”. Things are better now, but sadly not by that much.

[*] Environment variables are great for controlling local processes but are unsuitable when it comes to Windows services because a machine reboot is required when they change. This is because the environment variables that a service process receives is inherited from the SCM (Service Control Manager), so you’d need to restart the SCM as well as the service (it doesn’t notice changes like the Explorer shell does). So, in this scenario I would favour using the Registry instead as you can get away with just bouncing the service.

[$] Rather than spend time up-front learning all about the .Net ConfigurationManager I just created a simple mechanism that happened to use files with a .config extension and that also happened to use the same XML format as for the <appSettings> section. The intention was always to switch over to the real .Net ConfigurationManager, but we haven’t needed to yet – even our common client-side WCF settings use our hierarchical mechanism.

The Public Interface of a Database

The part of my recent ACCU London talk on database unit testing that generated the most chatter was the notion that there can be a formal public interface to it. Clearly we’re not talking about desktop databases such as Access, but the big iron products like SQL Server and Oracle. It is also in the Enterprise arena that this distinction is most sorely needed because it is too easy to bypass any Data Access Layer and directly hit the tables with tools like SQLCMD and BCP. I’m not suggesting that this is ever done with any malicious intent, on the contrary, it may well be done as a workaround or Tactical Fix[*] until a proper solution can be developed.

In any Enterprise there are often a multitude of in-house and external systems all connected and sharing data. Different consumers will transform that data into whatever form they need and successful systems can in turn find themselves becoming the publishers of data they only intended to consume because they can provide it in a more digestible form. The tricky part is putting some speed bumps in place to ensure that the development team can see when they have violated the design by opening up the internals to the outside world.

Building Abstractions

So what does this Public Interface look like? If you look at tables and stored procedures in a similar light to structs and functions in C, or classes and methods in C++/C# you naturally look for a way to stop direct access to any data members and this means stopping the caller performing a SELECT, INSERT, UPDATE or DELETE directly on the table. You also need to ensure that any implementation details remain private and do not leak out by clearly partitioning your code so that the clients can’t [accidentally] exploit them.

I’ll admit the analogy is far from perfect because there are significant differences between a table of data rows and a single instance of a class, but the point is to try and imagine what it would do for you if you could, and how you might be able to achieve some of the more desirable effects without sacrificing others such as productivity and/or performance. As you tighten the interface you will gain more flexibility in how you can implement a feature and more importantly open yourself up to the world of database refactoring (assuming you have a good unit test suite behind you) and even consider using TDD.

Stored Procedures

The most obvious conclusion to the encapsulation of logic is the blanket use of Stored Procedures and User Defined Functions so that all access is done using parameterised functions. These two constructs provide the most flexibility in the way that you design your abstractions because they allow for easy composition which becomes more essential as you start to acquire more and more logic. They also provide a good backdoor for when the unanticipated performance problems start to appear as you can often remediate a SQL query without disturbing the client.

Of course they are not a panacea, but they do provide a good fit for most circumstances and are a natural seam for writing unit tests and using TDD. The unit test aspect has probably been the biggest win for us because it has allowed us to develop and test the behaviour in isolation and then just plug it in knowing it works. It has also allowed us to refactor our data model more easily because we have the confidence that we are in control of it.

Views

Stored procedures are great when you have a very well defined piece of functionality to encapsulate, such as updating a record that has other side-effects that are inconsequential to the client, but they are more of a burden when it comes to querying data. If you try and encapsulate every read query using stored procedures you can easily end up with a sea of procedures all with subtly different names, or a single behemoth procedure that takes lots of nullable parameters and has a ‘where’ clause that no one wants to touch. Views solve this problem by giving the power back to client, but only insofar as to let them control the query predicates – the list of columns (and their names) should be carefully decided up front to avoid just exposing the base tables indirectly, as then you’re back to where you started.

User Defined Types

User defined types seem to have a bad name for themselves but I couldn’t find much literature on what the big issues really are[#][$]. I find them to be a very good way to create aliases for the primitive types in the same way as you would use ‘typedef’ in C/C++. A classic source of inconsistencies that I’ve seen in SQL code is where you see a varchar(100) used in some places and a varchar(25) in others as the developer just knows it has to be “big enough” and so picks an arbitrary size; it’s then left to the maintainer to puzzle the reason for the mismatch. UDTs allow your interface to be consistent in it’s use of types which makes comprehension easier and they can also be used by the client to ensure they pass compatible types across the boundary.

My current system started out on SQL Server 2005 which didn’t have the native Date and Time types so we created aliases for them. We managed to move to SQL Server 2008 before release and so could simply change the aliases. The system also has a number of different inputs and outputs that are not simple integers and so it’s important that we use the same scale and precision for the same type of value.

Schemas 

Another tool in the box is Schemas. These are a way to partition your code, much like you would with namespaces (or packages), although they don’t provide the hierarchic behaviour that you get with, say, C# or C++. A very common database model is to separate the input (or staging) tables from the final ones whilst the data is validated and cleansed. One way to apply that might be to use the default ‘dbo’ schema for the production tables and use a separate ‘staging’ schema for the input tables. You can then overload table and object names without having to resort to ugly prefixes or suffixes.

You can of course go much further than this if you start out by treating the default ‘dbo’ schema as synonymous with ‘internal’ or ‘private’; then all objects in that default schema are considered as purely implementation details and not directly exposed to the client. You then create separate schemas for the major subsystems, such as a ‘backend’ schema for the server-side code and a ‘ui’ schema for the client. These will allow you to create multiple interfaces tailored for each subsystem that, if you use traditional functional composition, avoids duplicate code by just internally layering stored procedures. This natural layering also quickly highlights design problems, such as when you see an object in the ‘dbo’ schema referencing one in the ‘staging’ schema. There is often a natural flow, such as from staging to production, and schemas help you remain consistent about how you achieve that flow, i.e. push from staging to production or pull from staging by production.

Permissions

Ultimately if you want to deny access to something, just don’t grant it in the first place :-). Databases offer a fine-grained permissions system that allows you to grant access to specific objects such as individual stored procedures, functions and views. This means that your core definition of what constitutes the public interface is “anything you’ve been granted access to”. However it is not always obvious what may or may not be permissioned, and to what role, so the other mechanisms, such as schemas, can be used to provide a more visible means of scope.

Part of the permissions structure will be based around the roles the various clients play, such as server or UI, and so you will probably see a strong correlation between a role and the set of objects you then choose to expose in your various interfaces rather than just blindly exposing the same set for everyone. For example it is common to have a “Service Account” under which the back-end runs, this could be mapped to a specific Service Role which is then only granted permission to those objects it genuinely invokes which all exist in the specific Service schema. In contrast a Support Role may be granted access to all objects that only performs reads but have a bunch of special objects in a separate Support schema that can do some serious damage but are needed occasionally to fix certain problems. Different ‘public’ interfaces for different roles.

Oranges Are Not The Only Fruit

Jeff Atwood wrote a post for his Coding Horror blog way back in 2004 titled “Who Needs Stored Procedures, Anyways?” that rallies against the blanket use of stored procedures. One of his points is aimed at dispelling the performance myth of procedures, but as I wrote last year “Stored Procedures Are About More Than Just Performance”, but he’s right that embedded parameterised queries can perform equally well. Hopefully my comment about the use of views shows that I also feel the blanket use of procedures is not necessarily the best choice.

There are a lot of comments to his post, many of which promote the silver bullet that is an O/RM, such as Hibernate or Entity Framework. And that is why I was careful to describe the context in my opening paragraphs. If you’re working in a closed shop on an isolated system you can probably get away with a single Data Access Layer through which everything is channelled, but that is often not possible in a large corporation.

I would say that Jeff’s main point is that if you want to expose “A Service” then do just that, say, as a Web Service, so that you truly abstract away the persistent store. I agree with the sentiment, but corporate systems just don’t change technology stacks on a whim, they seem to try and drain every last ounce out of them. Hence I would question the real value in building (and more importantly maintaining) that proverbial “extra level of indirection” up front. Clearly one size does not fit all, but sadly many commenter's appear to fail to appreciate that. Heterogeneous systems are also wonderful in theory but when a company purposefully restricts itself to a small set of vendors and products what does it really buy you?

For me personally the comments that resonated most closely were the ones advocating the use of independent unit testing for the database code. I believe the ideal of using TDD to develop the database layer adds an interesting modern twist that should at least cause Jeff some pause for thought.

 

[*] The term tactical is almost always synonymous for quick-and-dirty, whereas strategic is seen as the right way to do it. However the number of tactical fixes and even entire tactical systems that have lived longer that many strategic solutions is huge. It just goes to show that growing systems organically is often a better way to approach the problem.

[#] The only real issue I’ve come across is that you can’t define a temporary table using UDTs on SQL Server – you have to add them to the model database or something which all sounds a bit ugly. I’ve not tried it out but I recently read that you can use “SELECT TOP(0) X,Y,Z INTO #Tmp FROM Table” or “SELECT X,Y,Z INTO #Tmp FROM Table WHERE 1 = 0” as a fast way of creating a temporary table based on a result set because the optimiser should know it doesn’t need to do any I/O. Describing your temporary table this way makes perfect sense as it avoids the need to specify any types or column names explicitly; so long as it incurs a minimal performance cost that is.

[$] Another more minor grumble seems to be to do with binding rules, in that they are effectively copied at table creation time instead of referencing the UDT master definition. You also can’t alias the XML type on SQL Server which would have been useful to us as we had performance concerns about using the XML type on a particular column. In the end we got close to what we wanted by using an alias to varchar(8000) with a rule that attempts a conversion of the value to XML – we could then just drop the rule if we noticed a performance problem in testing.