This discussion is archived
7 Replies Latest reply: Oct 5, 2012 1:10 AM by Tolls RSS

Connection object is getting closed before the stored procedure is complete

966025 Newbie
Currently Being Moderated
Hi Everyone,
I am facing an issue where by the java connection object is closed before the stored procedure it is connected to is complete.
I am not sure if the fault is in SP or Connection pool.
After spending some time, i could able to figure out that the procedure is taking a tad more time for processing as there are over 1000 records in the database tables it is dealing with.
Would that be a potential cause ? or Am i required to handle it in Java only ?

I want to know what could be possible causes for this issue ?


Please Help.

FYI,
The following are the logs which says,
XYZ (Stored Procedure) : Start Time is 1349217771302 Procedure started here
INFO >2012-10-02 18:43:09,935 [ConnectionPool]: Closing connection: DataSource [ABC](684)
INFO >2012-10-02 18:46:03,512 DAO[main]: XYZ : End Time is 1349217963512 Procedure ended here

Thanks in Advance.
  • 1. Re: Connection object is getting closed before the stored procedure is complete
    EJP Guru
    Currently Being Moderated
    How do you know it's the same connection? Is the procedure failing?

    1000 records is peanuts.
  • 2. Re: Connection object is getting closed before the stored procedure is complete
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!
    >
    I am facing an issue where by the java connection object is closed before the stored procedure it is connected to is complete.
    I am not sure if the fault is in SP or Connection pool.
    After spending some time, i could able to figure out that the procedure is taking a tad more time for processing as there are over 1000 records in the database tables it is dealing with.
    Would that be a potential cause ? or Am i required to handle it in Java only ?

    I want to know what could be possible causes for this issue ?
    >
    Could you help anyone with a problem like that if they didn't provide any code for you to look at?

    We have no idea what the Java code is doing, what kind of database or procedure you are talking about, what the procedure is doing or what kind of connection or connection pool you are using.

    Post the code and a detailed explanation of what you are trying to do.
  • 3. Re: Connection object is getting closed before the stored procedure is complete
    Joe Weinstein Expert
    Currently Being Moderated
    The error message seems to imply that the connection pool is configured to take back the connection after some fixed time, no matter whether it is in use or not. Simpleminded pool IMHO, but your answer is probably to read up on how your pool is configured and alter it to allow you whatever time you need...
  • 4. Re: Connection object is getting closed before the stored procedure is complete
    966025 Newbie
    Currently Being Moderated
    Hi ,
    Thank you all for your quick response.

    Well it's my bad i dint provide you any code to look into thinking that i am dealt with a gen issue and also i am too paranoid to post any code i am dealing with in the forum (i am sorry).
    But here is some information for you,
    Database : Oracle 10g
    Java: 1.5 version

    We are using only One connection object for the entire java backend process .


    The SP is of over 1000 lines of code which for obvious reasons i can't past it here but this morning i figured out an issue in SP where by a query taking way more than usual time to execute which led to SP's poor performance and also the reason for why it is taking very long time than usual.
    This query is a simple SELECT query where it is trying fetch over 2000 records from a table of over 3 million records. The execution time is over 30-40 seconds which is the root cause of SP's poor performance.
    When i eliminated this from the logic and ran the query it could able process (inserting huge volume around 5000 records of data) in 1 second instead of 3-4 minutes earlier.
    I tried to replicate this issue (which occurred in our production server) in my local system but no luck as there was no connection issue here but only the substantial time difference.


    We are using a customized connection pool which is as follows,
    I am not sure what's going on here because it seems to be greek and latin to me.
    What we are doing in our DAO is we are using method of the below ConnectionPool.getInstance(SCHEMA) to get the connection object.
    Looking forward to seeking advice from you on how connection pool in general works.
    public class ConnectionPool extends Thread
    {
         private static final ConnectionPool me = new ConnectionPool();
    
         private Hashtable dataSources = new Hashtable();
    
         private static final int MIN_TIMEOUT = 0;
         private long timeOut;
         private Hashtable cons = new Hashtable();
         private Hashtable active = new Hashtable();
        
        private boolean trace = false;
    
         private ConnectionPool()
         {
              registerDataSources();
              this.timeOut = PropertyManager.getIntProperty("connectionPool.timeOut", MIN_TIMEOUT);
              setName("ConnectionPool");
              setPriority(MIN_PRIORITY);
              if (timeOut > 0)
                   start();
         }
    
         private void registerDataSources()
         {
            dataSources.clear();
            
              Properties props = System.getProperties();
              String app = props.getProperty("X", "Y");
    
              for(Enumeration e = props.keys(); e.hasMoreElements();)
              {
                   String key = (String) e.nextElement();
                   if (key.startsWith(app + ".connectionPool.dataSources.") && key.endsWith(".selector"))
                   {
                        String ds = key.substring((app + ".connectionPool.dataSources.").length(), key.length() - ".selector".length());
                        LogManager.logStatus("Registering [" + ds + "] (selector) " + 
                                  props.getProperty(app + ".connectionPool.dataSources." + ds + ".selector"));
                        dataSources.put(ds, 
                             new GenDataSource(
                                  ds, 
                                  props.getProperty(app + ".connectionPool.dataSources." + ds + ".selector")));
                        continue;                    
                   }
                   
                   if (!key.startsWith(app + ".connectionPool.dataSources.") || !key.endsWith(".server"))
                        continue;
    
                   String ds = key.substring((app + ".connectionPool.dataSources.").length(), key.length() - ".server".length());
                   
                   try
                   {
                        LogManager.logStatus("Registering [" + ds + "] " + 
                                  props.getProperty(app + ".connectionPool.dataSources." + ds + ".url"));
                        loadDriver(props.getProperty(app + ".connectionPool.dataSources." + ds + ".driver"));
                   }
                   catch (Exception se)
                   {
                        LogManager.logException(se);
                   }
    
                   GenDataSource genDataSource = new GenDataSource(
                             ds, 
                             props.getProperty(app + ".connectionPool.dataSources." + ds + ".useMatrix", "false").equals("true"),
                             props.getProperty(app + ".connectionPool.dataSources." + ds + ".server"),
                             props.getProperty(app + ".connectionPool.dataSources." + ds + ".url"),
                             props.getProperty(app + ".connectionPool.dataSources." + ds + ".user"),
                             props.getProperty(app + ".connectionPool.dataSources." + ds + ".password"));
                   // Set the schema if schema is defined in settings.xml file
                   if (genDataSource != null && props.getProperty(app + ".connectionPool.dataSources." + ds + ".schema") != null ) {
                        genDataSource.setSchema(props.getProperty(app + ".connectionPool.dataSources." + ds + ".schema"));
                   }
                             
                   dataSources.put(ds, genDataSource);
                        
              }
         }
         
    
    
         public static Connection getConnection(String dataSource) throws SQLException
         {
              GenDataSource ds = (GenDataSource) me.dataSources.get(dataSource);
              
              if (me.timeOut <= 0)
              {
                   if (ds.getSchema() != null ) 
                        return updateSchema ( ds);
                   else
                        return DriverManager.getConnection(ds.url(), ds.user(), ds.password());
    
              }
    
              String key = dataSource;
              Stack free;
              GenPooledConnection pc = null;
    
              synchronized (me)
              {
                   if ((free = (Stack) me.cons.get(key)) == null)
                   {
                        free = new Stack();
                        me.cons.put(key, free);
                   }
    
                   if (!free.empty())
                        pc = (GenPooledConnection) free.pop();
              
                   if (pc == null)
                   {
                        if (ds.getSchema() != null ) 
                             pc = new GenPooledConnection("DataSource [" + key + "]", 
                                                 updateSchema ( ds), free, me.active, me.timeOut, me.trace);
                        else
                             pc = new GenPooledConnection("DataSource [" + key + "]", 
                                       DriverManager.getConnection(ds.url(), ds.user(), ds.password()), free, me.active, me.timeOut, me.trace);
                   }
                   else
                        pc.touch();
              }
              LogManager.logStatus("Using " + pc);
              me.active.put(pc.id(), pc);
              return pc;
    
         }
         
         
         
         public void run()
         {
              for(;;)
              {
                   try
                   {
                        sleep(60 * 1000);
    
                        synchronized (me) {
                        
                             for(Enumeration e = cons.elements(); e.hasMoreElements();)
                             {
                                  Stack stack = (Stack) e.nextElement();
                                  
                                  for (int i = stack.size()-1; i >= 0; i--)
                                  {
                                       GenPooledConnection pc = (GenPooledConnection) stack.elementAt(i);
                                       if (pc.isExpired())
                                            stack.removeElementAt(i);
                                  }
                             }
                        }
                   }
                   catch (Exception e)
                   {
                        GenUtil.reportException(e);
                   }
              }
         }
         
       
         
         private static Connection updateSchema ( GenDataSource ds) throws SQLException {
                        
              Connection con = DriverManager.getConnection(ds.url(), ds.user(), ds.password());
              if (ds.getSchema() != null ) {
                   String sql = "SET SCHEMA  " + ds.getSchema()+ ";";
                   LogManager.logDebugMessage("updating the Schema with sql statement " + sql);
                   PreparedStatement ps = con.prepareStatement(sql);
                   ps.execute();
                   ps.close();
              }
              return con;
              
         }
              
        }
    }
    Thanks.

    Edited by: EJP on 5/10/2012 14:09: added {noformat}
    {noformat} tags. Please use them.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 5. Re: Connection object is getting closed before the stored procedure is complete
    rp0428 Guru
    Currently Being Moderated
    >
    The SP is of over 1000 lines of code
    >
    That's one sure sign that something is wrong. An SP shouldn't need to be more than a couple of pages so yours needs to be refactored and modularized. That many lines means that it is trying to do to much. But that's another story.
    >
    We are using a customized connection pool which is as follows,
    >
    Well thanks (I think) for posting the code. But that code has a lot of errors in it that would keep it from even compiling properly.

    Please edit your post and add \
     tags on the line before and the line after the code to preserve formatting and make the code readable.
    
    This code
    public static Connection getConnection(String dataSource) throws SQLException
    {
    GenDataSource ds = (GenDataSource) me.dataSources.get(dataSource);


    if (me.timeOut <= 0)
    {
    if (ds.getSchema() != null )
    return updateSchema ( ds);
    else
    return DriverManager.getConnection(ds.url(), ds.user(), ds.password());

    }

    String key = dataSource;
    Stack free;
    GenPooledConnection pc = null;
    has an IF statement where, unless I overlooked a brace, each possible branch performs a RETURN. So the code following it (starting with 'String key ...') is unreachable and will never be executed.
    
    So either you posted the wrong code, you posted edited code that is missing some pieces or my eyes are missing something.
    
    Then later your 'updateSchema' method constructs a sql statement that is invalid.
    private static Connection updateSchema ( GenDataSource ds) throws SQLException {

    Connection con = DriverManager.getConnection(ds.url(), ds.user(), ds.password());
    if (ds.getSchema() != null ) {
    String sql = "SET SCHEMA " + ds.getSchema()+ ";";
    LogManager.logDebugMessage("updating the Schema with sql statement " + sql);
    PreparedStatement ps = con.prepareStatement(sql);
    ps.execute();
    ps.close();
    }
    return con;
    }
    There is no SET SCHEMA command that I am aware of. There is an ALTER SESSION SET CURRENT_SCHEMA command.
    
    See ALTER SESSION in the SQL Language doc
    http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_2013.htm
    {quote}
    CURRENT_SCHEMA 
    Syntax:
    
    CURRENT_SCHEMA = schema
    The CURRENT_SCHEMA parameter changes the current schema of the session to the specified schema. Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema. The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMA statement.
    
    This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session.
    {quote}
    So it is hard to follow the code due to the error but am still looking at it.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 6. Re: Connection object is getting closed before the stored procedure is complete
    EJP Guru
    Currently Being Moderated
    We are using only One connection object for the entire java backend process .
    No, you are using a home-grown connection pool, see below.
    We are using a customized connection pool
    Why?
    Looking forward to seeking advice from you on how connection pool in general works.
    If you don't know how they work why are you writing one?

    I strongly urge you not to do this. Use one of the many existing ones, e.g. the Apache DBCP. What you've written so far, apart from not compiling as noted by @rp0428, seems to have a very simplistic model of just timing out connections regardless of whether they are currently in use or not. A properly written connection pool implementation just won't do that, and the entire problem you started with will just disappear.
  • 7. Re: Connection object is getting closed before the stored procedure is complete
    Tolls Journeyer
    Currently Being Moderated
    rp0428 wrote:
    has an IF statement where, unless I overlooked a brace, each possible branch performs a RETURN. So the code following it (starting with 'String key ...') is unreachable and will never be executed.
    public static Connection getConnection(String dataSource) throws SQLException {
       GenDataSource ds = (GenDataSource) me.dataSources.get(dataSource);
    
    
       if (me.timeOut <= 0) {
          if (ds.getSchema() != null ) 
             return updateSchema ( ds);
          else
             return DriverManager.getConnection(ds.url(), ds.user(), ds.password());
       }
    
       String key = dataSource;
       Stack free;
       GenPooledConnection pc = null;
    Does that help?
    If me.timeOut > 0 then it will execute the code at the end.

    (I hate reading unformatted code).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points