Java ConnectionPool 连接未关闭，陷入“睡眠”状态

Java ConnectionPool connection not closing, stuck in 'sleep'

提问by Doug Miller

I have a webapp that uses JNDI lookups to get a connection to the database.

我有一个使用 JNDI 查找来连接到数据库的 web 应用程序。

The connection works fine and returns the query no problems. The issue us that the connection does not close properly and is stuck in the 'sleep' mode (according to mysql administrator). This means that they become unusable nad then I run out of connections.

连接工作正常并且返回查询没有问题。问题是连接没有正确关闭并停留在“睡眠”模式（根据 mysql 管理员）。这意味着它们变得无法使用，然后我用完了连接。

Can someone give me a few pointers as to what I can do to make the connection return to the pool successfully.

有人可以给我一些关于我可以做些什么来使连接成功返回池的指示。

public class DatabaseBean {

private static final Logger logger = Logger.getLogger(DatabaseBean.class);

private Connection conn;
private PreparedStatement prepStmt;

/**
 * Zero argument constructor
 * Setup generic databse connection in here to avoid redundancy
 * The connection details are in /META-INF/context.xml
 */
public DatabaseBean() {
    try {
        InitialContext initContext = new InitialContext();
        DataSource ds = (DataSource) initContext.lookup("java:/comp/env/jdbc/mysite");
        conn = ds.getConnection();
    }
    catch (SQLException SQLEx) {
        logger.fatal("There was a problem with the database connection.");
        logger.fatal(SQLEx);
        logger.fatal(SQLEx.getCause());
    }
    catch (NamingException nameEx) {
        logger.fatal("There was a naming exception");
        logger.fatal(nameEx);
        logger.fatal(nameEx.getCause());
    }
}

/**
 * Execute a query. Do not use for statements (update delete insert etc).
 *
 * @return A ResultSet of the execute query. A set of size zero if no results were returned. It is never null.
 * @see #executeUpdate() for running update, insert delete etc.
 */

public ResultSet executeQuery() {
    ResultSet result = null;
    try {
        result = prepStmt.executeQuery();
        logger.debug(prepStmt.toString());
    }
    catch (SQLException SQLEx) {
        logger.fatal("There was an error running a query");
        logger.fatal(SQLEx);
    }
    return result;
}

SNIP

剪报

public void close() {
    try {
        prepStmt.close();
        prepStmt = null;

        conn.close();
        conn = null;
    } catch (SQLException SQLEx) {
        logger.warn("There was an error closing the database connection.");
    }
}
}

This is inside a javabean that uses the database connection.

这是在使用数据库连接的 javabean 中。

public LinkedList<ImportantNoticeBean> getImportantNotices() {

    DatabaseBean noticesDBBean = new DatabaseBean();
    LinkedList<ImportantNoticeBean> listOfNotices = new LinkedList<ImportantNoticeBean>();

    try {
        PreparedStatement preStmt = noticesDBBean.getConn().prepareStatement("SELECT pseudonym, message, date_to, date_from " +
                "FROM importantnotices, users " +
                "WHERE importantnotices.username = users.username " +
                "AND NOW() >= date_from AND NOW() <= date_to;");

        noticesDBBean.setPrepStmt(preStmt);
        ResultSet result = noticesDBBean.executeQuery();

        while (result.next()) {
            ImportantNoticeBean noticeBean = new ImportantNoticeBean();

            noticeBean.setAuthor(result.getString("pseudonym"));
            noticeBean.setMessage(result.getString("message"));
            noticeBean.setDateTo(result.getDate("date_to"));
            noticeBean.setDateFrom(result.getDate("date_from"));

            listOfNotices.add(noticeBean);
        }

        result.close();

    } catch (SQLException SQLEx) {
        logger.error("There was an error in ImportantNoticesBean.getImportantNotices()");
        logger.error(SQLEx);
    } finally {
        noticesDBBean.close();
    }
    return listOfNotices;
}

<Context reloadable="true">

    <Resource name="jdbc/mysite"
              auth="Container"
              type="javax.sql.DataSource"
              username="user"
              password="password"
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/mysite"
              maxActive="10"
              maxIdle="5"
              maxWait="6000"
              removeAbandoned="true"
              logAbandoned="false"
              removeAbandonedTimeout="20"
            />
</Context>

采纳答案by Doug Miller

The issue us that the connection does not close properly and is stuck in the 'sleep' mode

连接未正确关闭并卡在“睡眠”模式的问题

This was actually only half right.

这其实只对了一半。

The problem I ran into was actually that each app was defining a new connection to the database sever. So each time I closed all the connections App A would make a bunch of new connections as per it's WEB.xml config file and run happily. App B would do the same. The problem is that they are independent poolswhich try to grab up to the server defined limit. It is a kind of race condition I guess. So when App A has finished with the connections it sits waiting to to use them again until the timeout has passed while App B who needs the connection now is denied the resources even though App A has finished with the and should be back in the pool. Once the timeout has passed, the connection is freed up and B (or C etc) can get at it again.

我遇到的问题实际上是每个应用程序都定义了一个到数据库服务器的新连接。所以每次我关闭所有连接时，App A 都会根据它的 WEB.xml 配置文件创建一堆新连接并愉快地运行。App B 也会这样做。问题在于它们是独立的池，它们试图达到服务器定义的限制。我猜这是一种竞争条件。因此，当 App A 完成连接时，它会等待再次使用它们，直到超时过去，而现在需要连接的 App B 被拒绝资源，即使 App A 已完成连接并且应该回到池中。一旦超时，连接被释放，B（或 C 等）可以再次访问它。

e.g. if the limit is 10 (mySQL profile limit) and each app has been configured to use a max of 10 the there will be 20 attempts at connections. Obviously this is a bad situation.

例如，如果限制为 10（mySQL 配置文件限制）并且每个应用程序已配置为最多使用 10 个，则将有 20 次连接尝试。显然，这是一个糟糕的情况。

The solution is to RTFM and put the connection details in the right place. This does make shared posting a pain but there are ways around it (such as linking to other xml files from the context).

解决方法是 RTFM 并将连接细节放在正确的位置。这确实使共享发布变得痛苦，但有一些方法可以解决（例如从上下文链接到其他 xml 文件）。

Just to be explicit: I put the connection details in the WEB.xml for each app and the had a fight about it.

明确地说：我将每个应用程序的连接详细信息放在 WEB.xml 中，并且对此进行了争论。

回答by Binil Thomas

You seem to be closing the connection properly - except for the case where prepStmt.close() throws a SQLException, I can't find a connection leak.

您似乎正在正确关闭连接 - 除了 prepStmt.close() 抛出 SQLException 的情况外，我找不到连接泄漏。

What pool implementation are you using? When you close a connection, the pool need not close the underlying MySQL connection immediately - after all that is the point of a connection pool! So from MySQL side, the connections would look alive, although your app is not using any; they might simply be held by the TC connection pool.

您使用的是什么池实现？当你关闭一个连接时，池不需要立即关闭底层的 MySQL 连接——毕竟​​这是连接池的重点！因此，从 MySQL 方面来看，尽管您的应用程序没有使用任何连接，但连接看起来还很活跃；它们可能只是由 TC 连接池持有。

You might want to experiment with the settings of the connection pool.Ask it to shrink the pool when the system is idle. Or, ask it to refresh all connections periodically. Or, have a strict upper bound on the number of concurrent connections it ever gets from MySQL etc.

您可能想要试验连接池的设置。要求它在系统空闲时缩小连接池。或者，要求它定期刷新所有连接。或者，对它从 MySQL 等获得的并发连接数有一个严格的上限。

One way to check if your code has a connection leak is to force the ds.getConnection() to always open a new physical connection and conn.close() to release the connection (if your connection pool has settings for those). Then if you watch the connections on MySQL side, you might be able to figure out if the code really has a connection leak or not.

检查您的代码是否存在连接泄漏的一种方法是强制 ds.getConnection() 始终打开一个新的物理连接并使用 conn.close() 释放连接（如果您的连接池有这些设置）。然后，如果您观察 MySQL 端的连接，您也许能够确定代码是否真的存在连接泄漏。

回答by John Meagher

One thing that @binil missed, you are not closing the result set in the case of an exception. Depending on the driver implementation this may cause the connection to stay open. Move the result.close() call to the finally block.

@binil 错过的一件事是，您不会在出现异常的情况下关闭结果集。根据驱动程序的实现，这可能会导致连接保持打开状态。将 result.close() 调用移动到 finally 块。

回答by ScArcher2

This is a similar question - Connection Pool Settings for Tomcat

这是一个类似的问题 - Tomcat 的连接池设置

This is my response to that question and it fixed the problem for the other guy. It may help you out too.

这是我对那个问题的回答，它解决了另一个人的问题。它也可以帮助你。

Tomcat Documentation

Tomcat 文档

DBCP uses the Jakarta-Commons Database Connection Pool. It relies on number of Jakarta-Commons components:

DBCP 使用 Jakarta-Commons 数据库连接池。它依赖于 Jakarta-Commons 组件的数量：

* Jakarta-Commons DBCP
* Jakarta-Commons Collections
* Jakarta-Commons Pool

I'm using the same connection pooling stuff and I'm setting these properties to prevent the same thing it's just not configured through tomcat. But if the first thing doesn't work try these.

我正在使用相同的连接池的东西，我正在设置这些属性来防止同样的事情，它只是没有通过 tomcat 配置。但是，如果第一件事不起作用，请尝试这些。

testWhileIdle=true
timeBetweenEvictionRunsMillis=300000

回答by Doug Miller

Ok I might have this sorted. I have changed the database config resource to the following:

好吧，我可能已经排序了。我已将数据库配置资源更改为以下内容：

*SNIP*
maxActive="10"
maxIdle="5"
maxWait="7000"
removeAbandoned="true"
logAbandoned="false"
removeAbandonedTimeout="3"
*SNIP*

This works well enough for now. What is happening, afaik, is that once I reach the ten connections then Tomcat is checking for abandoned connections (idle time > 3). It does this in a batch job each time that max connections is reached. The potential issue with this is if i need more than 10 queries run at the same time (not unique to me). The important thing is that removeAbandonedTimeout is less than maxWait.

这现在工作得很好。发生的事情，afaik，一旦我达到十个连接，Tomcat 就会检查废弃的连接（空闲时间 > 3）。每次达到最大连接时，它都会在批处理作业中执行此操作。这样做的潜在问题是，如果我需要同时运行 10 个以上的查询（不是我独有的）。重要的是 removeAbandonedTimeout 小于 maxWait。

Is this what should be happening? ie Is this the way that the pool should operate? If it is is seems, at least to me, that you would wait until something (the connection) is broken before fixing rather than not letting it 'break' in the first place. Maybe I am still not getting it.

这是应该发生的事情吗？即这是池应该运行的方式吗？如果看起来，至少在我看来，您会等到某些东西（连接）断开后再进行修复，而不是一开始就让它“断开”。也许我仍然没有得到它。

回答by Igor Zelaya

I am using the same configuration as you are. If the connection in mysql administrator(windows) shows that it is in sleep mode it only means that is pooled but not in use. I checked this running a test program program with multiple threads making random queries to Mysql. if it helps here is my configuration:

我正在使用与您相同的配置。如果 mysql 管理员（windows）中的连接显示它处于睡眠模式，则仅表示已池化但未使用。我检查了这个运行一个测试程序程序，其中多个线程对 Mysql 进行随机查询。如果有帮助，这里是我的配置：

        defaultAutoCommit="false"
        defaultTransactionIsolation="REPEATABLE_READ"
        auth="Container"
        type="javax.sql.DataSource"
        logAbandoned="true" 
          removeAbandoned="true"
        removeAbandonedTimeout="300" 
        maxActive="-1"
        initialSize="15"
        maxIdle="10"
        maxWait="10000" 
        username="youruser"
        password="youruserpassword"
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://yourhost/yourdatabase"/>