Closing your Database Connections
Posted in Code - C# by MidniteBlogger on April 5th, 2008 Sometimes working with a managed language like C# can make you lazy. Having cut my programmers teeth on C++, I learned very early that if you “new” something you must also “delete” it. Forget and you get a memory leak.
After moving to C#, however, I occasionally become complacent and begin to think that the garbage collector (GC) will solve all wrongs with my coding style. I re-learned that lesson the hard way with database connections recently, and almost had a site shut down because of it. Perhaps that experience will save you some pain.
Managed code is awesome. You can “new” and allocate all kinds of resources however you want and the GC will just clean up for you. Right? Well, not exactly. It is true that the GC will free “new”ed memory once all references to it are released. The problem (especially with a web app on a busy site) is that by the time the finalizer queue runs, the server box could be out of a scarce resource like database connections and further requests will fail often causing the site to block for extended periods of time.
Let me give you an example. Let’s take a class derived from DbConnection like SqlConnection (for SQL Server databases) or OdbcConnection (for generic connections to databases like MySQL). We’ll use it by creating our own derived class and holding an instance of the connection as a class member. Just to be safe, we’ll let the finalizer close the DB connection for us (since database connections don’t tend to close themselves) so we don’t leak any resources.
Good idea? Let’s see:
public class DBUtilities
{
private SqlConnection m_dbSqlConn;
~DBUtilities()
{
// Our safety net?
if (m_dbSqlConn != null)
m_dbSqlConn.Close();
}
public SqlConnection GetSqlConnection()
{
try
{
m_dbSqlConn = new SqlConnection();
m_dbSqlConn.ConnectionString = "MyConnectionString";
m_dbSqlConn.Open();
if (m_dbSqlConn.State != ConnectionState.Open)
return null;
}
catch (Exception e) {}
return m_dbSqlConn;
}
}
//Use the class:
void UseDatabase()
{
DBUtilities dbu = new DBUtilities();
SqlConnection sqlConn = dbu.GetSqlConnection();
SqlCommand cmd = new SqlCommand("Select * from aTable", sqlConn);
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
//Do something with the data
.....
dr.Close();
}
Cool. The finalizer for DBUtilities will close the database connection for us, won’t it?
Well, sure, but the important question is “when?”. The answer is “whenever the GC gets around to it.” That could be several seconds later, or longer. In my case, the games web site (The best online games) where I was using the code above would simply start blocking after a few database requests. Bad news. It gets worse.
The hosting company (CrystalTech) I was using at the time called me with a problem and an ultimatum. The problem was that my site was hogging resources (I had to figure out that it was database resources) and the ultimatum was that they were shutting off my site until I could prove to them that it was well-behaved again.
Scramble. Scramble.
Fortunately it didn’t take too long to dawn on me that the scarce resource was the database connections, and doing something as simple as calling
sqlConn.Close();
when done with the SqlDataReader reads and taking the Close() call out of the class finalizer solved the entire problem. Hard to believe? It was for me, and it was a hard lesson in non-deterministic finalization.
I think calling Dispose on the connection would have the same effect, but have not proven that yet.
In the end, I switched the site to Godaddy because of CrystalTech’s non-helpful attitude and now everything is humming along smoothly for the happy games players.
midniteblogger
