Connection problem and Oracle

ro
2007-04-07
2013-04-03
  • ro

    ro - 2007-04-07

    Hi Morten,

    I recently discoverred that Gentle open connections without closing them at various places. Especially the .NET 2.0 ODP.NET drivers Oracle has a lot of problems with this. I changed the code so the connection is closed when openend in the method itself. Are you interested in the code?

    grtz

    Roger

     
    • Nobody/Anonymous

      Is this Gentle 1 or 2?
      Could you post some details here or in mailing list?

      regards

      Drazen

       
    • ro

      ro - 2007-04-17

      Gentle 1. last version from svn
      One of the methods in PersistenceBroker.cs i changed:

      public SqlResult Retrieve( Type type, Key key, IDbConnection conn, IDbTransaction tr )
      {
          bool connectionCreated = false;
          SqlStatement stmt = GetRetrieveStatement( type, key );
          // connections are supplied from outside when in a transaction or executing batch queries
          if (tr != null)
          {  
                 conn = tr.Connection;
          }
          else if (conn == null)
          {
              conn = stmt.SessionBroker.Provider.GetConnection();
              connectionCreated = true;
          }
          //conn = tr != null ? tr.Connection : ( conn != null ? conn : stmt.SessionBroker.Provider.GetConnection() );
          SqlResult sr = stmt.Execute( conn, tr );
          if (connectionCreated)
          {
              conn.Close();
          }
          // require that operation succeeded and a valid result
          if (IsPrimaryKeyForType(type, key))
          {
              Check.Verify( sr.ErrorCode == 0, Error.NoSuchRecord, type, key, sr.Error );
              Check.Verify( sr.RowsContained == 1, Error.UnexpectedRowCount, sr.RowsContained, 1 );
          }
          return sr;
      }

       
      • Bennett Yip

        Bennett Yip - 2007-04-24

        did this solution solve your problem?

         
    • Nobody/Anonymous

      We have experienced something similar with SQL Server - we'll end up with multiple connections (I've seen 6) for a single domain.  Haven't had time to investigate further.

      Bill Sorensen
      Integrated DNA Technologies

       
    • Nobody/Anonymous

      Hm, I have Asp.Net 1. application on Oracle 10g (MS driver) with number of users and I did not see more than 15 connections at peek time (about 100 users simultaneous). I expected more.

      Are you talking about winforms or Asp.Net application?

       
      • Martin Marconcini

        I've performed a simple test and with WinForms/SQL2000 i only get "3-4" connections even when performing way too many CRUD operations.

         
        • Bennett Yip

          Bennett Yip - 2007-04-20

          I performed a test with WinForms/MySQL. I run the program in 3 instances. At the same moment, there is only 4 connections with MySQL. One of them is used by the monitoring program, therefore each program use 1 connection only without transaction.

          The settings in gentle.config:

          <?xml version="1.0" encoding="utf-8" ?>
          <Gentle.Framework>
              <Options>
                  <CommandTimeout>120</CommandTimeout>
                  <ConcurrencyControl>false</ConcurrencyControl>
                  <MasterDefinition>Attributes</MasterDefinition>
                  <Analyzer>
                      <Level>Full</Level>
                      <Silent>false</Silent>
                  </Analyzer>

                  <Cache>
                      <DefaultStrategy>Temporary</DefaultStrategy>
                      <CacheStatements>true</CacheStatements>
                      <CacheObjects>false</CacheObjects>
                      <SkipQueryExecution>true</SkipQueryExecution>
                      <UniqingScope>Thread</UniqingScope>
                  </Cache>

                  <Logging>
                      <Verbosity>Debug</Verbosity>
                      <Frailty>Debug</Frailty>
                      <Category name="All" enabled="false" />
                      <Category name="StatementExecution" enabled="true" />
                      <Category name="Cache" enabled="true" />
                      <Category name="Metadata" enabled="true" />
                  </Logging>
              </Options>
              <DefaultProvider name="MySQL" connectionString="Server=localhost;Port=3306;Uid=root;Pwd=xxxxxx;Database=gentlenet;" />
              <Providers>
              <Provider name="MySQL"  assembly="Gentle.Provider.MySQL" />
              </Providers>
          </Gentle.Framework>

           
    • Bennett Yip

      Bennett Yip - 2007-04-20

      For Gentle.NET 1.2.X

      For CRUD operation with single object: (tr is null)
      In PersistenceBroker.cs class, when it gets connection with stmt.SessionBroker.Provider.GetConnection(), it is always followed by stmt.Execute(conn,tr).
      Let's look into Execute method in SqlStatement.cs:

      internal SqlResult Execute( IDbConnection conn, IDbTransaction tr )
      {
      ...
        finally
        {
          // close the database connection (only if not in transaction)
          if( conn != null && conn.State == ConnectionState.Open && tr == null )
          {
            conn.Close();
          }
          // only if not in transaction; would otherwise break with PostgreSQL (not permitted)
          if( tr == null )
          {
            // clear the connection reference
            try
            {
              cmd.Connection = null;
            }
            catch
            {
              // ignore errors here.. like for the SQLite-provider which complains for no good reason
            }
            // clear the transaction reference
            cmd.Transaction = null;
          }
        }
      ... 
      }

      The connection should be closed properly in this case.

      If it is running under transaction, it will not close the connection here.

      Do we need to clear the connection and transaction reference with the cmd object under transaction? Is it necessary to do it?

       
      • ro

        ro - 2007-04-24

        I missed this one. Too bad, this isn't the problem then. The only place a cmd.Execute.. is done is in the JetAnalyser and in the PersistenceBroker.Execute. In this codes the connection is closed. I don't understand where my connections get lost in gentle. Maybe there are some Thread.Abort's (a known Oracle problem, you have to clear the connections in that case).
        Does anyone know were to look besides cmd.Execute???

        Grtz

        Roger

         
        • Bennett Yip

          Bennett Yip - 2007-04-25

          Did your program use multi-threads within one transaction? or do it just always use single objects without transaction?

          What's your settings in gentle.conf?

           
    • Bennett Yip

      Bennett Yip - 2007-04-20

      CRUD operations under Transaction

      As mentioned in "CRUD operation with single object", it will not close connection when Execute method is called under transaction.

      so, when will it close the connection?
      The connection will closed after you call Transaction.Commit or Transaction.Rollback method.
      Both methods call Dispose() method and the transaction and connection object will be closed inside Displose(bool isDisposing) method.

              public void Commit(){
                  try{
                      if( IsInitialized ){
                          dbTransaction.Commit();
                      }
                  }finally{
                      Dispose();
                  }
              }

              public void Rollback(){
                  try{
                      if( IsInitialized ){
                          dbTransaction.Rollback();
                      }
                  }finally{
                      Dispose();
                  }
              }

              public void Dispose()
              {
                  // called by user - release all resources
                  Dispose( true );
                  ...
              }
             
              protected virtual void Dispose( bool isDisposing )
              {
                  // check to see if we have any resources to free
                  if( IsInitialized ){
                      if( isDisposing ){
                          try{
                              dbConnection.Close();
                              dbConnection = null;
                              dbTransaction.Dispose();
                              dbTransaction = null;                           
                          } catch( InvalidOperationException ){}
                          catch( Exception e ){...}
                      }
                  }
                  else if( dbConnection != null && dbConnection.State == ConnectionState.Open ){
                      // connection but no transaction - this should never happen, but better safe than sorry
                      dbConnection.Close();
                  }
              }

       
    • Nobody/Anonymous

      Yes by leaving the ODP driver and using the Oracle Driver.
      Another problem: seems that the OracleAnalyzer.AddParameter uses the wrong typecasting. When correcting this an oci error occurs ;(

       
    • ro

      ro - 2007-05-11

      Fixed:
      The Microsoft Oracle driver is more stable then the ODP version ;) We had to fix some bugs in it thought to get it working. Fixes submitted on Jira:

      http://www.mertner.com/jira/browse/GOPF-236

       
    • Nobody/Anonymous

      Hi Morten,

      Can you or another developer add the fix in 236 in svn ??

      Tnxs,

      Roger

       

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks