Connection Pooling - Tote Verbindungen

[Rahmspinat]

Hallo ihr,

ich habe eine Anwendung die rund um die Uhr laufen muss.

Leider bricht die Datenbankverbindung regelmäßig ab.

Um eine Verbindung mit der Datenbank zu erstellen verwende ich einen Connection Pool mit der Bibiliothek commons-dbcp, die ich im Internet gefunden habe.

Ich habe gelesen das eine connection, wenn die wait_timeout vom Datenbankserver abgelaufen ist, geschlossen wird. Mit getConnection() wird immer eine funktionierende Connection aus dem Pool geholt. Die nicht mehr funktionierenden Verbindungen bleiben aktiv und werden nicht automatisch aus dem Pool entfernt. Wenn dann die maximale Poolgröße erreicht wird geht gar nichts mehr.

Meine Frage ist jetzt, wie kann ich aus dem Pool eine kaputte Verbindung entfernen und wie kann ich diese Identifizieren?

Hat einer eine Idee, eine Anregung oder eine Alternative?

Hier die Klasse über die ich die Verbindung erstelle:

/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 * 
 *      [url]http://www.apache.org/licenses/LICENSE-2.0[/url]
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

import java.sql.DriverManager;
import java.sql.Connection;

//
// Here are the dbcp-specific classes.
// Note that they are only used in the setupDriver
// method. In normal use, your classes interact
// only with the standard JDBC API
//

import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;

//
// Here's a simple example of how to use the PoolingDriver.
// In this example, we'll construct the PoolingDriver manually,
// just to show how the pieces fit together, but you could also
// configure it using an external conifguration file in
// JOCL format (and eventually Digester).
//

//
// To compile this example, you'll want:
//  * commons-pool-1.5.4.jar
//  * commons-dbcp-1.2.2.jar
// in your classpath.
//
// To run this example, you'll want:
//  * commons-collections.jar
//  * commons-pool-1.5.4.jar
//  * commons-dbcp-1.2.2.jar
//  * the classes for your (underlying) JDBC driver
// in your classpath.
//
// Invoke the class using two arguments:
//  * the connect string for your underlying JDBC driver
//  * the query you'd like to execute
// You'll also want to ensure your underlying JDBC driver
// is registered.  You can use the "jdbc.drivers"
// property to do this.
//
// For example:
//  java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver \
//       -classpath commons-pool-1.5.3.jar:commons-dbcp-1.2.2.jar:oracle-jdbc.jar:. \
//       ManualPoolingDriverExample \
//       "jdbc:oracle:thin:scott/tiger@myhost:1521:mysid" \
//       "SELECT * FROM DUAL"
//
public class ManualPoolingDriver {
    
    public ManualPoolingDriver() {
        //
        // First we load the underlying JDBC driver.
        // You need this if you don't use the jdbc.drivers
        // system property.
        //
        System.out.println("Loading underlying JDBC driver.");
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        System.out.println("Done.");

        //
        // Then we set up and register the PoolingDriver.
        // Normally this would be handled auto-magically by
        // an external configuration, but in this example we'll
        // do it manually.
        //
        System.out.println("Setting up driver.");
        try {
            setupDriver("jdbc:mysql://localhost/blabla");
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("Done.");
    }
    

    public Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");
            return conn;
        }catch(Exception ex) {
            System.err.println(ex.getMessage());
            return conn;
        }
    }

    public static void setupDriver(String connectURI) throws Exception {
        //
        // First, we'll need a ObjectPool that serves as the
        // actual pool of connections.
        //
        // We'll use a GenericObjectPool instance, although
        // any ObjectPool implementation will suffice.
        //
        ObjectPool connectionPool = new GenericObjectPool(null);
      
        //
        // Next, we'll create a ConnectionFactory that the
        // pool will use to create Connections.
        // We'll use the DriverManagerConnectionFactory,
        // using the connect string passed in the command line
        // arguments.
        //
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI,"name", "pw");

        //
        // Now we'll create the PoolableConnectionFactory, which wraps
        // the "real" Connections created by the ConnectionFactory with
        // the classes that implement the pooling functionality.
        //
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);
        //
        // Finally, we create the PoolingDriver itself...
        //
        
        
        Class.forName("org.apache.commons.dbcp.PoolingDriver");
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");

        //
        // ...and register our pool with it.
        //
        driver.registerPool("example",connectionPool);

        //
        // Now we can just use the connect string "jdbc:apache:commons:dbcp:example"
        // to access our pool of Connections.
        //
    }

    public static void printDriverStats() throws Exception {
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
        ObjectPool connectionPool = driver.getConnectionPool("example");
       
        System.out.println("NumActive: " + connectionPool.getNumActive());
        System.out.println("NumIdle: " + connectionPool.getNumIdle());
    }

    public static void shutdownDriver() throws Exception {
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
        driver.closePool("example");
    }
}

 

[Rahmspinat]

Als Alternative habe ich zurzeit erstmal den wert der Variable "wait_timeout" auf das maximale gesetzt und denke, dass es eine ganze weile klappen könnte (ausgerechnet 1 Jahr).

Ich finde aber nicht, dass das eine saubere Lösung ist.

 

[Gast2]

Leider bricht die Datenbankverbindung regelmäßig ab.
[...]
Mit getConnection() wird immer eine funktionierende Connection aus dem Pool geholt. Die nicht mehr funktionierenden Verbindungen bleiben aktiv und werden nicht automatisch aus dem Pool entfernt.
[...]
Meine Frage ist jetzt, wie kann ich aus dem Pool eine kaputte Verbindung entfernen und wie kann ich diese Identifizieren?
[...]
Hat einer eine Idee, eine Anregung oder eine Alternative?

Witzig - wir hatten hier exakt das gleiche Problem. Bei uns ist OCI und der Haufen an Firewalls zwischen den Maschinen schuld. Ich hab es gelöst durch eine eignes ConnectionPooling basierend auf dem common-pool framework:

ConnectionPool hält intern einen Pool und bietet die Funktionen um davon Objekte zu holen oder zurück zu geben:

public final class ConnectionPool {

	private static final ConnectionPool instance = new ConnectionPool();
	private final GenericKeyedObjectPool internalPool;
	private final PooledConnectionFactory factory = new PooledConnectionFactory();
	
	public static ConnectionPool getInstance(){
		return instance;
	}
	
	private ConnectionPool() {
		internalPool = new GenericKeyedObjectPool(
				factory, 
				maxActive, 
				whenExhaustedAction,  
				maxWait, 
				maxIdle, 
				maxTotal,
				minIdle, 
				testOnBorrow, // true, damit die Connection beim borow validiert wird
				testOnReturn,  // true, damit die Connection beim return validiert wird
				timeBetweenEvictionRunsMillis, 
				numTestsPerEvictionRun, 
				minEvictableIdleTimeMillis, 
				testWhileIdle,
				lifo);

	}
	
	public synchronized Connection getConnection(String dbIdentifier) throws SQLException{
		return (Connection) internalPool.borrowObject(dbIdentifier);
	}
	
	public synchronized void releaseConnection(String dbIdentifier, Connection connection) throws SQLException{
		internalPool.returnObject(dbIdentifier, connection);
	}

	public synchronized void invalidateConnection(String dbIdentifier,Connection connection) throws SQLException{
		internalPool.invalidateObject(dbIdentifier, connection);
	}

	public void shutdownPool() {
		internalPool.close();
		internalPool.clear();
	}

Dir passende Factory baut dann Objekte, validiert sie und schließt sie ggfs.

final class PooledConnectionFactory implements KeyedPoolableObjectFactory {

	@Override
	public void activateObject(Object arg0, Object arg1) throws Exception {
		Connection connection = (Connection) arg1;
		((PooledConnection)connection).setActive(true);
	}

	@Override
	public void destroyObject(Object arg0, Object arg1) throws Exception {
		PooledConnection pc = (PooledConnection) arg1;
		try {
			pc.close();
		} catch (SQLException e) {
			logger.warn("Could not close PooledConnection", pc, "for", arg0, e);
		}
	}

	@Override
	public Object makeObject(Object arg0) throws Exception {
		String dbIdentifier = arg0.toString();
		DataSource ds = getDataSource(dbIdentifier);
		Connection physicalConnection = getConnection(ds);
		Connection connection = new PooledConnection(dbIdentifier, physicalConnection);
		return connection;
	}

	@Override
	public void passivateObject(Object arg0, Object arg1) throws Exception {
		Connection connection = (Connection) arg1;
		((PooledConnection)connection).setActive(false);
	}

	@Override
	public boolean validateObject(Object arg0, Object arg1) {
		return ((PooledConnection) arg1).isValid();
	}

Und die PooledConnection selber als Wrapper um eine OCI Connection

public final class PooledConnection implements Connection {

	private final String dbIdentifier;
	private final Connection innerConnection;
	private boolean closed;
	private boolean active;

	public PooledConnection(String dbIdentifier, Connection physicalConnection) {
		this.dbIdentifier = dbIdentifier;
		this.innerConnection = physicalConnection;
		this.closed = false;
	}

	/**
	 * @return the closed
	 */
	public synchronized boolean isClosed() {
		return closed;
	}

	/**
	 * @param closed
	 *            the closed to set
	 */
	protected synchronized void setClosed(boolean closed) {
		this.closed = closed;
	}

	/**
	 * @return the valid
	 */
	public synchronized boolean isValid() {
		try {
			Statement stmt = createStatement();
			stmt.execute("SELECT 1 FROM dual");
			ResultSet rs = stmt.getResultSet();
			String result = null;
			while (rs.next()) {
				result = rs.getString(1);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			return false;
		}
		return true;
	}

	/**
	 * @return the available
	 */
	public synchronized boolean isActive() {
		return active;
	}

	/**
	 * @param available
	 *            the available to set
	 */
	protected synchronized void setActive(boolean available) {
		this.active = available;
	}

	/**
	 * @return the dbIdentifier
	 */
	public synchronized String getDbIdentifier() {
		return dbIdentifier;
	}

	/**
	 * @return the inner (physical) connection
	 */
	public synchronized Connection getInnerConnection() {
		return innerConnection;
	}

	/*
	 * Connection Implementations
	 */

	/**
     * {@inheritDoc}
	 */
	@Override
	public void close() throws SQLException {
		innerConnection.close();
		setClosed(true);
	}
	
	/**
     * {@inheritDoc}
	 */
	@Override
	public void clearWarnings() throws SQLException {
		innerConnection.clearWarnings();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public void commit() throws SQLException {
		innerConnection.commit();

	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public Array createArrayOf(String typeName, Object[] elements)
			throws SQLException {
		return innerConnection.createArrayOf(typeName, elements);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public Blob createBlob() throws SQLException {
		return innerConnection.createBlob();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public Clob createClob() throws SQLException {
		return innerConnection.createClob();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public NClob createNClob() throws SQLException {
		return innerConnection.createNClob();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public SQLXML createSQLXML() throws SQLException {
		return innerConnection.createSQLXML();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public Statement createStatement() throws SQLException {
		return innerConnection.createStatement();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public Statement createStatement(int resultSetType, int resultSetConcurrency)
			throws SQLException {
		return innerConnection.createStatement(resultSetType,
				resultSetConcurrency);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public Statement createStatement(int resultSetType,
			int resultSetConcurrency, int resultSetHoldability)
			throws SQLException {
		return innerConnection.createStatement(resultSetType,
				resultSetConcurrency, resultSetHoldability);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public Struct createStruct(String typeName, Object[] attributes)
			throws SQLException {
		return innerConnection.createStruct(typeName, attributes);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public boolean getAutoCommit() throws SQLException {
		return innerConnection.getAutoCommit();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public String getCatalog() throws SQLException {
		return innerConnection.getCatalog();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public Properties getClientInfo() throws SQLException {
		return innerConnection.getClientInfo();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public String getClientInfo(String name) throws SQLException {
		return innerConnection.getClientInfo(name);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public int getHoldability() throws SQLException {
		return innerConnection.getHoldability();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public DatabaseMetaData getMetaData() throws SQLException {
		return innerConnection.getMetaData();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public int getTransactionIsolation() throws SQLException {
		return innerConnection.getTransactionIsolation();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public Map<String, Class<?>> getTypeMap() throws SQLException {
		return innerConnection.getTypeMap();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public SQLWarning getWarnings() throws SQLException {
		return innerConnection.getWarnings();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public boolean isReadOnly() throws SQLException {
		return innerConnection.isReadOnly();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public boolean isValid(int timeout) throws SQLException {
		return innerConnection.isValid(timeout);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public String nativeSQL(String sql) throws SQLException {
		return innerConnection.nativeSQL(sql);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public CallableStatement prepareCall(String sql) throws SQLException {
		return innerConnection.prepareCall(sql);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public CallableStatement prepareCall(String sql, int resultSetType,
			int resultSetConcurrency) throws SQLException {
		return innerConnection.prepareCall(sql, resultSetType,
				resultSetConcurrency);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public CallableStatement prepareCall(String sql, int resultSetType,
			int resultSetConcurrency, int resultSetHoldability)
			throws SQLException {
		return innerConnection.prepareCall(sql, resultSetType,
				resultSetConcurrency, resultSetHoldability);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public PreparedStatement prepareStatement(String sql) throws SQLException {
		return innerConnection.prepareStatement(sql);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
			throws SQLException {
		return innerConnection.prepareStatement(sql, autoGeneratedKeys);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public PreparedStatement prepareStatement(String sql, int[] columnIndexes)
			throws SQLException {
		return innerConnection.prepareStatement(sql, columnIndexes);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public PreparedStatement prepareStatement(String sql, String[] columnNames)
			throws SQLException {
		return innerConnection.prepareStatement(sql, columnNames);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public PreparedStatement prepareStatement(String sql, int resultSetType,
			int resultSetConcurrency) throws SQLException {
		return innerConnection.prepareStatement(sql, resultSetType,
				resultSetConcurrency);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public PreparedStatement prepareStatement(String sql, int resultSetType,
			int resultSetConcurrency, int resultSetHoldability)
			throws SQLException {
		return innerConnection.prepareStatement(sql, resultSetType,
				resultSetConcurrency, resultSetHoldability);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public void releaseSavepoint(Savepoint savepoint) throws SQLException {
		innerConnection.releaseSavepoint(savepoint);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public void rollback() throws SQLException {
		innerConnection.rollback();

	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public void rollback(Savepoint savepoint) throws SQLException {
		innerConnection.releaseSavepoint(savepoint);

	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public void setAutoCommit(boolean autoCommit) throws SQLException {
		innerConnection.setAutoCommit(autoCommit);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public void setCatalog(String catalog) throws SQLException {
		innerConnection.setCatalog(catalog);

	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public void setClientInfo(Properties properties)
			throws SQLClientInfoException {
		innerConnection.setClientInfo(properties);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public void setClientInfo(String name, String value)
			throws SQLClientInfoException {
		innerConnection.setClientInfo(name, value);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public void setHoldability(int holdability) throws SQLException {
		innerConnection.setHoldability(holdability);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public void setReadOnly(boolean readOnly) throws SQLException {
		innerConnection.setReadOnly(readOnly);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public Savepoint setSavepoint() throws SQLException {
		return innerConnection.setSavepoint();
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public Savepoint setSavepoint(String name) throws SQLException {
		return innerConnection.setSavepoint(name);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public void setTransactionIsolation(int level) throws SQLException {
		innerConnection.setTransactionIsolation(level);

	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
		innerConnection.setTypeMap(map);

	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		return innerConnection.isWrapperFor(iface);
	}

	/**
     * {@inheritDoc}
	 */
	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		return innerConnection.unwrap(iface);
	}

Dabei wird dann effektiv jede Connection die nicht mehr funktioniert aus dem Pool entfernt. Ich hab dann noch dem Pool und der Factory ein Listener Concept spendiert und das läuft jetzt sehr stabil

 

[Rahmspinat]

huhu,

danke für den Code fassy. Sieht auf jedenfall super aus. Werde ich später testen. Hat sich gerade ein wesentlich schlimmeres Problem aufgetan, so dass ich das erstmal pausieren muss.

Danke trotzdem.

Das wird mir sicherlich in späteren Projekten noch mehrere male von nützen sein 🙂

gruß Martin

 

[bronks]

Hallo ihr,

ich habe eine Anwendung die rund um die Uhr laufen muss.

Leider bricht die Datenbankverbindung regelmäßig ab.

Um eine Verbindung mit der Datenbank zu erstellen verwende ich einen Connection Pool mit der Bibiliothek commons-dbcp, die ich im Internet gefunden habe.

Der wait_timeout wird Dich nicht retten. Dem Apache DBCP kann man noch folgende Parameter mitgeben, für den Fall, daß verweise Verbindungen bestehen können:
- removeAbandoned
- removeAbandonedTimeout

Dein Hauptproblem dürfte m.E. geregelt sein, wenn Du die Datenbankverbindung mit dem URL-Parameter [autoreconnect=true] aufbaust.

 

[Rahmspinat]

Danke auch für deine antwort bronks.