Spring Microservice Application Resilience: The Role of @Transactional in Preventing Connection Leaks

In any microservice, managing database interactions with precision is crucial for maintaining application performance and reliability. Usually, we will unravel weird issues with database connection during performance testing. Recently, a critical issue surfaced within the repository layer of a Spring microservice application, where improper exception handling led to unexpected failures and service disruptions during performance testing. This article delves into the specifics of the issue and also highlights the pivotal role of the @Transactional annotation, which remedied the issue.

Spring microservice applications rely heavily on stable and efficient database interactions, often managed through the Java Persistence API (JPA). Properly managing database connections, particularly preventing connection leaks, is critical to ensuring these interactions do not negatively impact application performance.

Issue Background

During a recent round of performance testing, a critical issue emerged within one of our essential microservices, which was designated for sending client communications. This service began to experience repeated Gateway time-out errors. The underlying problem was rooted in our database operations at the repository layer.

An investigation into these time-out errors revealed that a stored procedure was consistently failing. The failure was triggered by an invalid parameter passed to the procedure, which raised a business exception from the stored procedure. The repository layer did not handle this exception efficiently; it bubbled up. Below is the source code for the stored procedure call:  

public long createInboxMessage(String notifCode, String acctId, String userId, String s3KeyName,
                               List<Notif> notifList, String attributes, String notifTitle,
                               String notifSubject, String notifPreviewText, String contentType, boolean doNotDelete, boolean isLetter,
                               String groupId) throws EDeliveryException {
    try {
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("p_create_notification");
        DbUtility.setParameter(query, "v_notif_code", notifCode);
        DbUtility.setParameter(query, "v_user_uuid", userId);
        DbUtility.setNullParameter(query, "v_user_id", Integer.class);
        DbUtility.setParameter(query, "v_acct_id", acctId);
        DbUtility.setParameter(query, "v_message_url", s3KeyName);
        DbUtility.setParameter(query, "v_ecomm_attributes", attributes);
        DbUtility.setParameter(query, "v_notif_title", notifTitle);
        DbUtility.setParameter(query, "v_notif_subject", notifSubject);
        DbUtility.setParameter(query, "v_notif_preview_text", notifPreviewText);
        DbUtility.setParameter(query, "v_content_type", contentType);
        DbUtility.setParameter(query, "v_do_not_delete", doNotDelete);
        DbUtility.setParameter(query, "v_hard_copy_comm", isLetter);
        DbUtility.setParameter(query, "v_group_id", groupId);
        DbUtility.setOutParameter(query, "v_notif_id", BigInteger.class);

        query.execute();
        BigInteger notifId = (BigInteger) query.getOutputParameterValue("v_notif_id");
        return notifId.longValue();
    } catch (PersistenceException ex) {
        logger.error("DbRepository::createInboxMessage - Error creating notification", ex);
        throw new EDeliveryException(ex.getMessage(), ex);
    }
}

Issue Analysis

As illustrated in our scenario, when a stored procedure encountered an error, the resulting exception would propagate upward from the repository layer to the service layer and finally to the controller. This propagation was problematic, causing our API to respond with non-200 HTTP status codes—typically 500 or 400. Following several such incidents, the service container reached a point where it could no longer handle incoming requests, ultimately resulting in a 502 Gateway Timeout error. This critical state was reflected in our monitoring systems, with Kibana logs indicating the issue: 

 `HikariPool-1 - Connection is not available, request timed out after 30000ms.`

The issue was improper exception handling, as exceptions bubbled up through the system layers without being properly managed. This prevented the release of database connections back into the connection pool, leading to the depletion of available connections. Consequently, after exhausting all connections, the container was unable to process new requests, resulting in the error reported in the Kibana logs and a non-200 HTTP error.

Resolution

To resolve this issue, we could handle the exception gracefully and not bubble up further, letting JPA and Spring context release the connection to the pool. Another alternative is to use @Transactional annotation for the method. Below is the same method with annotation:

@Transactional
public long createInboxMessage(String notifCode, String acctId, String userId, String s3KeyName,
                               List<Notif> notifList, String attributes, String notifTitle,
                               String notifSubject, String notifPreviewText, String contentType, boolean doNotDelete, boolean isLetter,
                               String groupId) throws EDeliveryException {
	………
}

The implementation of the method below demonstrates an approach to exception handling that prevents exceptions from propagating further up the stack by catching and logging them within the method itself:

public long createInboxMessage(String notifCode, String acctId, String userId, String s3KeyName,
                               List<Notif> notifList, String attributes, String notifTitle,
                               String notifSubject, String notifPreviewText, String contentType, boolean doNotDelete, boolean isLetter,
                               String loanGroupId) {
    try {
        .......
        query.execute();
    	BigInteger notifId = (BigInteger) query.getOutputParameterValue("v_notif_id");
    	return notifId.longValue();      
    } catch (PersistenceException ex) {
        logger.error("DbRepository::createInboxMessage - Error creating notification", ex);
    }
    return -1;
}

With @Transactional

The @Transactional annotation in Spring frameworks manages transaction boundaries. It begins a transaction when the annotated method starts and commits or rolls it back when the method completes. When an exception occurs, @Transactional ensures that the transaction is rolled back, which helps appropriately release database connections back to the connection pool.

Without @Transactional

If a repository method that calls a stored procedure is not annotated with @Transactional, Spring does not manage the transaction boundaries for that method. The transaction handling must be manually implemented if the stored procedure throws an exception. If not properly managed, this can result in the database connection not being closed and not being returned to the pool, leading to a connection leak.

Best Practices

• Always use @Transactional when the method's operations should be executed within a transaction scope. This is especially important for operations involving stored procedures that can modify the database state.
• Ensure exception handling within the method includes proper transaction rollback and closing of any database connections, mainly when not using @Transactional.

Conclusion

Effective transaction management is pivotal in maintaining the health and performance of Spring Microservice applications using JPA. By employing the @Transactional annotation, we can safeguard against connection leaks and ensure that database interactions do not degrade application performance or stability. Adhering to these guidelines can enhance the reliability and efficiency of our Spring Microservices, providing stable and responsive services to the consuming applications or end users.