数据库连接池耗尽——Java

DB connection pool getting exhausted -- Java

提问by Ankit

I am using Connection Pool (snaq.db.ConnectionPool) in my application. The connection pool is initialized like:

我在我的应用程序中使用连接池 (snaq.db.ConnectionPool)。连接池初始化如下：

String dburl = propertyUtil.getProperty("dburl");
String dbuserName = propertyUtil.getProperty("dbuserName");
String dbpassword = propertyUtil.getProperty("dbpassword");
String dbclass = propertyUtil.getProperty("dbclass");
String dbpoolName = propertyUtil.getProperty("dbpoolName");
int dbminPool = Integer.parseInt(propertyUtil.getProperty("dbminPool"));
int dbmaxPool = Integer.parseInt(propertyUtil.getProperty("dbmaxPool"));
int dbmaxSize = Integer.parseInt(propertyUtil.getProperty("dbmaxSize"));
long dbidletimeout = Long.parseLong(propertyUtil.getProperty("dbidletimeout"));
Class.forName(dbclass).newInstance();
ConnectionPool moPool = new ConnectionPool(dbpoolName, dbminPool, dbmaxPool, dbmaxSize,
dbidletimeout, dburl, dbuserName, dbpassword);

DB Pool values used are:

使用的数据库池值是：

dbminPool=5
dbmaxPool=30
dbmaxSize=30
dbclass=org.postgresql.Driver
dbidletimeout=25

My application was leaking connection somewhere (connection was not released) and due to which the connection pool was getting exhausted. I have fixed that code for now.

我的应用程序在某处泄漏连接（连接未释放），因此连接池已耗尽。我现在已经修复了那个代码。

Shouldn't the connections be closed after idle timeout period? If that is not correct assumption, Is there any way to close the open idle connectionsanyway (through java code only)?

不应该在空闲超时时间后关闭连接吗？如果这不是正确的假设，是否有任何方法可以关闭打开的空闲连接（仅通过 java 代码）？

采纳答案by c.s.

The timeoutvariable does not seem to correspond to the time the connection is being idle but to how much time the pool can wait to return a new connection or throw an exception (I had a look at this source code, don't know if it is up-to-date). I think that it would be rather difficult to keep track of "idle" connections because what "idle" really means in this case? You might want to get a connection for later use. So I would say that the only safeway for the connection pool to know that you are done with the connection, is to call close()on it.

该timeout变量似乎并不对应于连接空闲的时间，而是对应于池可以等待返回新连接或抛出异常的时间（我看过这个源代码，不知道是不是最新）。我认为跟踪“空闲”连接会相当困难，因为在这种情况下“空闲”的真正含义是什么？您可能希望获得连接以供以后使用。所以我想说，让连接池知道你已经完成连接的唯一安全方法是调用close()它。

If you are worried about the development team forgetting to call close()in their code, there is a technique which I describe below and I have used in the past (in my case we wanted to keep track of unclosed InputStreams but the concept is the same).

如果您担心开发团队忘记调用close()他们的代码，那么我在下面描述了一种技术，我过去曾使用过（在我的情况下，我们想跟踪未关闭的InputStreams，但概念是相同的）。

Disclaimer:

免责声明：

• I assume that the connections are only used during a single request and do not span during consecutive requests. In the latter case you can't use the solution below.
• Your connection pool implementation seems to already use similar techniques with the ones I describe below (i.e. it already wraps the connections) so I cannot possibly know if this will work for your case or not. I have not tested the code below, I just use it to describe the concept.
• Please use that only in your development environment. In production you should feel confident that your code is tested and that it behaves correctly.

• 我假设连接仅在单个请求期间使用，并且在连续请求期间不跨越。在后一种情况下，您不能使用下面的解决方案。
• 您的连接池实现似乎已经使用了与我在下面描述的技术类似的技术（即它已经包装了连接），所以我不可能知道这是否适用于您的情况。我没有测试下面的代码，我只是用它来描述这个概念。
• 请仅在您的开发环境中使用它。在生产中，您应该确信您的代码已经过测试并且它的行为是正确的。

Having said the above, the main idea is this: We have a central place (the connection pool) from where we acquire resources (connections) and we want to keep track if those resources are released by our code. We can use a web Filterthat uses a ThreadLocalobject that keeps track of the connections used during the request. I named this class TrackingFilterand the object that keeps track of the resources is the Trackerclass.

综上所述，主要思想是：我们有一个中心位置（连接池），我们从中获取资源（连接），我们希望跟踪这些资源是否被我们的代码释放。我们可以使用一个 Web Filter，它使用一个ThreadLocal对象来跟踪请求期间使用的连接。我命名了这个类TrackingFilter，跟踪资源的对象就是这个Tracker类。

public class TrackingFilter implements Filter {
    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
        Tracker.start();
        try {
            chain.doFilter(request, response);
        } finally {
            Tracker.stop();
        }
    }

    ...
}

For the Trackerto be able to keep track of the connections, it needs to be notified every time a connection is acquired with getConnection()and every time a connection is closed with a close()call. To be able to do that in a way that is transparent to the rest of the code we need to wrap the ConnectionPooland the returned Connectionobjects. Your code should return the new TrackingConnectionPoolinstead of the original pool (I assume the way to access the connection pool is at a single place). This new pool will wrap in turn, every Connectionit provides, as a TrackableConnection. The TrackableConnectionis the object that knows how to notify our Trackerwhen created and when closed.

为了Tracker能够跟踪连接，需要在每次获取连接时getConnection()以及每次通过close()调用关闭连接时通知它。为了能够以对其余代码透明的方式做到这一点，我们需要包装ConnectionPool和返回的Connection对象。您的代码应该返回新的TrackingConnectionPool而不是原始的池（我假设访问连接池的方式是在一个地方）。这个新池将依次包装Connection它提供的每一个，作为TrackableConnection. 的TrackableConnection是，知道如何通知我们的对象Tracker创建和关闭时的时候。

When you call Tracker.stop()at the end of the request it will report any connections for which close()has not been called yet. Since this is a per request operation you will identify only the faulty operations (i.e. during your "Create new product" functionality) and then hopefully you will be able to track down those queries that leave open connections and fix them.

当您Tracker.stop()在请求结束时调用时，它将报告close()尚未调用的任何连接。由于这是针对每个请求的操作，因此您将仅识别错误操作（即在“创建新产品”功能期间），然后希望您能够追踪那些留下打开连接的查询并修复它们。

Below you can find code and comments for the TrackingConnectionPool, TrackableConnectionand the Trackerclass. The delegate methods were left out for brevity. I hope that helps.

下面你可以找到代码和注释TrackingConnectionPool，TrackableConnection以及Tracker类。为简洁起见，省略了委托方法。我希望这有帮助。

Note: For the wrappers use an automated IDE feature (like Eclipse's "Generate delegate methods") otherwise it would be a time-consuming and error prone task.

注意：对于包装器，请使用自动化的 IDE 功能（如 Eclipse 的“生成委托方法”），否则这将是一项耗时且容易出错的任务。

//------------- Pool Creation

ConnectionPool original = new ConnectionPool(String dbpoolName, ...); 
TrackingConnectionPool trackingCP = new TrackingConnectionPool(original);

// ... or without creating the ConnectionPool yourself
TrackingConnectionPool trackingCP = new TrackingConnectionPool(dbpoolName, ...);

// store the reference to the trackingCP instead of the original

//------------- TrackingConnectionPool

public class TrackingConnectionPool extends ConnectionPool {

    private ConnectionPool originalPool;  // reference to the original pool

    // Wrap all available ConnectionPool constructors like this
    public TrackingConnectionPool(String dbpoolName, ...) {
        originalPool = new ConnectionPool(dbpoolName, ...);
    }

    // ... or use this convenient constructor after you create a pool manually
    public TrackingConnectionPool(ConnectionPool pool) {
        this.originalPool = pool; 
    }

    @Override
    public Connection getConnection() throws SQLException {
        Connection con = originalPool.getConnection();
        return new TrackableConnection(con);   // wrap the connections with our own wrapper
    }
    @Override
    public Connection getConnection(long timeout) throws SQLException {
        Connection con = originalPool.getConnection(timeout);
        return new TrackableConnection(con);   // wrap the connections with our own wrapper
    }

    // for all the rest public methods of ConnectionPool and its parent just delegate to the original
    @Override
    public void setCaching(boolean b) {
        originalPool.setCaching(b);
    }
    ...
}

//------------- TrackableConnection

public class TrackableConnection implements Connection, Tracker.Trackable {

    private Connection originalConnection;
    private boolean released = false;

    public TrackableConnection(Connection con) {
        this.originalConnection = con;
        Tracker.resourceAquired(this);  // notify tracker that this resource is aquired
    }

    // Trackable interface

    @Override
    public boolean isReleased() {
        return this.released;
    }

    // Note: this method will be called by Tracker class (if needed). Do not invoke manually
    @Override
    public void release() {
        if (!released) {
            try {
                // attempt to close the connection
                originalConnection.close();
                this.released = true;  
            } catch(SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    // Connection interface

    @Override
    public void close() throws SQLException {   
        originalConnection.close();
        this.released = true;   

        Tracker.resourceReleased(this); // notify tracker that this resource is "released"
    }

    // rest of the methods just delegate to the original connection

    @Override
    public Statement createStatement() throws SQLException {
        return originalConnection.createStatement();
    }
    ....
}

//------------- Tracker

public class Tracker {

    // Create a single object per thread
    private static final ThreadLocal<Tracker> _tracker = new ThreadLocal<Tracker>() {
            @Override 
            protected Tracker initialValue() {
                return new Tracker();
            };
    };

    public interface Trackable {
        boolean isReleased();
        void release();
    }

    // Stores all the resources that are used during the thread.
    // When a resource is used a call should be made to resourceAquired()
    // Similarly when we are done with the resource a call should be made to resourceReleased()
    private Map<Trackable, Trackable> monitoredResources = new HashMap<Trackable, Trackable>();

    // Call this at the start of each thread. It is important to clear the map
    // because you can't know if the server reuses this thread
    public static void start() {
        Tracker monitor = _tracker.get();
        monitor.monitoredResources.clear();
    }

    // Call this at the end of each thread. If all resources have been released
    // the map should be empty. If it isn't then someone, somewhere forgot to release the resource
    // A warning is issued and the resource is released.
    public static void stop() {
        Tracker monitor = _tracker.get();
        if ( !monitor.monitoredResources.isEmpty() ) {
            // there are resources that have not been released. Issue a warning and release each one of them
            for (Iterator<Trackable> it = monitor.monitoredResources.keySet().iterator(); it.hasNext();) {
                Trackable resource = it.next();

                if (!resource.isReleased()) {
                    System.out.println("WARNING: resource " + resource + " has not been released. Releasing it now.");
                    resource.release();
                } else {
                    System.out.println("Trackable " + resource 
                            + " is released but is still under monitoring. Perhaps you forgot to call resourceReleased()?");
                }
            }
            monitor.monitoredResources.clear();
        }
    }

    // Call this when a new resource is acquired i.e. you a get a connection from the pool
    public static void resourceAquired(Trackable resource) {
        Tracker monitor = _tracker.get();
        monitor.monitoredResources.put(resource, resource);
    }

    // Call this when the resource is released
    public static void resourceReleased(Trackable resource) {
        Tracker monitor = _tracker.get();
        monitor.monitoredResources.remove(resource);
    }
}

回答by user1351763

You don't have your full code posted so I assume you are not closing your connections. You STILL need to close the connection object obtained from the pool as you would if you were not using a pool. Closing the connection makes it available for the pool to reissue to another caller. If you fail to do this, you will eventually consume all available connections from your pool. A pool's stale connection scavenger is not the best place to clean up your connections. Like your momma told you, put your things away when you are done with them.

您没有发布完整的代码，所以我假设您没有关闭连接。如果您不使用池，您仍然需要关闭从池中获得的连接对象。关闭连接使池可以重新发送给另一个调用者。如果您不这样做，您最终将消耗池中的所有可用连接。池的陈旧连接清除程序不是清理连接的最佳位置。就像你妈妈告诉你的那样，当你处理完它们时，把它们收起来。

try {
  conn = moPool.getConnection(timeout);
  if (conn != null)
    // do something
} catch (Exception e) {
  // deal with me
} finally {
  try { 
    conn.close(); 
  } catch (Exception e) { 
    // maybe deal with me
  }
}

• E

• 乙

回答by Learn More

The whole point of connection pooling is to let pool handle all such things for you.

连接池的全部意义在于让池为您处理所有这些事情。

• Having a code for closing open idle connections of java poolwill not help in your case.
• Think about connection pool maintaining MAPs for IDLEor IN-USEconnections.
• IN-USE: If a connection object is being referenced by application, it is put in to in-use-mapby pool.
• IDLE: If a connection object is not being referenced by application / or closed, it is put into idle-mapby pool.
• Your pool exhausted because you were not closing connections. Not closing connections resulted all idleconnections to be put into in-use-map.
• Since idle-pooldoes not have any entry available, pool is forced to create more of them.
• In this way all your connections got marked as IN-USE.
• Your pool does not have any open-idle-connections, which you can close by code.
• Pool is not in position to close any connection even if time-out occurs, because nothing is idle.

• 拥有代码对closing open idle connections of java pool您的情况无济于事。
• 考虑维护 MAPIDLE或IN-USE连接的连接池。
• IN-USE: 如果应用程序正在引用连接对象，则将其放入in-use-map池中。
• IDLE: 如果应用程序 / 或 未引用连接对象closed，则将其放入idle-map池中。
• 您的池已耗尽，因为您没有关闭连接。不关闭连接导致所有idle连接都被放入in-use-map.
• 由于idle-pool没有任何可用条目，池被迫创建更多条目。
• 通过这种方式，您的所有连接都被标记为IN-USE。
• 您的池没有任何open-idle-connections，您可以通过代码关闭。
• 即使发生超时，池也无法关闭任何连接，因为没有什么是空闲的。

You did your best when you fixed connection leakage from your code.

当您修复代码中的连接泄漏时，您已尽力而为。

You can forcerelease of pool and recreate one. But you will have to be carefull because of existing connections which are in-use might get affected in their tasks.

您可以force释放池并重新创建一个。但是您必须小心，因为正在使用的现有连接可能会影响其任务。

回答by Mark Rotteveel

In most connection pools, the idle timeout is the maximum time a connection pool is idle in the connection pool (waiting to be requested), not how long it is in use (checked out from the connection pool).

在大多数连接池中，空闲超时是连接池在连接池中空闲的最长时间（等待被请求），而不是使用多长时间（从连接池中检出）。

Some connection pools also have timeout settings for how long a connection is allowed to be in use (eg DBCP has removeAbandonedTimeout, c3p0 has unreturnedConnectionTimeout), and if those are enabled and the timeout has expired, they will be forcefully revoked from the user and either returned to the pool or really closed.

一些连接池也有关于允许连接使用多长时间的超时设置（例如 DBCP has removeAbandonedTimeout, c3p0 has unreturnedConnectionTimeout），如果这些已启用且超时已过期，它们将被用户强行撤销并返回到游泳池还是真的关门了。

回答by Vadzim

log4jdbccan be used to mitigate connection leak troubleshooting by means of jdbc.connectionlogger.

log4jdbc可用于通过jdbc.connection记录器减轻连接泄漏故障排除。

This technique doesn't require any modification of the code.

这种技术不需要对代码进行任何修改。