commons-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kenny Huang (JIRA)" <j...@apache.org>
Subject [jira] Issue Comment Edited: (DBCP-287) Socket Connection Timeout Fix for Connection Pool management
Date Mon, 04 May 2009 04:08:30 GMT

    [ https://issues.apache.org/jira/browse/DBCP-287?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12705471#action_12705471
] 

Kenny Huang edited comment on DBCP-287 at 5/3/09 9:06 PM:
----------------------------------------------------------

I use a list store the last visited time that conn is used. If more that 5 minutes, close
the connection. Attached code:
{code}
public static DataSource qaDB = null;
private static HashMap<Integer, Calendar> connectTime = null;  // use hashcode as key
private static int MINUTES_TIME_OUT = 5; // 5 minutes

static {
    Properties properties = new Properties();
    properties.put("driverClassName", "oracle.jdbc.driver.OracleDriver");
    properties.put("url", "xxxxxxxxxxxxxx");
    properties.put("username", "xxxx");
    properties.put("password", "xxxx");
    properties.put("accessToUnderlyingConnectionAllowed", "true"); // this property must be
set
    try {
        qaDB = BasicDataSourceFactory.createDataSource(properties);
    } catch(Exception e) {
        qaDB = null;
    }
    
    connectTime = new HashMap<Integer, Calendar>();
}


// return a connection
public static Connection getQAConnection() {
    if (qaDB != null) {
        try {
            while(true) {
                Connection conn = qaDB.getConnection();
                
                Calendar curTime = Calendar.getInstance();
                int hashCode = conn.hashCode();
                
                if (connectTime.containsKey(hashCode)) {
                    // compare the last visited time.
                    Calendar lastTime = connectTime.get(hashCode);
                    lastTime.add(Calendar.MINUTE, MINUTES_TIME_OUT);
                    
                    if (lastTime.before(curTime)) {
                        // timeout
                        if (conn instanceof DelegatingConnection) {
                            Connection delegate = ((DelegatingConnection)conn).getDelegate();
                            if (delegate instanceof PoolableConnection)
                                try {
                                    // throws exception when conn is really timeout. 
                                    // exceeded maximum idle time exception
                                    ((PoolableConnection)delegate).reallyClose();
                                } catch(Exception e_) { } 
                            try {
                                conn.close();
                            } catch(Exception e_) { }
                            connectTime.remove(hashCode);
                            
                        } else {
                            conn.close();
                            connectTime.remove(hashCode);
                        }
                        System.out.println("Del-> " + hashCode);
                        
                    } else {
                        // update the last visited time
                        connectTime.put(hashCode, curTime);
                        System.out.println("Upd-> " + hashCode);
                        return conn;
                    }
                    
                } else {
                    connectTime.put(hashCode, curTime);
                    System.out.println("New-> " + hashCode);
                    return conn;
                }
            }
        } catch(SQLException e) { }
    }
    return null;
}
{code}

      was (Author: yourenzhuce):
    I use a list store the last visited time that conn is used. If more that 5 minutes, close
the connection. Attached code:
{code}
public static DataSource qaDB = null;
private static HashMap<Integer, Calendar> connectTime = null;  // use hashcode as key
private static int MINUTES_TIME_OUT = 5; // 5 minutes

static {
	Properties properties = new Properties();
	properties.put("driverClassName", "oracle.jdbc.driver.OracleDriver");
	properties.put("url", "xxxxxxxxxxxxxx");
	properties.put("username", "xxxx");
	properties.put("password", "xxxx");
	properties.put("accessToUnderlyingConnectionAllowed", "true"); // this property must be set
	try {
		qaDB = BasicDataSourceFactory.createDataSource(properties);
	} catch(Exception e) {
		qaDB = null;
	}
	
	connectTime = new HashMap<Integer, Calendar>();
}


// return a connection
public static Connection getQAConnection() {
	if (qaDB != null) {
		try {
			while(true) {
				Connection conn = qaDB.getConnection();
				
				Calendar curTime = Calendar.getInstance();
				int hashCode = conn.hashCode();
				
				if (connectTime.containsKey(hashCode)) {
					// compare the last time.
					Calendar lastTime = connectTime.get(hashCode);
					lastTime.add(Calendar.MINUTE, MINUTES_TIME_OUT);
					
					if (lastTime.before(curTime)) {
						// timeout
						if (conn instanceof DelegatingConnection) {
							Connection delegate = ((DelegatingConnection)conn).getDelegate();
							if (delegate instanceof PoolableConnection)
								try {
									// throws exception when conn is really timeout. exceeded maximum idle time exception
									((PoolableConnection)delegate).reallyClose();
								} catch(Exception e_) { } 
							try {
								conn.close();
							} catch(Exception e_) { }
							connectTime.remove(hashCode);
							
						} else {
							conn.close();
							connectTime.remove(hashCode);
						}
						System.out.println("Del-> " + hashCode);
						
					} else {
						connectTime.put(hashCode, curTime);
						System.out.println("Upd-> " + hashCode);
						return conn;
					}
					
				} else {
					connectTime.put(hashCode, curTime);
					System.out.println("New-> " + hashCode);
					return conn;
				}
			}
		} catch(SQLException e) { }
	}
	return null;
}

{code}
  
> Socket Connection Timeout Fix for Connection Pool management
> ------------------------------------------------------------
>
>                 Key: DBCP-287
>                 URL: https://issues.apache.org/jira/browse/DBCP-287
>             Project: Commons Dbcp
>          Issue Type: Task
>         Environment: JDK 1.5 on Linux Tomcat 5.5.23 with DBCP
>            Reporter: Ajay Warrier
>
> I have an apache tomcat server configured in the Production with DBCP Connection Pool
parameters configured to talk to the Database.
> The Application war files looks at the datasource that is setup in the context.xml to
connect to the DB
> Now the issue is....There is a firewall between the Tomcat Server and the DB. and the
firewall is programmed to timeout and disconnect any existing
> sockets after an hour of inactivity. When this happens , and a new connection is tried
from the Tomcat Server to the DB, it fails as TOmcat still things that 
> the connection is active and tries to make an attempt on this Socket timed out connection
and then it eventually times out.
> Here is a snippet from my context.xml
> <Resource name="jdbc/TESTDB" auth="Container" type="javax.sql.DataSource" driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@SERVER_NAME:1521:TEST_DB_SID" username="TEST1" password="TEST1" maxActive="200"
maxIdle="10" maxWait="10000" removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"/>
> Can anyone help me resolve this issue? ..

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message