Duplicate key violation on thrown on CommandGateway for Postgres using Hikari CP

Kevin_van_Ingen · February 19, 2018, 3:00pm

Hi all,
I have a setup where I have two load balanced hosts connected to Postgres using a Hikari connection pool. I noticed in the log a duplicate key violation. I happend six times over the last four months. These are important dependencies:

Gradle: org.axonframework:axon-spring:3.0.6
Gradle: org.axonframework:axon-spring-boot-autoconfigure:3.0.6
Gradle: org.axonframework:axon-spring-boot-starter:3.0.6
Gradle: org.springframework.boot:spring-boot-starter-jdbc:1.5.7.RELEASE
Gradle: org.postgresql:postgresql:9.4.1212
Gradle: com.zaxxer:HikariCP:2.6.1

https://github.com/AxonFramework/AxonFramework/issues/313 which seems highly related to what I am experiencing, however based on this I would expect that postgres duplicate key errors are handled now.

The stacktrace:

`
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “events_aggregate_id_sequence_number_key”
Detail: Key (aggregate_id, sequence_number)=(618d1107-7708-4693-a521-1e7e9b9f0fa1, 33) already exists.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:460)
at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:793)
at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1659)
at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:125)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
at org.axonframework.common.jdbc.JdbcUtils.executeBatch(JdbcUtils.java:119)
at org.axonframework.eventsourcing.eventstore.jdbc.JdbcEventStorageEngine.lambda$appendEvents$5(JdbcEventStorageEngine.java:180)
at org.axonframework.common.transaction.TransactionManager.executeInTransaction(TransactionManager.java:44)
at org.axonframework.eventsourcing.eventstore.jdbc.JdbcEventStorageEngine.appendEvents(JdbcEventStorageEngine.java:178)
at org.axonframework.eventsourcing.eventstore.AbstractEventStorageEngine.appendEvents(AbstractEventStorageEngine.java:85)
at org.axonframework.eventsourcing.eventstore.AbstractEventStore.prepareCommit(AbstractEventStore.java:64)
at org.axonframework.eventhandling.AbstractEventBus.doWithEvents(AbstractEventBus.java:210)
at org.axonframework.eventhandling.AbstractEventBus.lambda$null$4(AbstractEventBus.java:145)
at org.axonframework.messaging.unitofwork.MessageProcessingContext.notifyHandlers(MessageProcessingContext.java:68)
at org.axonframework.messaging.unitofwork.DefaultUnitOfWork.notifyHandlers(DefaultUnitOfWork.java:91)
at org.axonframework.messaging.unitofwork.AbstractUnitOfWork.changePhase(AbstractUnitOfWork.java:214)
at org.axonframework.messaging.unitofwork.AbstractUnitOfWork.commitAsRoot(AbstractUnitOfWork.java:83)
at org.axonframework.messaging.unitofwork.AbstractUnitOfWork.commit(AbstractUnitOfWork.java:71)
at org.axonframework.messaging.unitofwork.DefaultUnitOfWork.executeWithResult(DefaultUnitOfWork.java:80)
at org.axonframework.commandhandling.SimpleCommandBus.doDispatch(SimpleCommandBus.java:143)
at org.axonframework.commandhandling.SimpleCommandBus.doDispatch(SimpleCommandBus.java:116)
at org.axonframework.commandhandling.SimpleCommandBus.dispatch(SimpleCommandBus.java:86)
at org.axonframework.commandhandling.gateway.AbstractCommandGateway.send(AbstractCommandGateway.java:79)
at org.axonframework.commandhandling.gateway.DefaultCommandGateway.send(DefaultCommandGateway.java:95)
at org.axonframework.commandhandling.gateway.DefaultCommandGateway.sendAndWait(DefaultCommandGateway.java:113)
at ps.furby.allocation.api.SupplyItemController.updateSupply(SupplyItemController.java:112)
at sun.reflect.GeneratedMethodAccessor132.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
at org.springframework.web.servlet.FrameworkServlet.doPut(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
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:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.boot.web.filter.ApplicationContextHeaderFilter.doFilterInternal(ApplicationContextHeaderFilter.java:55)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.AbstractRequestLoggingFilter.doFilterInternal(AbstractRequestLoggingFilter.java:244)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:110)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.oauth2.provider.authentication.OAuth2AuthenticationProcessingFilter.doFilter(OAuth2AuthenticationProcessingFilter.java:176)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:96)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:108)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.cloud.sleuth.instrument.web.TraceFilter.doFilter(TraceFilter.java:169)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:106)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at io.micrometer.spring.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:106)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
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:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1457)
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)

`

Based on issue 313 I would reason that maybe Hikari is an additional complexity, but I am really not sure. Currently I am looking into ways of debugging this. Can anybody reason if/why Hikari can be part of the problem?

Thanks in advance.

Steven_van_Beelen · February 20, 2018, 8:31am

Hi Kevin,

You’re saying you’ve got ‘two load balanced hosts’.

Do you with this mean you’ve got two instances of a single Axon application running against the same EventStore?

If yes, are you also using the DistributedCommandBus in this scenario?

If the first question is yes and the second question is no (so no DistributedCommandBus), then this behavior is to be expected.

The exception you’re seeing occurs if two instances try to handle a command for the same aggregate.

Your aggregate will in this scenario be loaded in both applications, both will perform some command handling and very likely both publish one or more events.

Since one of these applications will be the first to publish the event(s), thus the first to append to the store, that event will take the incremented sequence id.

The other application is not aware of this event being stored, hence will try to append an event with the same sequence id.

If my guess on your situation was wrong though, we’ll have to discuss this a little further.

Hope this helps you Kevin!

Cheers,

Steven

Kevin_van_Ingen · February 22, 2018, 6:56am

Thank you for the response.

Yes. And no (not yet at least). I have a SimpleCommandBus at this time. I will definitely investigate the merits of a distributed one. In the end I need the proper exception in order to get retry working with either simple or the distributed bus.

As for the other part of my problem. I do think that Hikari’s exceptions are not fully complient with the current JDBC JdbcSQLErrorCodesResolver. It did not provide my with a ConcurrencyException but instead I got a BatchUpdateException. To test it, I override (and add an additional check to it) and now I do get the proper concurrency exception. I configured a retry commandGateway which works as expected now. I have soon a lot of db specific handling in the JPA driver but nothing like that in the jdbc driver. One could argue that the Hikari wrapper is not providing the proper exceptions in the trace off course.

I will try the distributed bus very soon as well.

Many thanks!!

`

public class JdbcSQLErrorCodesResolver implements PersistenceExceptionResolver {
   private static final String POSTGRES_UNIQUE_VIOLATION_CODE = "23505";

   @Override
   public boolean isDuplicateKeyViolation(Exception exception) {
      return causeIsEntityExistsException(exception);
   }

   private boolean causeIsEntityExistsException(Throwable exception) {
      return isPostgresDuplicate(exception)
            >> exception instanceof java.sql.SQLIntegrityConstraintViolationException
            >> (exception.getCause() != null && causeIsEntityExistsException(exception.getCause()));
   }

   private boolean isPostgresDuplicate(Throwable exception) {
      if(exception instanceof BatchUpdateException){
         if(((BatchUpdateException)exception).getSQLState().equals(POSTGRES_UNIQUE_VIOLATION_CODE)){
            return true;
         }
      }
      return false;
   }
}

`

allardbz · February 23, 2018, 12:33pm

Hi,

just curious, because the current implementation checks for a java.sql.SQLException. What is the fully qualified classname of the BatchUpdateException you get? As far as I can tell, the java.sql.BatchUpdateException extends SQLException, so the SQLErrorCodeResolver should just pick it up.

Cheers,

Allard

Dylan_Viljoen · October 9, 2018, 8:34am

Hi,

I realize this is an old thread but I’m also experiencing the exact same exception as reported above with a very similar setup:

axon 3.3.4
spring-boot-2.x
distributed cmd bus
postgres db
HikariCP
Jdbc event-store

`


Internal Server Error org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "domain_event_entry1_idx"
  Detail: Key (aggregateidentifier, sequencenumber)=(5bac9b2e1e4dc800014693c55b727c57cd593e0009c2a0e2, 100) already exists.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
        ... 134 common frames omitted
Wrapped by: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO DomainEventEntry (eventIdentifier, aggregateIdentifier, sequenceNumber, type, timeStamp, payloadType, payloadRevision, payload, metaData) VALUES ('1bc7012b-79a5-45ae-b7ee-a222004c62a5','5bac9b2e1e4dc800014693c55b727c57cd593e0009c2a0e2',100,'DeviceAggregate','2018-10-08T13:58:14.691242Z','mu.cibecs.core.device.endpoint.AgentHeartbeatReceivedEvent',NULL,?,?) was aborted: ERROR: duplicate key value violates unique constraint "domain_event_entry1_idx"
  Detail: Key (aggregateidentifier, sequencenumber)=(5bac9b2e1e4dc800014693c55b727c57cd593e0009c2a0e2, 100) already exists.  Call getNextException to see other errors in the batch.
        at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:148)
        at org.postgresql.core.ResultHandlerDelegate.handleError(ResultHandlerDelegate.java:50)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2184)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:481)
        at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:840)
        at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1567)
        at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
        at org.axonframework.common.jdbc.JdbcUtils.executeBatch(JdbcUtils.java:119)
        ... 128 common frames omitted
Wrapped by: org.axonframework.eventsourcing.eventstore.EventStoreException: An event for aggregate [5bac9b2e1e4dc800014693c55b727c57cd593e0009c2a0e2] at sequence [100] could not be persisted
        at org.axonframework.eventsourcing.eventstore.AbstractEventStorageEngine.handlePersistenceException(AbstractEventStorageEngine.java:167)
        at org.axonframework.eventsourcing.eventstore.jdbc.JdbcEventStorageEngine.lambda$null$4(JdbcEventStorageEngine.java:272)
        at org.axonframework.common.jdbc.JdbcUtils.executeBatch(JdbcUtils.java:121)
        at org.axonframework.eventsourcing.eventstore.jdbc.JdbcEventStorageEngine.lambda$appendEvents$5(JdbcEventStorageEngine.java:253)
        at org.axonframework.common.transaction.TransactionManager.executeInTransaction(TransactionManager.java:44)
        at org.axonframework.eventsourcing.eventstore.jdbc.JdbcEventStorageEngine.appendEvents(JdbcEventStorageEngine.java:251)
        at org.axonframework.eventsourcing.eventstore.AbstractEventStorageEngine.appendEvents(AbstractEventStorageEngine.java:140)
        at org.axonframework.eventsourcing.FilteringEventStorageEngine.appendEvents(FilteringEventStorageEngine.java:68)
        at org.axonframework.eventsourcing.eventstore.AbstractEventStore.prepareCommit(AbstractEventStore.java:66)
        at org.axonframework.eventhandling.AbstractEventBus.doWithEvents(AbstractEventBus.java:220)
        at org.axonframework.eventhandling.AbstractEventBus.lambda$null$8(AbstractEventBus.java:155)
        at org.axonframework.messaging.unitofwork.MessageProcessingContext.notifyHandlers(MessageProcessingContext.java:68)
        at org.axonframework.messaging.unitofwork.DefaultUnitOfWork.notifyHandlers(DefaultUnitOfWork.java:91)
        at org.axonframework.messaging.unitofwork.AbstractUnitOfWork.changePhase(AbstractUnitOfWork.java:221)
        at org.axonframework.messaging.unitofwork.AbstractUnitOfWork.commitAsRoot(AbstractUnitOfWork.java:82)
        at org.axonframework.messaging.unitofwork.AbstractUnitOfWork.commit(AbstractUnitOfWork.java:70)
        at org.axonframework.messaging.unitofwork.DefaultUnitOfWork.executeWithResult(DefaultUnitOfWork.java:80)
        at org.axonframework.commandhandling.SimpleCommandBus.handle(SimpleCommandBus.java:148)
        at org.axonframework.commandhandling.SimpleCommandBus.doDispatch(SimpleCommandBus.java:121)
        at org.axonframework.commandhandling.SimpleCommandBus.dispatch(SimpleCommandBus.java:85)
        at org.axonframework.springcloud.commandhandling.SpringHttpCommandBusConnector.send(SpringHttpCommandBusConnector.java:77)
        at org.axonframework.commandhandling.distributed.DistributedCommandBus.dispatch(DistributedCommandBus.java:134)
        at org.axonframework.commandhandling.gateway.AbstractCommandGateway.send(AbstractCommandGateway.java:79)
        at org.axonframework.commandhandling.gateway.DefaultCommandGateway.send(DefaultCommandGateway.java:95)
        at org.axonframework.commandhandling.gateway.DefaultCommandGateway.sendAndWait(DefaultCommandGateway.java:113)
        at mu.cibecs.common.security.message.SecureCommandGateway.sendAndWait(SecureCommandGateway.java:29)
        at mu.cibecs.core.device.DeviceController.updateLastSeen(DeviceController.java:88)
        at jdk.internal.reflect.GeneratedMethodAccessor345.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:664) [11 skipped]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) [1 skipped]
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [2 skipped]
        at mu.cibecs.common.support.web.MdcRequestFilter.doFilter(MdcRequestFilter.java:47) [2 skipped]
        at org.springframework.boot.actuate.web.trace.servlet.HttpTraceFilter.doFilterInternal(HttpTraceFilter.java:90) [6 skipped]
        at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:155) [48 skipped]
        at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:123)
        at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:108)
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:800) [15 skipped]
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:800)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1471)
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1135)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.base/java.lang.Thread.run(Thread.java:844)

`

I’m going to attempt the workaround Kevin proposed but just wanted to bring it to your attention Allard/Steven

Steven_van_Beelen · October 9, 2018, 9:15am

Hi Dylan,

Much appreciated Dylan!

It is always very helpful to be apprised of issues people are experiencing.

I assume this occurs because two different nodes are dealing with commands for the same aggregate.

In that time frame, both will (very likely) append events to the event store, using their idea of the latest sequence number.

One of those nodes/threads will definitely be faster than the other, thus will make the first insert into the Event Store.

Now, the aggregateId - sequenceNumber pair is a key constraint, thus two events for the same aggregate (id) with the same sequence number would give this exception you’re experiencing.

So it is very likely something with the routing of your commands.

Have you set a specific RoutingStrategy for you CommandRouter by any chance?

Cheers,
Steven

Dylan_Viljoen · October 10, 2018, 7:30am

Hi Steven,

We’re using the default spring-boot auto-configured CommandRouter & RoutingStrategy. In our case SpringCloudHttpBackupCommandRouter & AnnotationRoutingStrategy I also revisited the particular use-case & reviewed the the command handling. Nothing out of the ordinary - client sends Cmd over CommandGateway -> Aggregate handles Cmd & raises new event.

Any other suggestions?

Thanks,
Dylan

allardbz · October 10, 2018, 2:43pm

Hi Dylan,

I have a hunch of what might be happening, here. Unfortunately, the Spring team has decided (see https://github.com/spring-projects/spring-boot/issues/7107) to leave open session in view enabled by default, even in Spring Boot 2, although they do report a warning in the logs. This means the transaction scope is broaded to outside of Axon’s Unit of Work. As a result, locks may be released before the transaction is committed and changes are not available to the new thread.
Since Spring Cloud Command Router uses HTTP, and the Open Session In View Interceptor get hold of this request, this exception is likely to occur when multiple requests arrive simultaneously for the same aggregate.

You can disable open session in view by putting this in application.properties:
spring.jpa.open-in-view=false

Let me know if this seems to fix the problem.

Cheers,

Allard

Dylan_Viljoen · October 11, 2018, 6:59am

Hi Allard,

Unfortunately in our case we’re not using JPA but jdbc eventstore.

Dylan

allardbz · October 26, 2018, 8:55am

Hi Dylan,

that does change things a bit. By default the JdbcEventStorageEngine uses the JdbcSQLErrorCodesResolver to resolve errors. This looks for a SQLIntegrityConstraintViolationException in the exception chain. Looking at the exception that you’re getting, it looks like you’re receiving a BatchUpdateException and a PSQLException.
You can configure a different PersistenceExceptionResolver, for example the SQLErrorCodesResolver, which uses error codes specific to the DB being used to detect duplicate key exceptions.

Hope this helps.
Cheers,

Allard