Spring/Hibernate connection leak with ScrollableResults

Question

I am currently investigating the following issue, which also affects our production environment https://github.com/querydsl/querydsl/issues/3089. A author of this library suspected that Hibernate might be the culprit of the issue, and i was able to reproduce this with a minimal example. Before opening an issue in the Hibernate issue tracker i wanted to make sure that i am not doing anything horribly wrong or it actually is an issue with Spring.

I am using the following versions:

• Spring-Boot 3.0.5 (older versions are also affected)
• Hibernate 6.1.7 FINAL (older versions are also affected)

Calling the following controller is causing the connection leak, which Hikari also picks up and logs in the output.

@Controller
public class IndexController {
    @PersistenceContext
    private EntityManager entityManager;

    @GetMapping("/test")
    public ResponseEntity<Map<Long, String>> fetchEntities() {
        Query query = entityManager.createQuery("select testEntity.id, testEntity.name from TestEntity testEntity");
        org.hibernate.query.Query<?> unwrappedQuery = query.unwrap(org.hibernate.query.Query.class);
        try (ScrollableResults<?> results = unwrappedQuery.scroll()) {
            while (results.next()) {}
        }
        return ResponseEntity.noContent().build();
    }
}

The connection also leaks, if i call entityManager.close and/or results.close()

With this entity:

@Entity
@Table(name = "test")
public class TestEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String name;

    public TestEntity() {
    }

    public TestEntity(String name) {
        this.name = name;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

}

This is my database config:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackageClasses = { DatabaseConfig.class })
@EntityScan(basePackageClasses = { DatabaseConfig.class })
public class DatabaseConfig {
}

My application config:

management:
  server:
    base-path: /actuator
    port: 8081
  endpoints.web.exposure.include: '*'

spring:
  datasource:
    url: jdbc:h2:mem:testdb
    driver-class-name: org.h2.Driver
    username: 
    password:
    hikari:
      leak-detection-threshold: 2000
  jpa:
    open-in-view: false
    hibernate:
      ddl-auto: create
    properties:
      hibernate:
        dialect: org.hibernate.dialect.H2Dialect

logging:
  level:
    com.zaxxer.hikari.pool.HikariPool: debug

Answer 1

Answering my own question. As M. Deinum mentioned in the comments you should always have clear boundaries for your transactions, even if they are read-only. Hibernate also mentions this in their documentation:

Database, or system, transaction boundaries are always necessary. No communication with the database can occur outside of a database transaction (this seems to confuse many developers who are used to the auto-commit mode). Always use clear transaction boundaries, even for read-only operations.

https://docs.jboss.org/hibernate/core/3.3/reference/en/html/transactions.html#transactions-demarcation

I still wanted to understand why a simple getResultList on the Query does not leak a connection, while scroll/next does, although both only request one connection and return a valid result.

It turns out that this is an interaction between the shared EntityManager that Spring provides and specific methods on the Query interface. The EntityManager you receive from Spring is actually a shared proxy of type SharedEntityManagerInvocationHandler which proxies the methods called on it. Specifically methods that return a Query interface (i.e. createQuery) return another proxy for the actual Query object.

That proxy has a list of methods which it considers to be terminating, called queryTerminatingMethods. The list contains:

• "execute", // jakarta.persistence.StoredProcedureQuery.execute()
• "executeUpdate", // jakarta.persistence.Query.executeUpdate()
• "getSingleResult", // jakarta.persistence.Query.getSingleResult()
• "getResultStream", // jakarta.persistence.Query.getResultStream()
• "getResultList", // jakarta.persistence.Query.getResultList()
• "list", // org.hibernate.query.Query.list()
• "stream", // org.hibernate.query.Query.stream()
• "uniqueResult", // org.hibernate.query.Query.uniqueResult()
• "uniqueResultOptional" // org.hibernate.query.Query.uniqueResultOptional()

For all those methods the proxy will automatically close the shared entity manager after invoking them, which also in turn releases the connection. scroll is missing from the list, which makes sense. And this is the reason why it leaks a connection here. You can also not just call close on the proxied EntityManager Spring provides, because that actually does nothing.

You can also confirm this behaviour by creating your own EntityManager with the Spring provided EntityManagerFactory before executing the query (which in most cases you wouldn't want/need to do). If you don't call close on this EntityManager, both getResultList and scroll will leak a connection.