java - 从mysql获取大数据的连接异常

Question

我正在尝试为用户获取一些数据。当数据小于 5000 行 时，代码运行完美。但是当我尝试获取大小为 25000 + 行的数据时，应用程序崩溃并给出以下 StackTrace 异常:

org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Cannot open connection
    Caused by: org.hibernate.exception.JDBCConnectionException: Cannot open connection
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:74)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
        at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:420)
        at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
        at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:129)
        at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:57)
        at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1290)
        at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:516)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:315)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:234)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:93)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)
        at $Proxy1.getAggregatedFeaturesByCode(Unknown Source)
        at com.ilrn.session.permissions.PermissionManagerImpl.handleFeatureGroup(PermissionManagerImpl.java:164)
        at com.ilrn.session.permissions.PermissionManagerImpl.buildUserPermissionMap(PermissionManagerImpl.java:142)
        at com.ilrn.entity.sql.SqlUser.getUserPermissions(SqlUser.java:2720)
        at com.ilrn.controller.modules.UsersReport.addResults(UsersReport.java:384)
        at com.ilrn.controller.modules.UsersReport.gen_main(UsersReport.java:229)
        at com.ilrn.controller.modules.UsersReport.generate(UsersReport.java:156)
        at com.ilrn.controller.ShellModule.generateShell(ShellModule.java:2016)
        at com.ilrn.controller.Application.processRequest(Application.java:1462)
        at com.ilrn.controller.BCAServlet.performTask(BCAServlet.java:197)
        at com.ilrn.controller.BCAServlet.doPost(BCAServlet.java:172)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:174)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:77)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at com.ilrn.session.webservices.axisws.clientactivity.ForceResponseCode200Filter.doFilter(ForceResponseCode200Filter.java:24)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at com.ilrn.util.RequestDumperFilter.doFilter(RequestDumperFilter.java:273)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at com.ilrn.controller.filters.ReferenceFilter.doFilter(ReferenceFilter.java:57)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at com.ilrn.controller.filters.WebContextFilter.doFilter(WebContextFilter.java:60)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at com.ilrn.util.DynamicRequestsFilter.doFilter(DynamicRequestsFilter.java:113)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
        at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
        at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
        at java.lang.Thread.run(Thread.java:595)
    Caused by: com.mysql.jdbc.CommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket.

    This is usually caused by a limit on the number of sockets imposed by the operating system. This limit is usually configurable. 

    For Unix-based platforms, see the manual page for the 'ulimit' command. Kernel or system reconfiguration may also be required.

    For Windows-based platforms, see Microsoft Knowledge Base Article 196271 (Q196271).
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1112)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:344)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2332)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2369)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2153)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:377)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
        at java.sql.DriverManager.getConnection(DriverManager.java:525)
        at java.sql.DriverManager.getConnection(DriverManager.java:140)
        at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:291)
        at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:277)
        at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:259)
        at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:241)
        at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
        at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:417)
        ... 56 more
    Caused by: java.net.BindException: Address already in use: connect
        at java.net.PlainSocketImpl.socketConnect(Native Method)
        at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
        at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
        at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:364)
        at java.net.Socket.connect(Socket.java:507)
        at java.net.Socket.connect(Socket.java:457)
        at java.net.Socket.<init>(Socket.java:365)
        at java.net.Socket.<init>(Socket.java:207)
        at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:257)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:294)
        ... 70 more


我有一点线索，应用程序正在尝试建立多个连接并超过连接数限制？我是否需要按照堆栈跟踪本身的建议增加此限制

这通常是由操作系统强加的套接字数量限制引起的。这个限制通常是可配置的。

如果可以，该怎么做？任何帮助将不胜感激。

Answer 1

增加连接池的最大大小，并使用批量更新:

设置批量大小

hibernate.jdbc.batch_size 20

批量更新是这样的:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

ScrollableResults customers = session.getNamedQuery("GetCustomers")
    .setCacheMode(CacheMode.IGNORE)
    .scroll(ScrollMode.FORWARD_ONLY);
int count=0;
while ( customers.next() ) {
    Customer customer = (Customer) customers.get(0);
    customer.updateStuff(...);
    if ( ++count % 20 == 0 ) {
        //flush a batch of updates and release memory:
        session.flush();
        session.clear();
    }
}

tx.commit();
session.close();

reference here .如果您仍然遇到同样的问题，请发布您的 c3p0 配置，可能那里有问题。