Connection Monitoring with JPA and Hibernate

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Hypersistence Optimizer is that tool!

Introduction

In this article, I’m going to show you how you can set up connection monitoring for your JPA and Hibernate application, no matter if you are using Spring or other application frameworks.

JPA and Hibernate connection monitoring – detecting the auto-commit mode

By default, a JDBC Connection is running in auto-commit mode, and to explicitly set the transaction boundaries, you need to disable the auto-commit mode via the setAutoCommit method and call commit or rollback at the end of the transaction.

If you fail to disable the auto-commit mode explicitly at the connection pool level, then Hibernate will do it before starting a transaction and revert the change at the end of the transaction. The extra operations incur an overhead that you should avoid.

So, let’s assume you’re calling a service method, like this one:

Page<Store> storePage = storeService.findAll(
    PageRequest.of(0, 25, Sort.by("id"))
);

With the default Spring settings, you would not know that the auto-commit mode has to be disabled and re-enabled by Hibernate for every executing transaction.

However, since version 2.3, Hypersistence Optimizer can detect these issues.

So, when executing the following integration test:

hypersistenceOptimizer.getEvents().clear();

Page<Store> storePage = storeService.findAll(
    PageRequest.of(pageNumber, pageSize, Sort.by("id"))
);

assertTrue(hypersistenceOptimizer.getEvents().isEmpty());

We can see that the test fails as follows:

SELECT 
    s.id AS id1_4_, 
    s.name AS name2_4_ 
FROM 
    stores s 
ORDER BY s.id ASC 
LIMIT ?

-- Hypersistence Optimizer : MINOR - AutoCommittingConnectionEvent 
-- The JDBC Connection was borrowed in auto-commit mode, 
-- and Hibernate needs to disable this mode when starting a transaction 
-- and re-enable it prior to releasing the JDBC Connection back to the pool.

The fix is very simple. Just add the following two lines to your Spring Boot application.properties file:

spring.datasource.hikari.auto-commit=false
spring.jpa.properties.hibernate.connection.provider_disables_autocommit=true

The spring.datasource.hikari.auto-commit setting tells Hikari to call setAutoCommit(false) on the JDBC Connection upon adding a new connection in the pool.

The hibernate.connection.provider_disables_autocommit configuration property tells Hibernate that the connection provider disables the auto-commit mode prior to passing the database connection to the application.

JPA and Hibernate connection monitoring – detecting statementless connections

Another very hard to track issue is when a database connection is fetched, but no SQL statement is executed.

For instance, lets’ assume we created the following parkingOccupancy service method:

@Override
@Transactional(readOnly = true)
public Occupancy parkingOccupancy(Integer storeId) {
    float occupancyRate = parkingService.parkingOccupancyRate(storeId);

    Occupancy[] occupancies = Occupancy.values();
    float maxAllowedRate = 0;
    Occupancy occupancy = null;
    
    for (int i = 0; i < occupancies.length; i++) {
        occupancy = occupancies[i];
        maxAllowedRate = (i + 1f) / occupancies.length;
        if(occupancyRate < maxAllowedRate) {
            return occupancy;
        }
    }
    return Occupancy.FULL;
}

The parkingService.parkingOccupancyRate() is a Web Service call, so we don’t really need to access the database system to fetch the required data.

However, if we execute the following integration test:

assertTrue(hypersistenceOptimizer.getEvents().isEmpty());

Occupancy occupancy = storeService.parkingOccupancy(storeId);

assertTrue(hypersistenceOptimizer.getEvents().isEmpty());

We get the following issues reported by Hibernate Optimizer:

-- Hypersistence Optimizer : MINOR - AutoCommittingConnectionEvent 
-- The JDBC Connection was borrowed in auto-commit mode, 
-- and Hibernate needs to disable this mode when starting a transaction 
-- and re-enable it prior to releasing the JDBC Connection back to the pool.

-- Hypersistence Optimizer : MAJOR 
-- StatementlessConnectionEvent 
-- The JDBC Connection was borrowed for [15] ms, but no SQL statement was executed. 

The problem is also generated by the default auto-commit mode and can be visualized in the following diagram:

Auto-coomit connection monitoring JPA Hibernate

In the absence of the hibernate.connection.provider_disables_autocommit setting, Hibernate doesn’t know whether the auto-commit mode has been disabled, so it needs to check it out.

However, to do so, it needs to acquire a database connection at the beginning of the @Transactional method, hence the connection is acquired for a given time without needing to execute any SQL statement.

The fix is very simple. Just remove the @Transactional annotation from all the methods that don’t end up accessing the database system.

JPA and Hibernate connection monitoring – detecting transactionless sessions

JPA and Hibernate allow you to execute queries even if you didn’t start a transaction since transactions are needed by JPA only for writing data.

So, the following example creates an EntityManager and executes two queries:

hypersistenceOptimizer.getEvents().clear();

try(Session entityManager = entityManagerFactory
        .createEntityManager().unwrap(Session.class)) {
    Post post = entityManager.createQuery("""
        select p 
        from Post p 
        where p.id = :id
        """, Post.class)
    .setParameter("id", 1L)
    .getSingleResult();

    int postCount = ((Number) entityManager.createQuery("""
        select count(p) 
        from Post p
        """)
    .getSingleResult()).intValue();
}

assertTrue(hypersistenceOptimizer.getEvents().isEmpty());

However, when running the above integration test case, we get the following failure:

SELECT 
    p.id AS id1_0_, 
    p.title AS title2_0_ 
FROM 
    post p 
WHERE 
    p.id = ?
    
SELECT 
    COUNT(p.id) AS col_0_0_ 
FROM 
    post p

-- Hypersistence Optimizer - CRITICAL 
-- TransactionlessSessionEvent 
-- The JPA EntityManager or Hibernate Session has acquired [2] database connections 
-- because the Persistence Context is not transactional. 
-- You should switch to using a transactional Persistence Context, 
-- which allows you to execute all SQL statements using a single database connection.

The fix is very simple. Just start a transaction for the JPA EntityManager, and all SQL statements are going to be enlisted in the context of the same database transaction.

All the issues we’ve found have been reported by Hypersistence Optimizer, which works with Spring Boot, Spring, Java EE, Jakarta EE, Quarkus, Play, JBoss, Glassfish, or other application servers.

To enable Hypersistence Optimizer, you need to add the io.hypersistence:hypersistence-optimizer:${hypersistence-optimizer.version} Maven dependency and configure the HypersistenceOptimizer bean:

@Configuration
public class HypersistenceConfiguration {
    @Bean
    public HypersistenceOptimizer hypersistenceOptimizer(
            EntityManagerFactory entityManagerFactory) {
        return new HypersistenceOptimizer(
            new JpaConfig(entityManagerFactory)
        );
    }
}

That’s it!

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Connection and transaction monitoring is very important when implementing a data access layer, and JPA and Hibernate are no different. Thanks to the Hypersistence Optimizer tool, you can catch mapping, configuration, query, session, and connection issues right from your integration tests, therefore preventing them from affecting the production system.

Transactions and Concurrency Control eBook

4 Comments on “Connection Monitoring with JPA and Hibernate

  1. Thanks, as always.

    On the lines saying:

    … the following two lines to your application.properties file:

    spring.datasource.hikari.auto-commit=false
    spring.jpa.properties.hibernate.connection.provider_disables_autocommit=true

    Should the second one have that “spring.jpa.properties.” prefix? You don’t mention when you refer to the setting thrn next two times in the article.

    • No. The first one is for Hikari while the second one is for Hibernate.

      • Ah, ok. I don’t use either regularly (obviously). 🙂 I only asked because you subsequently referred to the second line (twice) as just hibernate.connection.provider_disables_autocommit. I was just making sure the first reference wasn’t mistaken.

        I realize now you must have just been leaving off the spring.jpa.properties. prefix on the next two references to be succinct, and fair enough. Thanks.

      • The hibernate.connection.provider_disables_autocommit is the name of the Hibernate property, and that’s how you set it with any framework, but Spring Boot.

        However, Spring Boot is the most popular one, hence you need to use the spring.jpa.properties.hibernate.connection.provider_disables_autocommit instead. I added more info to make it clear.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.