Stop Throwing a Fit and Close That Connection!

Mac Libby

Evan Howlett

We've just release an update to FORM. This is a patch version that fixes two things:

  • Holding onto connections once result sets are fully read into memory
  • Uncaptured exceptions

We've also updated our dependencies and moved from Microsoft.Data.Sqlite to System.Data.Sqlite. This is because System.Data.Sqlite has significantly better support, particularly around pragmas, for the SQLite API.

What's the Issue?

During our production-use of FORM 2.0, we discovered something unexpected happening -- connection exhaustion. We found that long-running processes would accumulate connections which would only ever be released once the process was stopped. This confounded us because we built FORM in such a way that the connection should close after reading the result-set returned from the database into memory. This was supposed to happen because the connection object would go out of scope after reading the result set, which would cause it to get GC'd thereby closing the connections.

However, we got lazy.

Laziness is a key aspect to languages like Haskell, where it's lazy by default. This is what allows Haskell to produce infinite data structures and can help improve performance of certain algorithms in some cases. For most other languages, however, laziness is opt-in. F# achieves this through a type called Lazy (very original). This type is the base for the Seq type making Seqs lazy also.

I Thunk I'm Following

When you make a lazy computation, you create something called a thunk. A more popular term for it would also be a promise. This is an idea of a computation that, when explicitly asked, will return the result of that computation.

Laziness itself wasn't really the problem, it was the way we were using it. Now, in order to execute a command and read the results from the database in a lazy fashion, the connection had to be opened and held inside the thunk -- otherwise it would go out of scope and get dropped. Because we were holding onto the connection in the context of the seq, it would live for however long the seq lived. So if make 100 queries to the database and hold onto all those results, you have 100 active connections.

Thankfully, F# provides an easy way to fix this. Sequences are often used as a generic, list-like data structure. But there's also a subtlety to them that can be overlooked: they can be a heterogeneous collection of steps.

Take a look at this picture

We're yielding the results of the readerFunction from the seq. However, the seq is also acting as a sort-of constructor. A constructor with the steps of making the command, setting that command's transaction, executing the reader on the command, and yielding the results of reading the reader. Or, in a more generic way, it's a sequence of expressions.

If we add in F#'s list-item-separator syntax, it becomes a bit more clear what's actually happening.

So if we have this list of expressions, why can't we just add one final expressions that closes the connection?

Well... we can! And that is what we did. Now, when the reader function returns and after the results are yielded to the caller, the connection is explicitly closed.

Mind Your P's and Exceptions.

We are zealots when it comes to how runtime errors should be handled. In no scenario should a 3rd party library crash your program. Exceptions are, well, exceptional and should only be allowed to be thrown by the runtime itself. Half the time, it's not documented anywhere that an exception could be thrown from a function and even less often are those exceptions caught and handled properly.

We have been trying our absolute best to capture all exceptions in any place it could happen and return a Result from our functions with the exception in the Error state. This design forces the programmer to handle the inevitable errors that pop-up in production and leads to significantly more reliable code.

We discovered an area where exceptions could have been thrown and we weren't catching them -- opening the connection and creating the transaction. To fix this, we added a try-with expression inside the transaction function.

That's all for now, thanks for reading!