2

Context

This basically relates directly to this other question.

Essentially, our PUT endpoint needs to be more performant. Our approach is now to DELETE all relevant entries and then INSERT the new ones. This endpoint is only called once every few minutes, so there is no actual potential concurrent requests, but it does need to run fast.

I've already done other performance-related improvements like using JPQL for bulk queries, activating EclipseLink's batch-writing, and properly indexing the database.

At this point, the only other improvement I can think of is to run in parallel the calls which aren't related. Here is a screenshot of an execution of our endpoint as seen through Dynatrace PurePath:

Dynatrace PurePath

Analysis

There are 9 calls to the DB which could be turned into 2 sets of calls:

  1. All of the SELECT and DELETE calls could be started simultaneously and shouldn't step on each others' toes since they relate to different tables.
  2. All of the INSERT calls could be started simultaneously and shouldn't step on each others' toes since they relate to different tables.

The 3x Update calls simply don't exist anymore so we can forget about them. Thus, it seems like this endpoint could potentially be twice as fast if this works as intended.

Difficulty

Although to be confirmed, it is my understanding that:

  1. EntityManager is not thread-safe
  2. A JDBC connection cannot be shared by multiple threads
  3. Thus each thread needs to operate on a different connection
  4. Thus, to allow rollback, those threads should wait until all requests are confirmed to be successful before committing after being done with their individual work
  5. Additionally, @Async methods which return void are harder to control in terms of error handling

Question

How do we properly set up a Spring-MVC endpoint to issue database calls in different threads, and then use the returned CompletableFutures to get proper flow control by ensuring the queries ran successfully (i.e. completed without Exceptions).

The execution needs to be able to:

  1. Await the first set of CompletableFutures completion before proceeding with the next set of requests
  2. Allow rollback in case of error from any of the requests

Current state and problem

I'm trying to achieve this goal by using Spring's @Async, but I'm having trouble understanding how I should do this correctly.

The way I've done it currently throws a SQLTransientConnectionException after the 30s timeout configured for the HikariPool (which contains 8 threads):

javax.persistence.PersistenceException: java.lang.reflect.UndeclaredThrowableException
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:493)
    at jdk.internal.reflect.GeneratedMethodAccessor236.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:406)
    at com.sun.proxy.$Proxy280.getResultList(Unknown Source)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:126)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:131)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
    at com.sun.proxy.$Proxy187.getAllSitesWithConcatenatedIds(Unknown Source)
...
Caused by: java.lang.reflect.UndeclaredThrowableException: null
    at com.sun.proxy.$Proxy127.getConnection(Unknown Source)
    at org.eclipse.persistence.sessions.JNDIConnector.connect(JNDIConnector.java:138)
    at org.eclipse.persistence.sessions.DatasourceLogin.connectToDatasource(DatasourceLogin.java:172)
    at org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.connectInternal(DatasourceAccessor.java:348)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.connectInternal(DatabaseAccessor.java:316)
    at org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.reconnect(DatasourceAccessor.java:583)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.reconnect(DatabaseAccessor.java:1665)
    at org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.incrementCallCount(DatasourceAccessor.java:323)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:622)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:567)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2099)
    at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:603)
    at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:265)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:275)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:261)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:332)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:744)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2759)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2712)
    at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:584)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1232)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:911)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1191)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:485)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1279)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2983)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1898)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1880)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1845)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:262)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:482)
    ... 127 common frames omitted
Caused by: java.lang.reflect.InvocationTargetException: null
    at jdk.internal.reflect.GeneratedMethodAccessor233.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at com.foo.datasource.SchemaBasedDataSourceInvocationHandler.invoke(SchemaBasedDataSourceInvocationHandler.java:27)
    ... 158 common frames omitted
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30006ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:695)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
    at com.foo.datasource.DataSourceSelector.getConnection(DataSourceSelector.java:64)
    ... 162 common frames omitted

It's also worth noting that Hikari also complains:

11:50:21.0800 - DEBUG   TenantId[]  UserId[]    TraceId[]   Thread[HikariPool-1 housekeeper]
                Logger[com.zaxxer.hikari.pool.HikariPool]
                Msg[HikariPool-1 - Pool stats (total=8, active=8, idle=0, waiting=1)]
11:50:21.0800 - DEBUG   TenantId[]  UserId[]    TraceId[]   Thread[HikariPool-1 housekeeper]
                Logger[com.zaxxer.hikari.pool.HikariPool]
                Msg[HikariPool-1 - Fill pool skipped, pool is at sufficient level.]
11:50:35.0582 - DEBUG   TenantId[-1]    UserId[]    TraceId[]   Thread[pool-6-thread-1]
                Logger[com.zaxxer.hikari.pool.HikariPool]
                Msg[HikariPool-1 - Timeout failure stats (total=8, active=8, idle=0, waiting=0)]

It seems pretty obvious that the spawned threads are having trouble managing the connections. They either aren't getting any, or are not releasing them back to the pool properly. (Although in any case, even if I fix this, I'm not entirely certain the whole "rollback everything if an error occurs" is achievable?)

Here is an attempt at abstracting the current code's state (CF are CompletableFuture, --> are calls to other classes, <-- are the returned type, ### is a blocking operation):

@RestController

  --> @Service "Service 1"
        --> @Transactional @Service "Service for First Select"
              --> @Repository @Async "Table A"
              <-- CF
        <-- CF
  <-- CF#thenApply

  --> @Service "Service 1"
        --> @Transactional @Service "Service for Second Select"
              --> @Repository @Async "Table B"
              <-- CF
        <-- CF
  <-- CF#thenApply

  --> @Transactional @Service "Service 2"
        --> @Repository @Async "Delete from Table X"
        <-- void
        --> @Repository @Async "Delete from Table Y"
        <-- void
        --> @Repository @Async "Delete from Table Z"
        <-- void
        ### Need to wait for ALL (CF and void) of the previous @Async calls to be done
        --> @Repository @Async "Insert into Table X"
        <-- void
        --> @Repository @Async "Insert into Table Y"
        <-- void
        --> @Repository @Async "Insert into Table Z"
        <-- void
  <-- void

Other relevant threads

I've read through these to get some insight:

payne
  • 4,691
  • 8
  • 37
  • 85
  • Wouldn't it be simpler to use a stored procedure in the database that combines all the queries? With it, you would only need one network roundtrip. Your database may even be able to optimize/parallize the execution of the stored procedure. See for example the following link on how to call stored procedures from spring: https://www.baeldung.com/spring-data-jpa-stored-procedures – rmunge Dec 14 '21 at 20:52

0 Answers0