WAS gets hung WSVR0605W due to jdbc preparedstatement execute WCS

Tags: , , ,



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?

Answer

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();


Source: stackoverflow