search

gravitee-io / issues

Gravitee.io - API Platform - Issues
64 stars 26 forks source link

JDBC Deadlock on Commands and Events when using multiple instance of APIM #9113

Closed exalate-issue-sync[bot] closed 1 year ago

exalate-issue-sync[bot] commented 1 year ago

Description

There are deadlocks on Commands and Events queries when using APIM with SQLServer and multiple instances of Management API and Gateway.

For instance:

gio_apim_management_api-2 | Caused by: org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback; SQL [select * from [commands] c left join command_acknowledgments ca on c.id = ca.command_id left join command_tags ct on c.id = ct.command_id where c.id = ?]; An error occurred during the current command (Done status 0). Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Done status 0). Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 gio_apim_management_api-2 | at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:271)
 gio_apim_management_api-2 | at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
 gio_apim_management_api-2 | at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
 gio_apim_management_api-2 | at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
 gio_apim_management_api-2 | at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
 gio_apim_management_api-2 | at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:744)
 gio_apim_management_api-2 | at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:773)
 gio_apim_management_api-2 | at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:789)
 gio_apim_management_api-2 | at io.gravitee.repository.jdbc.management.JdbcCommandRepository.findById(JdbcCommandRepository.java:109)
 gio_apim_management_api-2 | ... 34 common frames omitted
 gio_apim_management_api-2 | Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Done status 0). Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 gio_apim_management_api-2 | at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
 gio_apim_management_api-2 | at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer$FetchBufferTokenHandler.onDone(SQLServerResultSet.java:5439)
 gio_apim_management_api-2 | at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:104)
 gio_apim_management_api-2 | at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:42)
 gio_apim_management_api-2 | at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:5542)
 gio_apim_management_api-2 | at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1821)
 gio_apim_management_api-2 | at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1079)
 gio_apim_management_api-2 | at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
 gio_apim_management_api-2 | at org.springframework.jdbc.core.JdbcTemplate$RowCallbackHandlerResultSetExtractor.extractData(JdbcTemplate.java:1695)
 gio_apim_management_api-2 | at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:723)
 gio_apim_management_api-2 | at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
 gio_apim_management_api-2 | ... 39 common frames omitted
 gio_apim_management_api-2 | 07:38:36.375 [searchindexer-1] ERROR o.s.s.s.TaskUtils$LoggingErrorHandler - Unexpected error occurred in scheduled task
 gio_apim_management_api-2 | io.gravitee.rest.api.service.exceptions.TechnicalManagementException: An error occurs while trying to delete command 137b3432-69ae-486c-bb34-3269ae186cab
 gio_apim_management_api-2 | at io.gravitee.rest.api.service.impl.CommandServiceImpl.delete(CommandServiceImpl.java:123)
 gio_apim_management_api-2 | at jdk.internal.reflect.GeneratedMethodAccessor397.invoke(Unknown Source)
 gio_apim_management_api-2 | at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 gio_apim_management_api-2 | at java.base/java.lang.reflect.Method.invoke(Unknown Source)
 gio_apim_management_api-2 | at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
 gio_apim_management_api-2 | at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
 gio_apim_management_api-2 | at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
 gio_apim_management_api-2 | at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
 gio_apim_management_api-2 | at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
 gio_apim_management_api-2 | at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
 gio_apim_management_api-2 | at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
 gio_apim_management_api-2 | at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
 gio_apim_management_api-2 | at jdk.proxy3/jdk.proxy3.$Proxy72.delete(Unknown Source)
 gio_apim_management_api-2 | at io.gravitee.rest.api.services.search.ScheduledSearchIndexerService.lambda$processCommands$1(ScheduledSearchIndexerService.java:121)
 gio_apim_management_api-2 | at java.base/java.util.ArrayList.forEach(Unknown Source)
 gio_apim_management_api-2 | at io.gravitee.rest.api.services.search.ScheduledSearchIndexerService.processCommands(ScheduledSearchIndexerService.java:119)
 gio_apim_management_api-2 | at io.gravitee.rest.api.services.search.ScheduledSearchIndexerService.lambda$run$0(ScheduledSearchIndexerService.java:109)
 gio_apim_management_api-2 | at java.base/java.lang.Iterable.forEach(Unknown Source)
 gio_apim_management_api-2 | at io.gravitee.rest.api.services.search.ScheduledSearchIndexerService.run(ScheduledSearchIndexerService.java:106)
 gio_apim_management_api-2 | at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
 gio_apim_management_api-2 | at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:95)
 gio_apim_management_api-2 | at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
 gio_apim_management_api-2 | at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
 gio_apim_management_api-2 | at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
 gio_apim_management_api-2 | at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
 gio_apim_management_api-2 | at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
 gio_apim_management_api-2 | at java.base/java.lang.Thread.run(Unknown Source)
 gio_apim_management_api-2 | Caused by: io.gravitee.repository.exceptions.TechnicalException: Failed to find command by id
 gio_apim_management_api-2 | at io.gravitee.repository.jdbc.management.JdbcCommandRepository.findById(JdbcCommandRepository.java:125)
 gio_apim_management_api-2 | at io.gravitee.repository.jdbc.management.JdbcCommandRepository.findById(JdbcCommandRepository.java:42)
 gio_apim_management_api-2 | at jdk.internal.reflect.GeneratedMethodAccessor104.invoke(Unknown Source)
 gio_apim_management_api-2 | at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 gio_apim_management_api-2 | at java.base/java.lang.reflect.Method.invoke(Unknown Source)
 gio_apim_management_api-2 | at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
 gio_apim_management_api-2 | at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
 gio_apim_management_api-2 | at jdk.proxy3/jdk.proxy3.$Proxy156.findById(Unknown Source)
 gio_apim_management_api-2 | at io.gravitee.rest.api.service.impl.CommandServiceImpl.delete(CommandServiceImpl.java:116)
 gio_apim_management_api-2 | ... 26 common frames omitted
exalate-issue-sync[bot] commented 1 year ago

This issue has been fixed, a new version will be available soon