Announcement Announcement Module
Collapse
No announcement yet.
Connection Pool exhausted...... Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Connection Pool exhausted......

    Hi All,

    "Need Urgent Help"


    I'm using JBoss 4.2., Spring 2.5.6, and Oracle 11.

    My clients invokes my web Service from different locations in the country....

    Its a project based on biometric verification. The webservice is invoked for syncing of data from client machine to server.

    here is the xml bean configuration

    My datasource configuration:

    Code:
    <bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource">
    	<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
            <property name="url" value="jdbc:oracle:thin:@xxx:1521:sid"/>
    	<property name="username" value="XXXXX" />
    	<property name="password" value="XXXXX" />
     <property name="maxActive" value="512" />
    	  <property name="removeAbandoned" value="true" />
    	  <property name="removeAbandonedTimeout" value="60" />
    	  <property name="numTestsPerEvictionRun"><value>-1</value></property> 
    		<property name="timeBetweenEvictionRunsMillis"><value>60000</value></property> 
    		<property name="minEvictableIdleTimeMillis"><value>60000</value></property>
    </bean>



    Source Code Method where updation takes place:
    ====================================

    Code:
    public Message MyUpdateMethod(MappingInputForm formBean) { 
    		Message mesg = new Message();
    		HashMap dbConstants = new HashMap(); 				
    		List beans = formBean.getMappingBeans(); 				
    		Connection conn = null; 		
    		Statement st = null; 		
    		String[] whereClauseColumns = "columns values"; 		
    		try{ 			
    			conn = this.dataSource.getConnection(); 						
    			conn.setAutoCommit(false); 			
    			st = SQL.getPreparedUpdateFor( column values , conn); 			
    			while( st == null){
    				if( conn != null && !conn.isClosed()){ 					
    					conn.close();		
    					conn=null; 				
    				}			
    			conn = this.dataSource.getConnection(); 				
    			log.warn("Retrying for Access List Connection ... as maximum cursors exceeded"); 			
    			}
    			int[] updateCounts = st.executeBatch(); 			
    			int cumulativeUpdateCount = 0; 			
    			for( int i=0; updateCounts != null && i<updateCounts.length; i++){
    				....	
    				some logic .....
    				....
    			} 			
    			conn.commit(); 						
    			mesg = new Message("Updated ("+ cumulativeUpdateCount +") records " , Message.SUCCESS); 		
    			} catch(Exception e) {
    				e.printStackTrace(); 			
    				log.error(e.getMessage() + "------" + DBMessage.parse(e.getMessage())); 			
    				mesg = new Message("Error Update " + DBMessage.parse(e.getMessage()), Message.ERROR); 			
    				try {
    					if( null!=conn && conn.getAutoCommit() == false){ 					
    						conn.rollback(); 					
    						conn.setAutoCommit(true); 				
    					}
    				}catch(SQLException sqle) {
    					sqle.printStackTrace(); 
    				}
    			}finally {
    				try {
    					if( null!=conn && !conn.isClosed()){ 					
    						conn.close(); 
    					} 				
    					conn=null; 			
    				} catch(Exception e) {} 		
    				}				
    			return mesg; 	
    		}

    Problem:
    ======

    The problem here is "data syncing is done from different locations at a time and we are not able to no know how to fix this problem.

    The no.of connections increase at a time and it gets struck saying that maximum connections exceeded.




    Any insight is greatly appreciated.

    Thanks in Advance
    - Charan

  • #2
    Code:
    conn = this.dataSource.getConnection(); 						
    			conn.setAutoCommit(false); 			
    			st = SQL.getPreparedUpdateFor( column values , conn); 			
    			while( st == null){
    				if( conn != null && !conn.isClosed()){ 					
    					conn.close();		
    					conn=null; 				
    				}			
    			conn = this.dataSource.getConnection();
    So, if the statement is NOT null, you skip over this and basically throw away the first connection and open another one....there's your connection leak right there!!!

    You are much better off using the jdbctemplate class and letting it handle your connection management for you and this is a prime example why

    Comment


    • #3
      As chudak already pointed out, use Springs resource management (resource management shouldn't be a burden of the developer).

      Comment

      Working...
      X