英文:
WAS gets hung WSVR0605W due to jdbc preparedstatement execute WCS
问题
我试图在一个函数内执行三个不同的预处理语句，基本上在三个不同的表中进行插入操作。
文件 registration.java
public String regPoints(Timestamp time2, String usersID2) throws PDHException{
// 函数的实现代码...
return msg;
}
private Connection createConnection() throws SQLException, Exception {
// createConnection 方法的实现代码...
}
当我从本地的 RAD 或本地环境的网页调用 regPoints 函数时，它能够正常工作。但是当它在测试环境中运行时却无法工作。当我检查日志时，我可以看到 SystemOut.log 中出现了以下警告，我也检查了数据库。它从不插入任何数据，因为最初的插入是它挂起的地方。
[日期 时间] 00000096 ThreadMonitor W WSVR0605W: 线程 "WebContainer : 2" (00000118) 已经活动了 324493 毫秒，可能已挂起。服务器中总共可能有 1 个线程挂起。
at java.net.SocketInputStream.socketRead0(Native Method)
// ... (堆栈跟踪)
这与 JDBC 版本有关吗？还是有其他特定原因导致了这个问题？
英文:
I'm trying to execute three different prepared statements inside a function which basically does an insert in three different tables.
File registration.java
public String regPoints(Timestamp time2, String usersID2) throws PDHException{
String METHODNAME="regPoints";
String msg = "Failure";
Long lUserid,ldataid,lpointsid = 0L;Connection conn = null;
PreparedStatement ps1=null,ps2=null,ps3 = null;
try{lUserid = ECKeyManager.singleton().getNextKey("hp_loyalty_users");
ldataid = ECKeyManager.singleton().getNextKey("hp_loyalty_data");
lpointsid = ECKeyManager.singleton().getNextKey("hp_loyalty_points");
conn = createConnection();
ps1=conn.prepareStatement(INSERT_USERS_REGISTRATION);
ps2=conn.prepareStatement(INSERT_DATA_REGISTRATION);
ps3=conn.prepareStatement(INSERT_POINTS_REGISTRATION);
conn.setAutoCommit(false);
logMsg(Level.INFO, METHODNAME,"Registration Started for WCS userid: "+usersID2);
logMsg(Level.INFO, METHODNAME,"Query started for users table to register");
ps1.setLong(1, lUserid);
ps1.setLong(2, Long.valueOf(usersID2));
ps1.setInt(3, regPoints);
ps1.setInt(4, Constants.LOYALTY_REG_DEFAULT);
ps1.setTimestamp(5,time2);
ps1.setTimestamp(6,time2);
ps1.setString(7,Constants.LOYALTY_TRUE);
ps1.addBatch();
logMsg(Level.INFO, METHODNAME,"Query started for data table to register");
ps2.setLong(1, ldataid);
ps2.setLong(2, lUserid);
ps2.setString(3, Constants.LOYALTY_NA);
ps2.setString(4, Constants.LOYALTY_NA);
ps2.setString(5, Constants.LOYALTY_NA);
ps2.setFloat(6, Constants.LOYALTY_REG_DEFAULT);
ps2.setInt(7, Constants.LOYALTY_REG_DEFAULT);
ps2.setTimestamp(8,time2);
ps2.setInt(9, regPoints);
ps2.setInt(10, Constants.LOYALTY_REG_DEFAULT);
ps2.setString(11, Constants.LOYALTY_REGISTRATION);
ps2.setTimestamp(12,time2);
ps2.setTimestamp(13,time2);
ps2.setInt(14, Constants.LOYALTY_REG_DEFAULT);
ps2.setString(15, Constants.LOYALTY_NA);
ps2.addBatch();
logMsg(Level.INFO, METHODNAME,"Query started for points table to register");
ps3.setLong(1, lpointsid);
ps3.setLong(2, lUserid);
ps3.setInt(3, regPoints);
ps3.setString(4, Constants.LOYALTY_FALSE);
ps3.setLong(5, ldataid);
ps3.setTimestamp(6,time2);
ps3.setString(7, Constants.LOYALTY_FALSE);
ps3.setTimestamp(8,time2);
ps3.addBatch();
int[] users = ps1.executeBatch();
logMsg(Level.INFO, METHODNAME,"rows processed in users table: "+users.length);
int[] data = ps2.executeBatch();
logMsg(Level.INFO, METHODNAME,"rows processed in data table: "+data.length);
int[] points = ps3.executeBatch();
logMsg(Level.INFO, METHODNAME,"rows processed in data table: "+points.length);
conn.commit();
msg = "Success";
logMsg(Level.INFO, METHODNAME,"Registration Completed for WCS userid: "+usersID2);
}catch (Exception e) {
if(null!=conn)
{
logMsg(Level.SEVERE, METHODNAME,"Registration Failed for WCS userid: "+usersID2);
try {
conn.rollback();
throw new PDHException(e);
} catch (SQLException e1) {
throw new PDHException(e1);
}
}
}finally {
JDBCUtils.closeStatement(ps1);
JDBCUtils.closeStatement(ps2);
JDBCUtils.closeStatement(ps3);
JDBCUtils.closeConnection(conn);
}
return msg;
}
createConnection method - I'm using ojdbc6.jar
private Connection createConnection() throws SQLException, Exception
{
final String METHODNAME = "createConnection";
Connection dbConn = null;
Class.forName("oracle.jdbc.driver.OracleDriver");
dbConn = DriverManager.getConnection(jdbcURL, CipherTextUtil.decodeHexString(userName) , CipherTextUtil.decodeHexString(pass));
dbConn.setAutoCommit(false);
return dbConn;
}
When I call the regPoints function from a webpage in my local RAD or local environment its working perfectly fine. But the same doesn't work when it's running on a test environment. When I checked the logs I could see the below warning in the SystemOut.log and I had checked the database too. It never inserts any data because the initial insert is the point where it gets hung
[7/28/20 5:26:42:251 UTC] 00000096 ThreadMonitor W WSVR0605W: Thread "WebContainer : 2" (00000118) has been active for 324493 milliseconds and may be hung. There is/are 1 thread(s) in total in the server that may be hung.
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:165)
at java.net.SocketInputStream.read(SocketInputStream.java:134)
at oracle.net.ns.Packet.receive(Packet.java:282)
at oracle.net.ns.DataPacket.receive(DataPacket.java:103)
at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:230)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:175)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:100)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:85)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:122)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:78)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1179)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
at oracle.jdbc.driver.OraclePreparedStatement.executeForRowsWithTimeout(OraclePreparedStatement.java:9870)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9974)
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:213)
at com.hp.commerce.hployalty.commands.HpLoyaltyTranscationCmdImpl.registrationFlow(HpLoyaltyTranscationCmdImpl.java:739)
at com.hp.commerce.hployalty.commands.HpLoyaltyTranscationCmdImpl.performExecute(HpLoyaltyTranscationCmdImpl.java:168)
at com.ibm.commerce.command.ECCommandTarget.executeCommand(ECCommandTarget.java:157)
at com.ibm.ws.cache.command.CommandCache.executeCommand(CommandCache.java:332)
at com.ibm.websphere.command.CacheableCommandImpl.execute(CacheableCommandImpl.java:167)
at com.ibm.commerce.command.MeasuredCacheableCommandImpl.execute(MeasuredCacheableCommandImpl.java:68)
at com.ibm.commerce.command.AbstractECTargetableCommand.execute(AbstractECTargetableCommand.java:199)
at com.hp.commerce.usermanagement.commands.ExtUserRegistrationAddCmdImpl.performExecute(ExtUserRegistrationAddCmdImpl.java:251)
at com.ibm.commerce.command.ECCommandTarget.executeCommand(ECCommandTarget.java:157)
at com.ibm.ws.cache.command.CommandCache.executeCommand(CommandCache.java:332)
at com.ibm.websphere.command.CacheableCommandImpl.execute(CacheableCommandImpl.java:167)
at com.ibm.commerce.command.MeasuredCacheableCommandImpl.execute(MeasuredCacheableCommandImpl.java:68)
at com.ibm.commerce.command.AbstractECTargetableCommand.execute(AbstractECTargetableCommand.java:199)
at com.ibm.commerce.component.BaseComponentImpl.executeCommand(BaseComponentImpl.java:285)
at com.ibm.commerce.component.WebAdapterComponentImpl.executeCommand(WebAdapterComponentImpl.java:46)
at com.ibm.commerce.component.objimpl.WebAdapterServiceBeanBase.executeCommand(WebAdapterServiceBeanBase.java:58)
at com.ibm.commerce.component.objects.EJSLocalStatelessWebAdapterService_ce749a4a.executeCommand(EJSLocalStatelessWebAdapterService_ce749a4a.java:31)
at com.ibm.commerce.component.objects.WebAdapterServiceAccessBean.executeCommand(WebAdapterServiceAccessBean.java:160)
at com.ibm.commerce.webcontroller.WebControllerHelper.executeCommand(WebControllerHelper.java:2781)
at com.ibm.commerce.struts.BaseAction.invokeService(BaseAction.java:1600)
at com.ibm.commerce.struts.LTPATokenGenerationEnabledBaseAction.invokeService(LTPATokenGenerationEnabledBaseAction.java:129)
at com.ibm.commerce.struts.BaseAction.executeAction(BaseAction.java:663)
at com.ibm.commerce.struts.BaseAction.execute(BaseAction.java:152)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at com.ibm.commerce.struts.ECActionServlet.processRequest(ECActionServlet.java:229)
at com.ibm.commerce.struts.ECActionServlet.doPost(ECActionServlet.java:184)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:595)
at com.ibm.commerce.struts.ECActionServlet.service(ECActionServlet.java:718)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:668)
at com.ibm.ws.cache.servlet.ServletWrapper.serviceProxied(ServletWrapper.java:307)
at com.ibm.ws.cache.servlet.CacheHook.handleFragment(CacheHook.java:562)
at com.ibm.ws.cache.servlet.CacheHook.handleServlet(CacheHook.java:255)
at com.ibm.ws.cache.servlet.ServletWrapper.service(ServletWrapper.java:259)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1233)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:782)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:481)
at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:178)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.invokeTarget(WebAppFilterChain.java:136)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:97)
at com.hp.ecom.b2c.service.AuthenticationFilter.doFilter(AuthenticationFilter.java:132)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
at com.ibm.commerce.foundation.server.services.servlet.filter.HttpSecurityFilter.doFilter(HttpSecurityFilter.java:268)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
at com.ibm.commerce.dynacache.filter.CacheFilter$1.run(CacheFilter.java:390)
at com.ibm.commerce.dynacache.filter.CacheFilter.doFilter(CacheFilter.java:553)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
at com.ibm.commerce.webcontroller.RuntimeServletFilter.doFilterAction(RuntimeServletFilter.java:831)
at com.ibm.commerce.webcontroller.RuntimeServletFilter.access$0(RuntimeServletFilter.java:614)
at com.ibm.commerce.webcontroller.RuntimeServletFilter$1.run(RuntimeServletFilter.java:458)
at com.ibm.commerce.webcontroller.RuntimeServletFilter.doFilter(RuntimeServletFilter.java:500)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
at com.hp.filter.LogFilter.doFilter(LogFilter.java:37)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
at com.hp.seo.filter.SEOSecurityCheckFilter.doFilter(SEOSecurityCheckFilter.java:161)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
at com.hp.seo.filter.SEOPdpCLPRedirectFilter.doFilter(SEOPdpCLPRedirectFilter.java:360)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
at com.hp.seo.filter.SEOHPCoBrandDynamicToStaticFilter.doFilter(SEOHPCoBrandDynamicToStaticFilter.java:185)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
at com.hp.seo.filter.SEOContentViewRedirectFilter.doFilter(SEOContentViewRedirectFilter.java:218)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
at com.hp.seo.filter.SEOCaseConversionFilter.doFilter(SEOCaseConversionFilter.java:347)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
at com.hp.vanity.filter.SEOVanityURLFilter.doFilter(SEOVanityURLFilter.java:98)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:967)
at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1107)
at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:4047)
at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:304)
at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1016)
at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1817)
at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:213)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:463)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:530)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:316)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:287)
at com.ibm.ws.ssl.channel.impl.SSLConnectionLink.determineNextChannel(SSLConnectionLink.java:1187)
at com.ibm.ws.ssl.channel.impl.SSLConnectionLink.readyInboundPostHandshake(SSLConnectionLink.java:768)
at com.ibm.ws.ssl.channel.impl.SSLConnectionLink$MyHandshakeCompletedCallback.complete(SSLConnectionLink.java:464)
at com.ibm.ws.ssl.channel.impl.SSLUtils.handleHandshake(SSLUtils.java:1137)
at com.ibm.ws.ssl.channel.impl.SSLHandshakeIOCallback.complete(SSLHandshakeIOCallback.java:87)
at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1892)
Is this something to do with jdbc version? Or do we have any specific reasons for this issue?
答案1
得分: 0
Next time, when you want to know the JDBC version, just do "java -jar ojdbc8.jar". Also, as suggested, it is better to use the latest driver. If you have at least JDK8 then you can use 19.7 even when your database is 12c. You can get JDBC drivers from Central Maven or can download them from here.
➜ lib **java -jar ojdbc8.jar**
Oracle 19.7.0.0.0 JDBC 4.2 compiled with javac 1.8.0_241 on Tue_Feb_11_04:20:00_PST_2020
#Default Connection Properties Resource
#Tue Jul 28 11:20:17 PDT 2020
***** JCE UNLIMITED STRENGTH IS INSTALLED ****
英文:
Next time, when you want to know the JDBC version, just do "java -jar ojdbc8.jar". Also, as suggested, it is better to use the latest driver. If you have atleast JDK8 then you can use 19.7 even when your database is 12c. You can get JDBC drivers from [Central Maven] or can download them from here.
➜ lib **java -jar ojdbc8.jar**
Oracle 19.7.0.0.0 JDBC 4.2 compiled with javac 1.8.0_241 on Tue_Feb_11_04:20:00_PST_2020
#Default Connection Properties Resource
#Tue Jul 28 11:20:17 PDT 2020
***** JCE UNLIMITED STRENGTH IS INSTALLED ****
答案2
得分: 0
我成功地从连接池中获取了连接，而不是创建新连接，以避免上述问题。默认情况下，WCS不允许使用ServerJDBCHelperAccessBean执行批处理语句。因此，我从BaseJDBCHelper类中获取了数据源并获得了连接。
DataSource ds = BaseJDBCHelper.getDataSource();
Connection dbConn = ds.getConnection();
英文:
I managed to take the connection from connection pool instead of creating one to avoid the above issue. By default WCS doesnt allow executing batch statements using serverjdbchelperaccessbean. So i took the datasource from basejdbchelper class and got the connection.
DataSource ds = BaseJDBCHelper.getDataSource();
Connection dbConn = ds.getConnection();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论