Database Connection Handling in Common Lisp Web Apps

I’ve spent most of this evening hacking my blogging software, but sadly not to add new features. I’ve clearly spent too much time doing web programming in PHP, because I neglected to even consider some major threading issues that arise when doing web programming from a persistent environment. In PHP the entire script is loaded (into a fresh thread) everytime the page is requested. This has a few interesting implications. One is that we can in many ways be wasteful about tracking resources when doing web programming in PHP because the whole running instance will be trashed soon enough anyway. Another is that even global variables are in some sense quite local. Or, in Lisp-speak, their scope is broad, but their extent is minimal.

The resource tracking issue isn’t very troubling when web programming in Lisp. The garbage collector has our backs on most things, and where it doesn’t we have our lexically-scoped macros (i.e. with-...) which promise us that our resources will be freed without demanding even a second thought from us.

It is the second issue to which we must pay more attention; our globals are truly global. Some resources we wish to share among all our threads—configuration parameters, constants, functions—but some of it we expect to be more local. But the question is how local? Lexical scope is an absolute pleasure for many circumstances, but when we’re talking about variables that we want to be available to multiple functions but with unique values per thread, lexical variables get messy. I suppose one could wrap a closure around a function that returned functions…but that methodology doesn’t scale all that well (at least not to my tastes).

So where does that leave us? The OOP cult would have us use objects, but one cannot always listen to them or one gets the ugliness of Java. Another alternative is the tiring practice of passing the values as arguments to every function that needs them. But we needn’t worry; the Lisp hackers of old foresaw even this, and thus we have the magic of dynamic scoping. All we need to do is (declare (special *foobar*)), and presto-chango, *foobar* is dynamically-scoped and therefore available to all functions we call. Richard Stallman presented this issue quite well in his 1981 paper, EMACS: The Extensible, Customizable Display Editor:

Some language designers believe that dynamic binding should be avoided, and explicit argument passing should be used instead. Imagine that function A binds the variable FOO, and calls the function B, which calls the function C, and C uses the value of FOO. Supposedly A should pass the value as an argument to B, which should pass it as an argument to C.This cannot be done in an extensible system, however, because the author of the system cannot know what all the parameters will be. Imagine that the functions A and C are part of a user extension, while B is part of the standard system. The variable FOO does not exist in the standard system; it is part of the extension. To use explicit argument passing would require adding a new argument to B, which means rewriting B and everything that calls B. In the most common case, B is the editor command dispatcher loop, which is called from an awful number of places.What’s worse, C must also be passed an additional argument. B doesn’t refer to C by name (C did not exist when B was written). It probably finds a pointer to C in the command dispatch table. This means that the same call which sometimes calls C might equally well call any editor command definition. So all the editing commands must be rewritten to accept and ignore the additional argument. By now, none of the original system is left!

How many programming languages can you name that allow the programmer to modify scoping rules? Not many. I’m sure someone is screaming right now that this is the most dangerous software engineering abuse they have ever heard of, but those who have experience with Common Lisp know better. When we see *variables-named-like-this* we expect dynamic scoping, and if there are no surprises, there are no problems.

Today I started seeing funky MySQL errors in my Hunchentoot logfile: "Error 2014 / Commands out of sync; You can't run this command now". At first I was baffled. But then I realized that my PHP-accustomed mind had (stupidly) caused me to make my database connection global without giving any thought to thread-safety. I suppose my threads were taking turns shoving partial SQL commands down the socket. This hadn’t been a concern in the past, but now my web traffic is starting to pick up a little, so the connections actually overlap. Once I realized what I was doing, I had to do a little research on scoping in threaded Common Lisp applications. I haven’t had much experience with the subject, but the CL-Cookbook offers a great explanation. So the solution (eventually) was to scope the database connection variable dynamically around the functions in each thread. Now things are quiet…