Memory leak with Enterprise Library 4 Data block and Execute Reader

Yesterday we had a very strange problem in production with an old sproc-based ASP.NET web application. We had just switched from using a handful of “SqlHelper” classes to the Microsoft Enterprise Library Data Access Application block (DAAB).

Strangely though, after releasing these changes to production, the application became plagued by connection and memory leaks, causing connection pool overflows (over 200 connections to the SQL box at one point) and the IIS application pool to fall over every few minutes.

Memory leaks are notoriously difficult to diagnose. We eventually were able to narrow it down to this offending piece of code, used for running FOR XML queries:

public XmlDocument ExecuteNativeXml(string sprocName, params object[] parameters)
{
    SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();

    using (DbCommand command = db.GetStoredProcCommand(sprocName, parameters))
    {
        XmlDocument document = new XmlDocument();

        using (XmlReader reader = db.ExecuteXmlReader(command))
            document.Load(reader);

        return document;
    }
}

Can you spot the memory leak? Everything that implements IDisposable is wrapped in a using block, and DAAB takes care of opening/closing SqlConnections — our code doesn’t have any contact with them at all. So what’s the problem then?

This article about connection pools in SQLMag from 2003 explains it:

…My test application shows that even when you use this [CommandBehavior.CloseConnection] option, if you don’t explicitly close the DataReader (or SqlConnection), the pool overflows. The application then throws an exception when the code requests more connections than the pool will hold.

Some developers insist that if you set the CommandBehavior.CloseConnection option, the DataReader and its associated connection close automatically when the DataReader finishes reading the data. Those developers are partially right—but the option works this way only when you’re using a complex bound control in an ASP.NET Web application. Looping through a DataReader result set to the end of its rowset (that is, when —the DataReader’s Read method— returns false) isn’t enough to trigger automatic connection closing. However, if you bind to a complex bound control such as the DataGrid, the control closes the DataReader and the connection— but only if you’ve set the CommandBehavior.CloseConnection option.

If you execute a query by using another Execute method (e.g., ExecuteScalar, ExecuteNonQuery, ExecuteXMLReader), you are responsible for opening the SqlConnection object and, more importantly, closing it when the query finishes. If you miss a close, orphaned connections quickly accumulate.

The fix is pretty bit ugly and unexpected — you have to reach inside the DBCommand and explicitly close its connection yourself:

public XmlDocument ExecuteNativeXml(string sprocName, params object[] parameters)
{
    SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();

    using (DbCommand command = db.GetStoredProcCommand(sprocName, parameters))
    {
        XmlDocument document = new XmlDocument();

        using (XmlReader reader = db.ExecuteXmlReader(command))
            document.Load(reader);

        // If you do not explicitly close the connection here, it will leak!
        if (command.Connection.State == ConnectionState.Open)
            command.Connection.Close();

        return document;
    }
}

So the bug was not the fault of DAAB, but it was caused by its use of CommandBehavior.CloseConnection — the recommended technique. A nice trap for young players!

8 thoughts on “Memory leak with Enterprise Library 4 Data block and Execute Reader

  1. Thanks Richard,
    This has been an ongoing problem for me, and I think this will fix it. You used a data reader here, but do all dbcommandwrapper’s have this problem?
    Jamison White

  2. Here is the generated code instead the Repository of the Web Service Software Factory =>
    ///
    /// Find all objects that match the given criteria.
    ///
    /// Type of object used to identify
    /// the objects to find.
    /// Factory object that can turn the
    /// identity object into the appropriate DbCommand.
    /// Factory object that can turn the
    /// returned result set into a domain object.
    /// Object that identifies which items to get.
    ///
    public List Find(
    ISelectionFactory selectionFactory,
    IDomainObjectFactory domainObjectFactory,
    TIdentity identity)
    {
    List results = new List();

    using (DbCommand command = selectionFactory.ConstructSelectCommand(db, identity))
    {
    using (IDataReader rdr = db.ExecuteReader(command))
    {
    while (rdr.Read())
    {
    results.Add(domainObjectFactory.Construct(rdr));
    }
    }
    }
    return results;
    }

    Wouldn’t this do the same?

  3. I just ran a quick test and it used one connection the entire time and once it was finished it quickly released it. No connection pool overflowing at all?

    for (int i = 0; i < 10000; i++)
    {
    Database database = DatabaseFactory.CreateDatabase("ConnectionString");
    using (DbCommand command = database.GetStoredProcCommand("[TestProc]"))
    {
    using (IDataReader rdr = database.ExecuteReader(command))
    {
    while (rdr.Read())
    {
    Console.WriteLine(rdr.GetString(0));
    }

    // // If you do not explicitly close the connection here, it will leak!
    // if (command.Connection.State == ConnectionState.Open)
    // {
    // command.Connection.Close();
    // }
    }
    }
    }

Comments are closed.