title: Spring Database Load Test: How to release connections
body: <p>I was just trying to simplify my Problem in a little Show-Case-Application.<br>
  It seems like the Connection doesn't get closed until the Thread finished. <a href="https://github.com/bastiankemmer/Spring-Hibernate-Problem" rel="noreferrer"><strong>Check this out</strong></a>  </p>
  
  <p><strong>Dependencies Information:</strong>  </p>
  
  <p><strong>Servlet Engine</strong>: Apache Tomcat/8.5.5<br>
  <strong>JPA Provider:</strong> Hibernate</p>
  
  <p>My pom.xml:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;project xmlns="http://maven.apache.org/POM/4.0.0"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"&gt;
      &lt;modelVersion&gt;4.0.0&lt;/modelVersion&gt;
  
      &lt;groupId&gt;...&lt;/groupId&gt;
      &lt;artifactId&gt;***-service&lt;/artifactId&gt;
      &lt;version&gt;0.0.1&lt;/version&gt;
      &lt;packaging&gt;jar&lt;/packaging&gt;
  
      &lt;name&gt;...&lt;/name&gt;
      &lt;description&gt;...&lt;/description&gt;
  
      &lt;parent&gt;
          &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
          &lt;artifactId&gt;spring-boot-starter&lt;/artifactId&gt;
          &lt;version&gt;1.4.1.RELEASE&lt;/version&gt;
      &lt;/parent&gt;
  
      &lt;properties&gt;
          &lt;project.build.sourceEncoding&gt;UTF-8&lt;/project.build.sourceEncoding&gt;
          &lt;project.reporting.outputEncoding&gt;UTF-8&lt;/project.reporting.outputEncoding&gt;
          &lt;java.version&gt;1.8&lt;/java.version&gt;
          &lt;spring.cloud.release.version&gt;Brixton.SR2&lt;/spring.cloud.release.version&gt;
      &lt;/properties&gt;
  
      &lt;dependencies&gt;
  
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-thymeleaf&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-actuator&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-data-jpa&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-data-rest&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-web&lt;/artifactId&gt;
          &lt;/dependency&gt;
  
          &lt;!-- eureka discovery client --&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.cloud&lt;/groupId&gt;
              &lt;artifactId&gt;spring-cloud-starter-eureka&lt;/artifactId&gt;
          &lt;/dependency&gt;
  
          &lt;dependency&gt;
              &lt;groupId&gt;mysql&lt;/groupId&gt;
              &lt;artifactId&gt;mysql-connector-java&lt;/artifactId&gt;
              &lt;scope&gt;runtime&lt;/scope&gt;
          &lt;/dependency&gt;
  
          &lt;!-- for unit tests --&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
              &lt;artifactId&gt;spring-boot-starter-test&lt;/artifactId&gt;
              &lt;scope&gt;test&lt;/scope&gt;
          &lt;/dependency&gt;
  
          &lt;!-- for rest documentation --&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;org.springframework.restdocs&lt;/groupId&gt;
              &lt;artifactId&gt;spring-restdocs-mockmvc&lt;/artifactId&gt;
              &lt;scope&gt;test&lt;/scope&gt;
          &lt;/dependency&gt;
  
          &lt;!-- for (un)marshalling of json / xml --&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;com.fasterxml.jackson.core&lt;/groupId&gt;
              &lt;artifactId&gt;jackson-databind&lt;/artifactId&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;com.fasterxml.jackson.dataformat&lt;/groupId&gt;
              &lt;artifactId&gt;jackson-dataformat-xml&lt;/artifactId&gt;
          &lt;/dependency&gt;
  
          &lt;dependency&gt;
              &lt;!-- jsoup HTML parser library @ http://jsoup.org/ --&gt;
              &lt;groupId&gt;org.jsoup&lt;/groupId&gt;
              &lt;artifactId&gt;jsoup&lt;/artifactId&gt;
              &lt;version&gt;1.9.2&lt;/version&gt;
          &lt;/dependency&gt;
  
      &lt;/dependencies&gt;
  
      &lt;dependencyManagement&gt;
          &lt;dependencies&gt;
              &lt;!-- also needed for eureka discovery --&gt;
              &lt;dependency&gt;
                  &lt;groupId&gt;org.springframework.cloud&lt;/groupId&gt;
                  &lt;artifactId&gt;spring-cloud-dependencies&lt;/artifactId&gt;
                  &lt;version&gt;${spring.cloud.release.version}&lt;/version&gt;
                  &lt;type&gt;pom&lt;/type&gt;
                  &lt;scope&gt;import&lt;/scope&gt;
              &lt;/dependency&gt;
          &lt;/dependencies&gt;
      &lt;/dependencyManagement&gt;
  
      &lt;build&gt;
          &lt;finalName&gt;${artifactId}&lt;/finalName&gt;
          &lt;plugins&gt;
              &lt;plugin&gt;
                  &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
                  &lt;artifactId&gt;spring-boot-maven-plugin&lt;/artifactId&gt;
                  &lt;configuration&gt;
                      &lt;executable&gt;true&lt;/executable&gt;
                      &lt;outputDirectory&gt;${dir}&lt;/outputDirectory&gt;
                  &lt;/configuration&gt;
              &lt;/plugin&gt;
          &lt;/plugins&gt;
      &lt;/build&gt;
  &lt;/project&gt;
  </code></pre>
  
  <p>I have a Spring Boot Server with MYSQL as DB. When I am Load-Testing (JMeter) with more than 100 Threads/Users, I get this Exception:  </p>
  
  <blockquote>
    <p>org.apache.tomcat.jdbc.pool.PoolExhaustedException: [http-nio-8015-exec-195] Timeout: Pool empty. Unable to fetch a connection in 60 seconds, none available[size:50; busy:50; idle:0; lastwait:60000].  </p>
  </blockquote>
  
  <p>My Datasource Configuration:</p>
  
  <pre><code>spring.datasource.tomcat.minIdle = 0
  spring.datasource.tomcat.maxIdle = 10
  spring.datasource.tomcat.maxActive = 50
  spring.datasource.tomcat.maxWait = 60000
  spring.datasource.tomcat.testOnBorrow = true
  spring.datasource.tomcat.timeBetweenEvictionRunsMillis = 1800000 // Updated
  spring.datasource.tomcat.numTestsPerEvictionRun = 50  // Updated
  spring.datasource.tomcat.minEvictableIdleTimeMillis = 10
  spring.datasource.tomcat.validationQuery = SELECT 1
  spring.datasource.tomcat.testWhileIdle = true
  </code></pre>
  
  <p>The only query on the Database is a simple <code>SELECT * FROM TABLE WHERE deletedat IS NULL</code>. It returns about 4000 Entries in about 0.01 seconds (via mysql CLI).<br>
  I have installed Neor Profile SQL to Profile my MYSQL DB. When I check all processes, the processes on my Schema are all Sleeping. It seems like my Configuration doesn't release those Connections.  </p>
  
  <p>I am using a simple Interface which extends from CrudRepository:  </p>
  
  <pre><code>public interface MyRepository extends CrudRepository&lt;MyModel, String&gt; {
      @Query("SELECT m from MyModel m WHERE m.deletedAt IS NULL")
      List&lt;MyModel&gt; findWhereDeletedAtIsNull();
  }
  </code></pre>
  
  <p>and use it </p>
  
  <pre><code>List&lt;MyModel&gt; myModelList = myRepository.findWhereDeletedAtIsNull();
  </code></pre>
  
  <p>What am I missing?<br>
  It seems that either the Configuration or the Code needs to be adapted in order to release connections.   </p>
  
  <p><strong>Hibernate Config:</strong></p>
  
  <pre><code>spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect  
  </code></pre>