Close Your Database Connections! And Use Connection Pools While You're at It

A wise man once said to me, "If you open a resource, make sure you close it when you're done."

Notice, he didn't say, "If you open a resource, make sure you close it when you're done, but if you don't, it's ok since the library/driver/runtime you're working with will eventually close it automatically."

If I had a nickel for every time I've seen resources opened and just assumed to be closed/eventually timed out, I'd be a rich man.  No example is more acute than that of database connections.

We are all guilty of doing it, but the effects of improperly closed database connections can be felt very strongly... and when you least expect or want them.  Everything will be humming along nicely in your newly-released production system until sometime in the not-too-distant future you get a panicked call from a customer/client wondering why their application/system isn't working anymore.  Or, possibly worse, the issue is intermittent with some users able to make use of the system while others cannot.

There's good news, though.  These kinds of issues can be dealt with using a little foresight and discipline.

Connection Pools Rule

Whenever possible, don't rely on your database's own raw connection sources.  These are typically configured in the database itself and don't lend themselves well to scaling or to configuring for different clients, which can be especially painful in multi-tenant situations.

Connection pools' greatest strengths lie in the ability to manage connections efficiently (thus freeing up the database to do more important things like query and serve up data) and to set a configurable maximum number of connections from which your application can draw.

There is no excuse not to use a connection pool.

Close What You Open When You Write the Open

(Courtesy: frinkiac.com)

Put more simply (but far less comically), write that connection.close(); a few lines below the connection.open(); when you first write that open command.  Write the intervening code between the two lines.  This way, you're forced to see and remember the connection close.

Be Exceptional

The use of try/catch blocks is a common pattern when dealing with resources.  Database connections are no different.  You can't always know when or where your code is going to fail, but you should still be prepared for it.

Make sure that you release any potentially open connections inside the appropriate catch blocks.  It's also a good idea to first check to see if you have a valid, open connection before trying to close it, lest you invoke another exception!

Testing, Diagnosing, and Troubleshooting

Most RDBMS systems have the ability to help you see if your code is properly closing the connection it is using.  For example, in MySQL, all you need to do is connect to the server via the command line (or your favorite client) as an admin (or user with appropriate permissions) and type,

SHOW PROCESSLIST;

... which can reveal information such as the following:

+----+------+-----------------+--------+---------+------+-------+------------------+
| Id | User | Host            | db     | Command | Time | State | Info             |
+----+------+-----------------+--------+---------+------+-------+------------------+
|  4 | root | localhost       | webapp | Query   |    0 | NULL  | show processlist | 
|  6 | root | localhost:3306  | webapp | Sleep   |  208 |       | NULL             | 
|  8 | root | localhost:3307  | webapp | Sleep   |  208 |       | NULL             | 
|  9 | root | localhost:3309  | webapp | Sleep   |  208 |       | NULL             | 
+----+------+-----------------+--------+---------+------+-------+------------------+
4 rows in set (0.00 sec)

From this information, we can quickly tell that not all connections are being properly closed.

I am frequently amazed (or at least very surprised) at the number of developers I have come across that aren't, at least, aware of this command.  Know it, use it, and love it!

Conclusion

While it may be true that libraries are getting better and smarter at managing these database connections, it's still no excuse for being lazy, so don't think that it's ok to just skip over these tips.  Resource leaks are the scourge of the development world.

Keep your customers happy by making sure that there are always enough connections available to service requests. And by keeping the number of open connections to a minimum, you also help improve your database's performance.

So, remember to always close those open database connections!