search

wso2 / product-is

Welcome to the WSO2 Identity Server source code! For info on working with the WSO2 Identity Server repository and contributing code, click the link below.
http://wso2.github.io/
Apache License 2.0
748 stars 728 forks source link

Mysql DB deadlock error in challenge questions #6427

Closed aaujayasena closed 1 week ago

aaujayasena commented 5 years ago

Labels: Priority: High Severity: critical Type: bug Affected: 5.9.0- alpha3

Description: Concurrent users 50+ get mysql dead lock issue intermittently. This happens when several indexes being updated in the table. As a solution, can design the code with retry the transaction if it fail due to a dead lock.

Log: 

TID: [-1234] [api] [2019-09-11 13:40:25,967] [02116aff-1c78-445a-9508-f86ac10a6bb7] ERROR {org.wso2.carbon.identity.rest.api.user.challenge.v1.core.UserChallengeService} - errorCode: CQM-10005 | message: Server Encountered an error while setting answers to the user challenges. org.wso2.carbon.identity.recovery.IdentityRecoveryServerException: Error while removing challenge questions of user 'isTestUser_832.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at org.wso2.carbon.identity.base.IdentityException.error(IdentityException.java:103)
    at org.wso2.carbon.identity.recovery.util.Utils.handleServerException(Utils.java:195)
    at org.wso2.carbon.identity.recovery.ChallengeQuestionManager.setChallengesOfUser(ChallengeQuestionManager.java:601)
    at org.wso2.carbon.identity.rest.api.user.challenge.v1.core.UserChallengeService.setChallengeAnswersOfUser(UserChallengeService.java:93)
    at org.wso2.carbon.identity.rest.api.user.challenge.v1.impl.MeApiServiceImpl.addChallengeAnswersForLoggedInUser(MeApiServiceImpl.java:38)
    at org.wso2.carbon.identity.rest.api.user.challenge.v1.MeApi.addChallengeAnswersForLoggedInUser(MeApi.java:96)
    at sun.reflect.GeneratedMethodAccessor178.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:179)
    at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96)
    at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:193)
    at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:103)
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59)
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:96)
    at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308)
    at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121)
    at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:267)
    at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234)
    at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208)
    at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160)
    at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:216)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:301)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doPost(AbstractHTTPServlet.java:220)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:276)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.wso2.carbon.identity.context.rewrite.valve.TenantContextRewriteValve.invoke(TenantContextRewriteValve.java:80)
    at org.wso2.carbon.identity.authz.valve.AuthorizationValve.invoke(AuthorizationValve.java:92)
    at org.wso2.carbon.identity.auth.valve.AuthenticationValve.invoke(AuthenticationValve.java:93)
    at org.wso2.carbon.tomcat.ext.valves.CompositeValve.continueInvocation(CompositeValve.java:99)
    at org.wso2.carbon.tomcat.ext.valves.TomcatValveContainer.invokeValves(TomcatValveContainer.java:49)
    at org.wso2.carbon.tomcat.ext.valves.CompositeValve.invoke(CompositeValve.java:62)
    at org.wso2.carbon.tomcat.ext.valves.CarbonStuckThreadDetectionValve.invoke(CarbonStuckThreadDetectionValve.java:146)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:678)
    at org.wso2.carbon.tomcat.ext.valves.CarbonContextCreatorValve.invoke(CarbonContextCreatorValve.java:57)
    at org.wso2.carbon.tomcat.ext.valves.RequestCorrelationIdValve.invoke(RequestCorrelationIdValve.java:112)
    at org.wso2.carbon.tomcat.ext.valves.RequestEncodingValve.invoke(RequestEncodingValve.java:49)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:853)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1587)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while getting claim attribute for user : isTestUser_832
    at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.doSetUserClaimValues(JDBCUserStoreManager.java:2199)
    at org.wso2.carbon.user.core.common.AbstractUserStoreManager.setUserClaimValues(AbstractUserStoreManager.java:2490)
    at org.wso2.carbon.identity.recovery.util.Utils.setClaimInUserStoreManager(Utils.java:272)
    at org.wso2.carbon.identity.recovery.ChallengeQuestionManager.setChallengesOfUser(ChallengeQuestionManager.java:598)
    ... 55 more
Caused by: org.wso2.carbon.user.core.UserStoreException: Error occurred while updating string values to database.
    at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.updateProperties(JDBCUserStoreManager.java:3354)
    at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.doSetUserClaimValues(JDBCUserStoreManager.java:2186)
    ... 58 more
Caused by: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1163)
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1817)
    at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1307)
    at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
    at sun.reflect.GeneratedMethodAccessor164.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
    at com.sun.proxy.$Proxy54.executeBatch(Unknown Source)
    at org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager.updateProperties(JDBCUserStoreManager.java:3331)
    ... 59 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2124)
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1801)
    ... 67 more

wso2is.log

isharak commented 1 week ago

This issue is being closed due to extended inactivity. Please feel free to reopen it if further attention is needed. Thank you for helping us keep the issue list relevant and focused!