java - Tomcat 连接池创建太多连接，卡在 sleep 模式

Question

我使用的是 Tomcat 6.0.29，带有 Tomcat 7 的连接池和 MySQL。测试我的应用程序，它不会重用池中的任何内容，但最终会创建一个新池，最终我无法使用数据库，因为当池的最大 Activity 大小设置时，池中有数百个 hibernate 连接到 20。

引用这里:

+----+------+-----------------+--------+---------+------+-------+------------------+
| Id | User | Host            | db     | Command | Time | State | Info             |
+----+------+-----------------+--------+---------+------+-------+------------------+
|  2 | root | localhost:51877 | dbname | Sleep   |    9 |       | NULL             |
|  4 | root | localhost       | NULL   | Query   |    0 | NULL  | show processlist |
|  5 | root | localhost:49213 | dbname | Sleep   |   21 |       | NULL             |
|  6 | root | localhost:53492 | dbname | Sleep   |   21 |       | NULL             |
|  7 | root | localhost:46012 | dbname | Sleep   |   21 |       | NULL             |
|  8 | root | localhost:34964 | dbname | Sleep   |   21 |       | NULL             |
|  9 | root | localhost:52728 | dbname | Sleep   |   21 |       | NULL             |
| 10 | root | localhost:43782 | dbname | Sleep   |   21 |       | NULL             |
| 11 | root | localhost:38468 | dbname | Sleep   |   21 |       | NULL             |
| 12 | root | localhost:48021 | dbname | Sleep   |   21 |       | NULL             |
| 13 | root | localhost:54854 | dbname | Sleep   |   21 |       | NULL             |
| 14 | root | localhost:41520 | dbname | Sleep   |   21 |       | NULL             |
| 15 | root | localhost:38112 | dbname | Sleep   |   13 |       | NULL             |
| 16 | root | localhost:39168 | dbname | Sleep   |   13 |       | NULL             |
| 17 | root | localhost:40427 | dbname | Sleep   |   13 |       | NULL             |
| 18 | root | localhost:58179 | dbname | Sleep   |   13 |       | NULL             |
| 19 | root | localhost:40957 | dbname | Sleep   |   13 |       | NULL             |
| 20 | root | localhost:45567 | dbname | Sleep   |   13 |       | NULL             |
| 21 | root | localhost:48314 | dbname | Sleep   |   13 |       | NULL             |
| 22 | root | localhost:34546 | dbname | Sleep   |   13 |       | NULL             |
| 23 | root | localhost:44928 | dbname | Sleep   |   13 |       | NULL             |
| 24 | root | localhost:57320 | dbname | Sleep   |   13 |       | NULL             |
| 25 | root | localhost:54643 | dbname | Sleep   |   29 |       | NULL             |
| 26 | root | localhost:49809 | dbname | Sleep   |   29 |       | NULL             |
| 27 | root | localhost:60993 | dbname | Sleep   |   29 |       | NULL             |
| 28 | root | localhost:36676 | dbname | Sleep   |   29 |       | NULL             |
| 29 | root | localhost:53574 | dbname | Sleep   |   29 |       | NULL             |
| 30 | root | localhost:45402 | dbname | Sleep   |   29 |       | NULL             |
| 31 | root | localhost:37632 | dbname | Sleep   |   29 |       | NULL             |
| 32 | root | localhost:56561 | dbname | Sleep   |   29 |       | NULL             |
| 33 | root | localhost:34261 | dbname | Sleep   |   29 |       | NULL             |
| 34 | root | localhost:55221 | dbname | Sleep   |   29 |       | NULL             |
| 35 | root | localhost:39613 | dbname | Sleep   |   15 |       | NULL             |
| 36 | root | localhost:52908 | dbname | Sleep   |   15 |       | NULL             |
| 37 | root | localhost:56401 | dbname | Sleep   |   15 |       | NULL             |
| 38 | root | localhost:44446 | dbname | Sleep   |   15 |       | NULL             |
| 39 | root | localhost:57567 | dbname | Sleep   |   15 |       | NULL             |
| 40 | root | localhost:56445 | dbname | Sleep   |   15 |       | NULL             |
| 41 | root | localhost:39616 | dbname | Sleep   |   15 |       | NULL             |
| 42 | root | localhost:49197 | dbname | Sleep   |   15 |       | NULL             |
| 43 | root | localhost:59916 | dbname | Sleep   |   15 |       | NULL             |
| 44 | root | localhost:37165 | dbname | Sleep   |   15 |       | NULL             |
| 45 | root | localhost:45649 | dbname | Sleep   |    1 |       | NULL             |
| 46 | root | localhost:55397 | dbname | Sleep   |    1 |       | NULL             |
| 47 | root | localhost:34322 | dbname | Sleep   |    1 |       | NULL             |
| 48 | root | localhost:54387 | dbname | Sleep   |    1 |       | NULL             |
| 49 | root | localhost:55147 | dbname | Sleep   |    1 |       | NULL             |
| 50 | root | localhost:47280 | dbname | Sleep   |    1 |       | NULL             |
| 51 | root | localhost:56856 | dbname | Sleep   |    1 |       | NULL             |
| 52 | root | localhost:58369 | dbname | Sleep   |    1 |       | NULL             |
| 53 | root | localhost:33712 | dbname | Sleep   |    1 |       | NULL             |
| 54 | root | localhost:44315 | dbname | Sleep   |    1 |       | NULL             |
| 55 | root | localhost:54649 | dbname | Sleep   |   14 |       | NULL             |
| 56 | root | localhost:41202 | dbname | Sleep   |   14 |       | NULL             |
| 57 | root | localhost:59393 | dbname | Sleep   |   14 |       | NULL             |
| 58 | root | localhost:38304 | dbname | Sleep   |   14 |       | NULL             |
| 59 | root | localhost:34548 | dbname | Sleep   |   14 |       | NULL             |
| 60 | root | localhost:49567 | dbname | Sleep   |   14 |       | NULL             |
| 61 | root | localhost:48077 | dbname | Sleep   |   14 |       | NULL             |
| 62 | root | localhost:48586 | dbname | Sleep   |   14 |       | NULL             |
| 63 | root | localhost:45308 | dbname | Sleep   |   14 |       | NULL             |
| 64 | root | localhost:43169 | dbname | Sleep   |   14 |       | NULL             |

它为每个请求精确创建 10 个，即 minIdle 和 InitialSize 属性，如下所示。

这是嵌入到 jsp 页面中的示例测试代码。该代码不是我的应用程序中的代码，只是用来查看问题是否与我的代码有关，但问题仍然存在。

Context envCtx;
envCtx = (Context) new InitialContext().lookup("java:comp/env");
DataSource datasource = (DataSource) envCtx.lookup("jdbc/dbname");
Connection con = null;

try {
  con = datasource.getConnection();
  Statement st = con.createStatement();
  ResultSet rs = st.executeQuery("select * from UserAccount");
  int cnt = 1;
  while (rs.next()) {
      out.println((cnt++)+". Token:" +rs.getString("UserToken")+
        " FirstName:"+rs.getString("FirstName")+" LastName:"+rs.getString("LastName"));
  }
  rs.close();
  st.close();
} finally {
  if (con!=null) try {con.close();}catch (Exception ignore) {}
}

这是我的 context.xml 文件:

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    <Resource name="jdbc/dbname" 
              auth="Container" 
              type="javax.sql.DataSource" 
              factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
              testWhileIdle="true"
              testOnBorrow="true"
              testOnReturn="false"
              validationQuery="SELECT 1"
              validationInterval="30000"
              timeBetweenEvictionRunsMillis="30000"
              maxActive="20" 
              minIdle="10" 
              maxWait="10000" 
              initialSize="10"
              removeAbandonedTimeout="60"
              removeAbandoned="true"
              logAbandoned="true"
              minEvictableIdleTimeMillis="30000" 
              jmxEnabled="true"
              jdbcInterceptors=
"org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
              username="" 
              password="" 
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/dbname?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8"/>

<WatchedResource>WEB-INF/web.xml</WatchedResource>
<WatchedResource>META-INF/context.xml</WatchedResource>
</Context>

我确定我可以将 removeAbandonedTimeout 设置为较低的值，它会清除所有这些 hibernate 连接，但这并不能解决真正的问题，不是吗？有谁知道我做错了什么？非常感谢。

Answer 1

我目前还没有测试环境，但是，我相信您应该在每次查询后关闭连接、语句和结果集；如果其中任何一个泄漏，它可能会使 Connection 处于空闲(但不一定返回到池)状态。

您收到的 Connection 对象实际上应该是池层的一种代理；在其上调用 close 会释放您对该连接的“保留”并将其返回到池中。 (它不一定会关闭底层的实际数据库连接。)

因为它可能保持打开状态(通常会保持打开状态)，池层可能会将未关闭的语句或结果集解释为仍然“忙碌”的指示。

您可以检查(例如，调试器使这很容易)Connection 对象以在运行时识别其状态，以确认这一点。

为简单起见(...)，我们在每次数据库连接调用后的 finally block 中使用了以下讨厌的小例程: ... finally { closeAll (rs, st, con); }，确保它们会立即脱离上下文。

    /**
 * Close a bunch of things carefully, ignoring exceptions. The
 * “things” supported, thus far, are:
 * <ul>
 * <li>JDBC ResultSet</li>
 * <li>JDBC Statement</li>
 * <li>JDBC Connection</li>
 * <li>Lock:s</li>
 * </ul>
 * <p>
 * This is mostly meant for “finally” clauses.
 *
 * @param things A set of SQL statements, result sets, and database
 *            connections
 */
public static void closeAll (final Object... things) {
    for (final Object thing : things) {
        if (null != thing) {
            try {
                if (thing instanceof ResultSet) {
                    try {
                        ((ResultSet) thing).close ();
                    } catch (final SQLException e) {
                        /* No Op */
                    }
                }
                if (thing instanceof Statement) {
                    try {
                        ((Statement) thing).close ();
                    } catch (final SQLException e) {
                        /* No Op */
                    }
                }
                if (thing instanceof Connection) {
                    try {
                        ((Connection) thing).close ();
                    } catch (final SQLException e) {
                        /* No Op */
                    }
                }
                if (thing instanceof Lock) {
                    try {
                        ((Lock) thing).unlock ();
                    } catch (final IllegalMonitorStateException e) {
                        /* No Op */
                    }
                }
            } catch (final RuntimeException e) {
                /* No Op */
            }
        }
    }
}

这只是语法糖，以确保没有人忘记放入更长、更丑陋的 if (null != con) { try { con.close () } catch (SQLException e) {} }(通常对 ResultSet、Statement 和 Connection 重复 3 次)；并消除了我们的格式化程序将在触及数据库的每个代码块上变成全屏附带清理代码的“视觉噪音”。

(其中的 Lock 支持是针对潜在异常的一些相关但令人讨厌的死锁状态，这与数据库根本没有太大关系，但我们在类似的情况下使用了减少某些线程同步代码中的线路噪音的方法。这是来自一个 MMO 服务器，它一次可能有 4,000 个 Activity 线程，试图操纵游戏对象和 SQL 表。)