3 Replies Latest reply: Apr 2, 2013 5:21 PM by 995569 RSS

    Memory leak when using CommandBuilder through System.Data.Common

    995569
      Hi everyone,

      I have a truly weird memory leak when using the CommandBuilder through System.Data.Common and the ODP.net factory. As soon as I get the UpdateCommand from the CommandBuilder a new DataReader is open and left hanging until the connection is closed. I know the reader is not closed because I eventually run into a "ORA-01000: maximum open cursors exceeded" error and because by looking at a private list in the connection object I can see the open DataReaders.

      The problem can be reproduced with this simple code:
      DbProviderFactory factory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");

      DbConnection connection = factory.CreateConnection();
      connection.ConnectionString = "Data Source=<SID>;User Id=<USER>;Password=<PWD>";
      connection.Open();

      DataSet ds = new DataSet();
      DbCommand cmd = factory.CreateCommand();
      cmd.CommandText = "SELECT * FROM <SOME_TABLE>";
      cmd.Connection = connection;

      DbDataAdapter adapter = factory.CreateDataAdapter();

      adapter.SelectCommand = cmd;
      adapter.Fill(ds);

      DbCommandBuilder builder = factory.CreateCommandBuilder();
      builder.DataAdapter = adapter;
      adapter.UpdateCommand = (Oracle.DataAccess.Client.OracleCommand)builder.GetUpdateCommand(); //when this line runs a new Cursor (DataReader) is opened and never closed regardless of the calls to Dispose() below
      builder.Dispose();
      adapter.Dispose();
      cmd.Dispose();
      ds.Dispose();

      I can see the open cursor in the database but also in the connection object with this watch expression in Visual Studio:
      ((Oracle.DataAccess.Client.OracleConnection)(connection)).m_DataReaderList.Count
      You should be able to see the above incremented as soon as the GetUpdateCommand() is called

      To make this nicer the problem goes away if I don't use the factory class and have a reference to Oracle.DataAccess:

      Oracle.DataAccess.Client.OracleConnection connection = new Oracle.DataAccess.Client.OracleConnection();
      connection.ConnectionString = "Data Source=<SID>;User Id=<USER>;Password=<PWD>";
      connection.Open();

      DataSet ds = new DataSet();
      Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
      cmd.CommandText = "SELECT * FROM <SOME TABLE>";
      cmd.Connection = connection;


      Oracle.DataAccess.Client.OracleDataAdapter adapter = new Oracle.DataAccess.Client.OracleDataAdapter();
      adapter.SelectCommand = cmd;
      adapter.Fill(ds);

      Oracle.DataAccess.Client.OracleCommandBuilder builder = new Oracle.DataAccess.Client.OracleCommandBuilder();
      builder.DataAdapter = adapter;
      adapter.UpdateCommand = builder.GetUpdateCommand(true);

      I'm using ODAC112021 (Oracle.DataAccess is version 4.112.2.0). I tried ODAC1120320 with the same result.

      If I use the factory just to create the CommandBuilder the problem is still there.

      Any ideas? Any suggestions to write the code using the factory slightly different so I can "avoid" the leak?

      Any idea will be welcomed.

      Thanks.
        • 1. Re: Memory leak when using CommandBuilder through System.Data.Common
          Mark Williams-Oracle
          Hi,

          Based on a quick look at this, the difference in behavior when working with your builder object as a DbCommandBuilder vs an OracleCommandBuilder is due to different code paths (not unexpected).

          Here's an abridged call stack when using the builder as a DbCommandBuilder:
          Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleDataReader.OracleDataReader(Oracle.DataAccess.Client.OracleConnection connection...
          Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleCommand.ExecuteReader(bool requery...
          Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleCommand.ExecuteReader(System.Data.CommandBehavior behavior...
          *** Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleCommandBuilder.GetSchemaTable(System.Data.Common.DbCommand srcCommand... ***
          System.Data.dll!System.Data.Common.DbCommandBuilder.BuildCache(bool closeConnection...
          System.Data.dll!System.Data.Common.DbCommandBuilder.GetUpdateCommand(System.Data.DataRow dataRow...
          I've indicated the call of interest by surrounding it with *** above.

          When working with the builder as an OracleCommandBuilder that method is not invoked.

          It looks like the OracleCommandBuilder.GetSchemaTable method (which is called via DbCommandBuilder.BuildCache) creates an OracleDataReader instance but does not Dispose it.

          This is a bug to me.

          Do you have access to My Oracle Support? If so, I recommend creating an SR with your nice testcase and you can reference this thread and/or the call stack above.

          Regards,
          Mark

          Edited by: Mark Williams on Mar 15, 2013 4:38 PM:

          Unpublished bug created for this issue:
          Bug 16500752 - ORACLEDATAREADER LEAK IN ORACLECOMMANDBUILDER GETSCHEMATABLE
          • 2. Re: Memory leak when using CommandBuilder through System.Data.Common
            662798
            What happens if you use "using" statements instead of expressly calling the Close, Dispose methods on these objects?

            Not sure if it will dispose of this data reader, but may be worth a try. using is supposed to dispose of objects in the correct way, according to the .NET documentation.

            For example...
            using(OracleConnection connection = new OracleConnection())
            {
               using(OracleCommand command = new OracleCommand())
              {
              }
            }
            Here's an example I came across on developer.com
            DbProviderFactory factory = DbProviderFactories.GetFactory
                  (providerName);
                string connectionString = CreateConnectionString
                  factory.CreateConnectionStringBuilder());
                using (DbConnection conn = factory.CreateConnection())
                {            
                  conn.ConnectionString = connectionString;
                  using (DbDataAdapter adapter = factory.CreateDataAdapter())
                  {
                    adapter.SelectCommand = conn.CreateCommand();
                    adapter.SelectCommand.CommandText = sql;
                    DataTable table = new DataTable("Table");
                    adapter.Fill(table);
                    resultsView.DataSource = table;
                    resultsView.DataBind();
                  }
                }
            Edited by: imterpsfan2 on Mar 18, 2013 11:10 AM

            Edited by: imterpsfan2 on Mar 18, 2013 11:28 AM
            • 3. Re: Memory leak when using CommandBuilder through System.Data.Common
              995569
              Oracle filed a new bug: 16584663 - MEMORY LEAK IN ORACLECOMMANDBUILDER OBJECT AND EVENTUALLY RUN INTO ORA-01000. Which seems to be a duplicate of bug 16500752 mentioned by Mark.

              The workaround I'm using is to cast to Oracle.DataAccess.Client.OracleCommandBuilder before calling GetInsertCommand, GetUpdateCommand and GetDeleteCommand. Of course I'm losing the benefits of System.Data.Common.

              Thanks for everyones help.