AIS Server Memory Leaks When Using ADO.NET Clients

[DrorHarari]

Several developers reported that when they test applications that use ADO.NET clients to access AIS servers they see indications of memory leaks on the AIS server that eventually causes allocation failures or otherwise excessive server load.

As it happens, there is a common programming error when using ADO.NET which is causing this problem. In this short article we'll discuss the problem and the easy way to resolve it. This problem is applicable to any ADO.NET provider and specifically to:

• The Microsoft ADO.NET provider for OLEDB providers
• The Microsoft ADO.NET provider for ODBC providers
• The Attunity ADO.NET provider

The problem, in a nutshell, is manifested by accomulation of 'QuerySpec' objects on the server (which in the Attunity speak means 'prepared statements') and some times other types of objects (e.g, cursors or rowsets in the Attunity speak).

The problem exists for both C# and VB.NET and it stems from the fact that the .NET architecture does not use reference counting like the COM architecture (and its ADO library) so when a reference to an object in the code goes out of scope, it is not necessarily released and as a result, related resources on the server are also kept. Since the reference is gone, there is no direct way to release these resources and eventually the server process exhausts its resources and terminates.

An example may make the problem clearer. The following is an excerpt from a module called OleHelper.c:

public static OleDbDataReader ExecuteReader(string constr, string sql, params OleDbParameter[] parms)
{
    OleDbConnection conn = new OleDbConnection(constr); 
    
    try
    {
        OleDbCommand cmd = new OleDbCommand();

        if (conn.State != ConnectionState.Open)
            conn.Open();
 
        cmd.Connection = conn;
        cmd.CommandText = sq;;

        if (parms != null)
        {
            foreach (OleDbParameter parm in parms)
                cmd.Parameters.Add(parm);
        }

        OleDbDataReader rdr =  cmd.ExecuteReader(CommandBehavior.CloseConnection);
        
        return rdr;
    }
    catch (OleDbException ex)
    {
        // Log something...
        throw new ServerException(ServerErrorCode.DataError, ex.ToString() , ErrorSeverity.Error);
    }
}

The problem with this code is that when the function returns and the 'cmd' object goes out of scope, the server resources associated with that object remain held until it gets garbage collected by the .NET framework. This may happen after long time during which time server resources are accomulated.

The general guideline in the .NET framework is that objects that offer a Dispose() method must be disposed explicitly before going out of scope. In the case of the AIS ADO.NET provider, objects the following classes shuold be disposed:

• AisCommand
• AisCommandBuilder
• AisConnection
• AisDataReader
• AisTransaction

In the case of the Microsoft ADO.NET provider for OLEDB providers, objects the following classes shuold be disposed:

• OleDbCommand
• OleDbCommandBuilder
• OleDbConnection
• OleDbDataAdapter
• OleDbDataReader
• OleDbTransaction

The code above fails indeed to call the Dispose method on the 'cmd' object but, fortunately, there is a very simple way to make sure the object is properly destructed.

The above code can be written correctly as follows:

public static OleDbDataReader ExecuteReader(string constr, string sql, params OleDbParameter[] parms)
{
    try
    {
        using (OleDbConnection conn = new OleDbConnection(constr))
        {    
            if (conn.State != ConnectionState.Open)
                conn.Open();
     
            using (OleDbCommand cmd = new OleDbCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = sql;
        
                if (parms != null)
                {
                    foreach (OleDbParameter parm in parms)
                        cmd.Parameters.Add(parm);
                }
        
                OleDbDataReader rdr =  cmd.ExecuteReader(CommandBehavior.CloseConnection);
                
                return rdr;
            }
        }
    }
    catch (OleDbException ex)
    {
        // Log something...
        throw new ServerException(ServerErrorCode.DataError, ex.ToString() , ErrorSeverity.Error);
    }
}

The C# 'using' keyword makes sure that the Dispose() method of 'cmd' (as well as of 'conn') is called when the block is exited. This will, in turn,release the server side QuerySpec objects. The Dispose() method of 'cmd' will be called even in case of an exception inside the 'using' block. Note that since this is OLEDB, connection pooling is in effect so the automatic connection close does not really close the connection and its backend resources. It only moves the connection with its many unclosed commands into the pool to be reused again and again.

In case you work in VB.NET, a similar pattern can be used:

Public Function ExecuteReader(constr As String, sql As String) As OleDbDataReader
    Try
        Using conn As New OleDbConnection(constr)
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If

            Using cmd As New OleDbCommand
                cmd.Connection = conn
                cmd.CommandText = sql

                ExecuteReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            End Using
        End Using

    Catch ex As Exception
        ' Log something
        throw ex;
    End Try
    
End Function