I checked asynchronous execution of queries in Spring Boot 1.5.9

Overview

I learned that it is possible to execute repository queries asynchronously using Spring's asynchronous processing, so I investigated how to implement it. This article summarizes a simple implementation of asynchronous queries and the results of their operation.

environment

reference

Database side preparation

Create a view that takes a long time to search to make it easier to check the operation.

CREATE OR REPLACE VIEW async_test_view (
      id
    , sleep
    , create_at ) AS
SELECT MD5(UUID()) AS id
    , SLEEP(10) AS sleep
    , NOW() AS create_at
;

Searching for this view will take about 10 seconds to return results.

> select * from pseudo_delay_view;
+----------------------------------+-------+---------------------+
| id                               | sleep | create_at           |
+----------------------------------+-------+---------------------+
| da863db6ff1b064ebff03f00efdd224b |     0 | 2017-12-23 17:27:08 |
+----------------------------------+-------+---------------------+
1 row in set (10.00 sec)

Implementation on the Spring Boot side

Enable asynchronous processing and set thread pool

  1. Add the EnableAsync annotation to enable asynchronous processing.
  2. Although it is not required to enable asynchronous processing, this example implements it to use a thread pool.
  3. Specify the task queue size. An idle thread processes the queued tasks. The default for ThreadPoolTaskExecutor is Integer.MAX_VALUE.
  4. Specify the pool size with CorePoolSize. MaxPoolSize is the maximum number.
  5. Specify the lifetime of idle threads above CorePoolSize. The default for ThreadPoolTaskExecutor is 60 seconds.
@SpringBootApplication
// 1
@EnableAsync
public class DemoGradleApplication {
  public static void main(String[] args) {
    SpringApplication.run(DemoGradleApplication.class, args);
  }

  // 2
  @Bean
  public TaskExecutor taskExecutor() {
    ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();

    // 3
    executor.setQueueCapacity(2);
    // 4
    executor.setCorePoolSize(2);
    executor.setMaxPoolSize(3);
    // 5
    executor.setKeepAliveSeconds(10);

    executor.afterPropertiesSet();

    return executor;
  }

}

When the pool size limit is reached

If the number of requests exceeds MaxPoolSize + QueueCapacity, an exception called RejectedExecutionException will be thrown by default.

If you want to execute arbitrary processing for reject processing when the request reaches the upper limit, pass the class that implements the RejectedExecutionHandler interface to ThreadPoolTaskExecutor as shown below. If not specified, the default is ThreadPoolExecutor # AbortPolicy. (Throws a RejectedExecutionException exception)

executor.setRejectedExecutionHandler((r,e) -> {
  //Implement any process you want to execute
  throw new RejectedExecutionException("Task:" + r.toString() + " rejected from " + e.toString());
});

entity

An implementation of the entity that corresponds to the view. There are no special notes.

@Entity
@Table(name="pseudo_delay_view")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PseudoDelay implements Serializable {

    private static final long serialVersionUID = -9169553858944816379L;

    @Id
    private String id;
    @Column(name="sleep", nullable=false)
    private Integer sleep;
    @Column(name="create_at", nullable=false)
    private LocalDateTime createAt;

}

Repository

An implementation of the repository that issues queries. Implement to execute the query asynchronously. All you have to do is add the Async annotation to the method.

  1. Annotate the method that executes the query asynchronously with Async annotation. Also, use CompletableFuture as the return type.
  2. This is a synchronous method for comparison.
public interface PseudoDelayRepository extends JpaRepository<PseudoDelay, String> {

    // 1
    @Async
    @Query("SELECT p FROM PseudoDelay AS p")
    CompletableFuture<PseudoDelay> findAsync();

    // 2
    @Query("SELECT p FROM PseudoDelay AS p")
    PseudoDelay findSync();

}

In this example, CompletableFuture is used, but in addition to this, Future and Spring ListenableFuture can be used. For details, see Spring Data JPA --Reference Documentation --3.4.7. Async query results In can be confirmed.

Check if the query is running asynchronously

The implementation of the service class for confirmation is as follows.

  1. This is a method for checking the operation of asynchronous queries.
  2. A method for checking synchronous queries for comparison.
@Service
@Slf4j
public class AsyncTestServiceImpl implements AsyncTestService {

  private PseudoDelayRepository repository;

  public AsyncTestServiceImpl(PseudoDelayRepository repository) {
    this.repository = repository;
  }

  // 1
  @Transactional(readOnly = true)
  @Override
  public PseudoDelay async() {
    log.debug("start async");

    CompletableFuture<PseudoDelay> future = repository.findAsync();

    //Do something while running the query asynchronously
    log.debug("execute somethings");

    PseudoDelay result = null;
    try {
      //Receive the query execution result
      result = future.thenApply(res -> {
        log.debug("async result : {}", res);
        return res;
      })
      .get();

      } catch (InterruptedException | ExecutionException e) {
        throw new RuntimeException(e);
      }
    }

    log.debug("end async");
    return result;
  }

  // 2
  @Transactional(readOnly = true)
  @Override
  public PseudoDelay sync() {
    log.debug("start sync");

    PseudoDelay result = repository.findSync();
    log.debug("sync result : {}", result);

    log.debug("end sync");
    return result;
  }

}

Asynchronous query operation check

You can see that other processing ("*** execute somethings ***") is being executed immediately after issuing the query.

2017-12-23 19:55:36.194 DEBUG 5304 --- [nio-9000-exec-4] : start async
2017-12-23 19:55:36.195 DEBUG 5304 --- [nio-9000-exec-4] : *** execute somethings ***
2017-12-23 19:55:46.198 DEBUG 5304 --- [ taskExecutor-2] : async result : PseudoDelay(id=9904388341a9d8dbdfb230fb5b675224, sleep=0, createAt=2017-12-23T19:55:36)
2017-12-23 19:55:46.199 DEBUG 5304 --- [nio-9000-exec-4] : end async

Checking the operation of synchronous queries

You can see that it is blocked until the result of the query is returned.

2017-12-23 19:57:49.465 DEBUG 5304 --- [nio-9000-exec-8] : start sync
2017-12-23 19:57:59.467 DEBUG 5304 --- [nio-9000-exec-8] : sync result : PseudoDelay(id=3a19a242c0207cd9ddad551ec2ccae66, sleep=0, createAt=2017-12-23T19:57:49)
2017-12-23 19:57:59.467 DEBUG 5304 --- [nio-9000-exec-8] : end sync

Specify a timeout

You can specify the timeout. If it does not complete within the specified time, a TimeoutException exception will be thrown.

result = future.get(5, TimeUnit.SECONDS);

About transaction timeout

We set a timeout for the transaction and looked at what would happen if a query that took longer than that timeout was executed asynchronously. This time I set the transaction timeout to 5 seconds and ran a query that took 10 seconds both asynchronously and synchronously as in the example above.

@Transactional(readOnly = true, timeout = 5)

** For asynchronous queries **

No exception was raised when the timeout was exceeded, and the expected result was not achieved. I do not know the cause, but it seems that transaction management is out of order if it is executed in another thread within the transaction. I checked if there is any description in the official document, but I have not checked it completely. For the time being, there was Spring @Async and transaction management in such an article.

** For synchronous queries **

I think it depends on the JDBC driver implementation, but in the case of MySQL, the following error will occur.

2017-12-23 20:17:18.297 ERROR 2260 --- [nio-9000-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Statement cancelled due to timeout or client request
2017-12-23 20:17:18.319 ERROR 2260 --- [nio-9000-exec-1] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [/app] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet] with root cause

com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2827) ~[mysql-connector-java-5.1.44.jar:5.1.44]

/...abridgement

Recommended Posts

I checked asynchronous execution of queries in Spring Boot 1.5.9
Asynchronous processing with regular execution in Spring Boot
Summary of what I learned about Spring Boot
Summary of what I learned in Spring Batch
05. I tried to stub the source of Spring Boot
I tried to reduce the capacity of Spring Boot
How to use CommandLineRunner in Spring Batch of Spring Boot
Specify spring.profiles.active via context-param of web.xml in Spring Boot
Set context-param in Spring Boot
Spring Boot 2 multi-project in Gradle
Major changes in Spring Boot 1.5
NoHttpResponseException in Spring Boot + WireMock
Accelerate testing of Validators that require DI in Spring Boot
Execution of initial processing using Spring Boot Command Line Runner
Spring Boot Hello World in Eclipse
Spring Boot application development in Eclipse
Memorandum of understanding when Spring Boot 1.5.10 → Spring Boot 2.0.0
Get a proxy instance of the component itself in Spring Boot
Write test code in Spring Boot
I participated in JJUG CCC 2019 Spring
Going out of message (Spring boot)
Implementation of asynchronous processing in Tomcat
What I did in the migration from Spring Boot 1.4 series to 2.0 series
Implement REST API in Spring Boot
What is @Autowired in Spring boot?
[Spring Boot] Role of each class
Implement Spring Boot application in Gradle
What I did in the migration from Spring Boot 1.5 series to 2.0 series
I tried GraphQL with Spring Boot
I want to control the default error message of Spring Boot
I tried Flyway with Spring Boot
I want to know the Method of the Controller where the Exception was thrown in the ExceptionHandler of Spring Boot
Thymeleaf usage notes in Spring Boot
Unknown error in line 1 of pom.xml when using Spring Boot in Eclipse
[Spring Boot] I investigated how to implement post-processing of the received request.
Procedure to make the value of the property file visible in Spring Boot
I got stuck using snake case for variable name in Spring Boot
My memorandum that I want to make ValidationMessages.properties UTF8 in Spring Boot
[Java] I participated in ABC-188 of Atcorder.
Launch (old) Spring Boot project in IntelliJ
Build Spring Boot + Docker image in Gradle
Static file access priority in Spring boot
Implementation of multi-tenant asynchronous processing in Tomcat
Output Spring Boot log in json format
Local file download memorandum in Spring Boot
Create Java Spring Boot project in IntelliJ
Loosen Thymeleaf syntax checking in Spring Boot
[Practice! ] Display Hello World in Spring Boot
WebMvcConfigurer Memorandum of Understanding for Spring Boot 2.0 (Spring 5)
Use DynamoDB query method in Spring Boot
I tried Lazy Initialization with Spring Boot 2.2.0
What I got into @Transactional in Spring
Asynchronous processing with Spring Boot using @Async
DI SessionScope Bean in Spring Boot 2 Filter
[* Java *] I participated in JJUG CCC 2019 Spring
Change session timeout time in Spring Boot
Organize the differences in behavior of @NotBlank, @NotEmpty, @NotNull with Spring Boot + Thymeleaf
Get the path defined in Controller class of Spring boot as a list
I tried to clone a web application full of bugs with Spring Boot
How to set environment variables in the properties file of Spring boot application
Fall 2017 Security Specialist I checked the frequency of words that appeared in the morning 2