9 Replies Latest reply on Oct 11, 2002 3:55 PM by jbnewb

    No ManagedConnection - oracle 9i

    jbnewb

      Hi,
      I am using JBoss 3.0.0 - I run into the No managed connection available problem even when there is a single user and the datasource's getConnection() is called more than the max-size attribute. I have read many posts about this - but seeen no solution. One was to change the max-size to 0 - but that means unlimited connections - that is not a good long term soln.


      Has there been any patch ? work-around that? Here is the piece from my oracle-sevice.xml that configures the managed connections.

      Please, please help me - we are closing all connections in a finally block... seems like they do not get returned to the pool


      <depends optional-attribute-name="ManagedConnectionPool">
      <!--embedded mbean-->


      5
      50
      5000
      15
      ByContainer



      <depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager

      <depends optional-attribute-name="JaasSecurityManagerService">jboss.security:name=JaasSecurityManager

      java:/TransactionManager

      <!--make the rar deploy! hack till better deployment-->
      jboss.jca:service=RARDeployer


      regards,
      jbnewb

        • 1. Re: No ManagedConnection - oracle 9i
          wchao

          Are you making certain to close your Statement, ResultSet, and Connection objects? I had connections not getting released even though I closed all my ResultSet and Connection objects. I had forgotten to close the Statement objects, and once I did that, it fixed the problem. It sounds like you are just closing the Connection objects.

          • 2. Re: No ManagedConnection - oracle 9i
            jbnewb

            Hi,
            Thanks for the reply . Yes - In every piece of code that makes any use of connections, in a finally block - the resultset, statement and the connection are being closed. In fact that is mandatory in our coding spec

            thanks

            • 3. Re: No ManagedConnection - oracle 9i
              davidjencks

              Well, the testsuite runs so there must be something slightly unusual in your code. The only thing I can think of right now is a problem that occurs if your bean class implements equals and hashcode. This problem is fixed in 3.0.2, 3.2, and 4. Otherwise you might try 3.0.2 anyway.

              • 4. Re: No ManagedConnection - oracle 9i
                jbnewb

                Thanks for the reply,
                We are not using EJB's - we are just using the datasource - i.e. the one bound at java:/<ds_name> from within the context of the app server from classes.

                Since we have a central class that retrieves / returns the connections obtained, the logging statements indicate that the close on the connections is being called and the count remains at 0 ( +1 for getConnection and -1 for close connection)

                Would playing about with the blockingTimeMillis do the trick ? Or setting max connections to a high number with high blocking time ?

                Any help is greatly appreciated - I am using 3.0.2 with Jetty

                regards

                • 5. Re: No ManagedConnection - oracle 9i
                  davidjencks

                  Are you using transactions in any way? Can you provide an example or more of an outline of your code? How are you getting connections and what is using them? Are you trying to use a transaction or connection in more than one thread?

                  • 6. Re: No ManagedConnection - oracle 9i
                    jbnewb

                    Hi,
                    Here is a skeleton of our code - Please let me know If we are making a mistake

                    The code that uses the connection
                    ----------------------------------
                    Connection _conn = null;
                    PreparedStatement _pst = null;
                    ResultSet _rs = null;
                    try{
                    _conn = DBConnection.getConnection();
                    //business logic with prepared statement etc.
                    }catch(SQLException _sqle){
                    //log some stuff here
                    }finally{
                    DBConnection.close(_rs, _pst, _conn);
                    }

                    DBConnection is the class that fetches the connection from the datasource - Here is the outline of the DBConnection class

                    public class DBConnection {


                    /**
                    * This method returns a connection object from the datasource created by the app server
                    * @return Connection object or null. Throws an exception in case there are problems accessing the ds
                    * @throws Exception
                    */
                    public static synchronized Connection getConnection() throws Exception{
                    if( null == dataSource_ ){
                    init();
                    }
                    //System.out.println("DATASOURCE IS " + dataSource_);
                    incCount();
                    return dataSource_.getConnection();
                    }

                    private static void init() throws Exception{
                    Connection _conn = null;
                    InitialContext _ctx = null;
                    DataSource _ds = null;
                    try{
                    _ctx = new InitialContext();
                    //System.out.println("Context is " + _ctx);

                    _ds = (DataSource)_ctx.lookup(Constants.DS_NAME);
                    if( null != _ds ){
                    dataSource_ = _ds;
                    }
                    _conn = dataSource_.getConnection();
                    }catch(NamingException _ne){
                    //to log the exception here as fatal
                    _ne.printStackTrace();
                    throw _ne;
                    }catch(SQLException _sqle){
                    //to log the exception here as fatal
                    _sqle.printStackTrace();
                    throw _sqle;
                    }finally{
                    _ctx = null;
                    _ds = null;
                    }
                    }

                    private static synchronized void incCount(){
                    count_++;
                    //log it here
                    }

                    private static synchronized void decCount(){
                    count_--;
                    //log it here
                    }

                    public static void close(ResultSet rs, Statement st, Connection conn){
                    try{
                    decCount();
                    }

                    finally{

                    if( null != rs ){
                    try{
                    rs.close();
                    }catch(SQLException _sqle){

                    }finally{
                    rs = null;
                    }
                    }
                    if( null != st ){
                    try{
                    st.close();
                    }catch(SQLException _sqle){

                    }finally{
                    st = null;
                    }
                    }
                    if( null != conn ){
                    try{
                    conn.close();
                    }catch(SQLException _sqle){

                    }finally{
                    conn = null;
                    }
                    }
                    }

                    }

                    }

                    • 7. Re: No ManagedConnection - oracle 9i
                      davidjencks

                      From a brief look your code looks fine. This might be a bug I fixed in 3.2 and head. If you want to work from Branch_3_0 source and compile your own jboss you can apply the fix by changing this method in connector/src/main/org/jboss/resource/connectionmanager/BaseConnectionManager2.java

                      public boolean isManagedConnectionFree()
                      {
                      return handleCount == 0;
                      }


                      to

                      public boolean isManagedConnectionFree()
                      {
                      return handles.isEmpty();
                      }

                      • 8. Re: No ManagedConnection - oracle 9i
                        jbnewb

                        Thanks a lot - Have made the change, will let you know after a few more days of testing.

                        thanks again,

                        • 9. Re: No ManagedConnection - oracle 9i
                          jbnewb

                          Hi,
                          it seems to work fine if there is no hot redeployment - It seems that if there is a hot redeployment of the war file (this is in development)- there are greater chances of running out of connections

                          regards