问题描述
服务采用JDBC进行数据库连接，在Jboss的deploy文件下放入数据源配置文件；下面是第一段报错信息：2015-01-2723:27:10,676WARN[org.jboss.resource.connectionmanager.TxConnectionManager](ajp-172.16.2.6-8009-63)Connectionerroroccured:org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@16b5a8b6[state=NORMALmc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@18232e85handles=0lastUse=1422372303124permit=falsetrackByTx=falsemcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@5fc8b669context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@723fd89fxaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@2dc32a24txSync=null]com.microsoft.sqlserver.jdbc.SQLServerException:Readtimedoutatcom.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1509)atcom.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1547)atcom.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:3295)atcom.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:4360)atcom.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:700)atcom.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:600)atcom.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4003)atcom.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1550)atcom.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:160)atcom.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:133)atcom.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:573)atorg.jboss.resource.adapter.jdbc.CheckValidConnectionSQL.isValidConnection(CheckValidConnectionSQL.java:58)atorg.jboss.resource.adapter.jdbc.BaseWrapperManagedConnectionFactory.isValidConnection(BaseWrapperManagedConnectionFactory.java:559)atorg.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkValid(BaseWrapperManagedConnection.java:321)atorg.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.matchManagedConnections(LocalManagedConnectionFactory.java:402)atorg.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:219)atorg.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:622)atorg.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:404)atorg.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:381)atorg.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:496)atorg.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:941)atorg.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:89)ategret.business.order.impl.OrderScheduleManagerImpl.getScheduleListByBarcode(OrderScheduleManagerImpl.java:821)ategret.ehis.ws.EHisWS.getScheduleListByBarcode(EHisWS.java:1047)atsun.reflect.GeneratedMethodAccessor315.invoke(UnknownSource)atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)atjava.lang.reflect.Method.invoke(Method.java:597)atorg.jboss.wsf.container.jboss50.invocation.InvocationHandlerJSE.invoke(InvocationHandlerJSE.java:108)atorg.jboss.ws.core.server.ServiceEndpointInvoker.invoke(ServiceEndpointInvoker.java:222)atorg.jboss.wsf.stack.jbws.RequestHandlerImpl.processRequest(RequestHandlerImpl.java:474)atorg.jboss.wsf.stack.jbws.RequestHandlerImpl.handleRequest(RequestHandlerImpl.java:295)atorg.jboss.wsf.stack.jbws.RequestHandlerImpl.doPost(RequestHandlerImpl.java:205)atorg.jboss.wsf.stack.jbws.RequestHandlerImpl.handleHttpRequest(RequestHandlerImpl.java:131)atorg.jboss.wsf.common.servlet.AbstractEndpointServlet.service(AbstractEndpointServlet.java:85)atjavax.servlet.http.HttpServlet.service(HttpServlet.java:717)atorg.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)atorg.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)atorg.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)atorg.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)atorg.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)atorg.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)atorg.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)atorg.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:190)atorg.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:92)atorg.jboss.web.tomcat.security.SecurityContextEstablishmentValve.process(SecurityContextEstablishmentValve.java:126)atorg.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:70)atorg.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)atorg.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)atorg.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)atorg.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)atorg.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)atorg.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:436)atorg.apache.coyote.ajp.AjpProtocol$AjpConnectionHandler.process(AjpProtocol.java:384)atorg.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)atjava.lang.Thread.run(Thread.java:662)
这段超时连续出现了很多次之后出现了下面的错误：2015-01-2723:27:19,942ERROR[STDERR](pool-22-thread-9)com.microsoft.sqlserver.jdbc.SQLServerException:该连接已关闭。2015-01-2723:27:19,942ERROR[STDERR](pool-22-thread-9)atcom.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:130)2015-01-2723:27:19,942ERROR[STDERR](pool-22-thread-9)atcom.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:294)2015-01-2723:27:19,942ERROR[STDERR](pool-22-thread-9)atcom.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:1980)2015-01-2723:27:19,942ERROR[STDERR](pool-22-thread-9)atorg.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:465)2015-01-2723:27:19,942ERROR[STDERR](pool-22-thread-9)atorg.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:460)2015-01-2723:27:19,942ERROR[STDERR](pool-22-thread-9)atorg.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:243)2015-01-2723:27:19,942ERROR[STDERR](pool-22-thread-9)ategret.ie.dao.impl.IEReceiveMessageDAOImpl.getIEMessage(IEReceiveMessageDAOImpl.java:1412)2015-01-2723:27:19,942ERROR[STDERR](pool-22-thread-9)ategret.ie.IEConsumeMessageProcessing.run(IEConsumeMessageProcessing.java:336)2015-01-2723:27:19,942ERROR[STDERR](pool-22-thread-9)atjava.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)2015-01-2723:27:19,942ERROR[STDERR](pool-22-thread-9)atjava.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)2015-01-2723:27:19,942ERROR[STDERR](pool-22-thread-9)atjava.lang.Thread.run(Thread.java:662)
下面分别附上两个错误对应的自己写的代码：第一段：publicOrderSchedule[]getScheduleListByBarcode(Stringbarcode,Stringpatientid,Stringdeptno)throwsException{ArrayList<OrderSchedule>scheduleList=newArrayList<OrderSchedule>();List<OrderScheduleEvents>orderScheduleEvent=newArrayList<OrderScheduleEvents>();Connectionconn=null;Statementstmt1=null;Statementstmt2=null;ResultSetrset=null;ResultSeteventSet=null;try{conn=egretdataSource.getConnection();//第一个错误是这里stmt1=conn.createStatement();stmt2=conn.createStatement();
第二段：@OverridepublicList<IEMessage>getIEMessage(StringserviceIds)throwsException{//Connectioncon=null;PreparedStatementps=null;ResultSetrs=null;List<IEMessage>list=newArrayList<IEMessage>();Stringsql="selecttop100*fromie_messagewith(nolock)wheremq_flag=0";if(serviceIds!=null)sql+="andservice_idin('BS005','BS006','BS019')";elsesql+="andservice_idnotin('BS005','BS006','BS019')";try{con=this.getCon();ps=con.prepareStatement(sql);//第二个错误是这里抛出的rs=ps.executeQuery();IEMessagemessage=null;
解决方案
解决方案二：
第一个问题我猜想是跟数据库表性能有关系，数据量太大导致产生死锁；第二个问题完全不理解啊，这个数据源的datasource应该没出错，否则整个程序会挂掉，怀疑是获取到的connection有问题，但是为什么别的地方也在用同一个datasource获取connection就不会报错？