Payara Enterprise Support Success Story: JDBC Connection Pool Behaviour

As part of the Payara Enterprise Support services that we deliver to customers on a daily basis, giving expert advice and clarifying how the internals of the products of the Payara Platform work is one of the most common scenarios we encounter. Here's' a story about the advice we gave to one of our customers regarding the behavior of JDBC Connection Pools in Payara Server.

How many database connections from a pool are occupied during one EJB transaction?

A customer who is migrating his applications to Payara Server 5.x wanted a direct reply to this question posted on StackOverflow:

I would like to know how many database connections from my database pool are occupied during the call of the following example code.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.annotation.Resource;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.ejb.TransactionAttributeType;
import javax.sql.DataSource;

@Stateless
@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public class SomeDbBean {

    @Resource(name = "jdbc/mydatabase")
    private DataSource database;

    public void updateSomeValues() throws SQLException, InterruptedException {

        // Connection 1, normally this is done in another method
        try (Connection conn = database.getConnection();
                PreparedStatement ps = conn.prepareStatement("update some where id = 1")) {
            ps.executeUpdate();
        }

        // Sleep to mimic some long processing logic
        Thread.sleep(30000);

        // Connection 2, normally this is done in another method
        try (Connection conn = database.getConnection();
                PreparedStatement ps = conn.prepareStatement("update some where id = 2")) {
            ps.executeUpdate();
        }
    }
}

The database commits happen once the method finishes, but what would be done with the two database connections I received from my connection pool? The try with resources call close on the connections. Will the connections remain occupied until the transaction is completed? Or are they freed to the database connection pool and JEE handles the commit in an invisible way?

I cannot really find the answer when looking at:

• https://javaee.github.io/javaee-spec/Specifications
• https://github.com/javaee/jta-spec

  When I have a connection pool with 50 connections can I call this method 50 times without a problem, or can I call this method 25 times before the pool runs out of connections?

  NOTE: I do not configure the JEE application server to use 'Pinned-To-Thread' or 'associate-with-thread' application server specific database connection pool settings.

   

• https://github.com/javaee/jta-spec/blob/master/JTA1.3Specification.pdf

I come as close as: how an application server may handle a connection request from the application

8. The application server invokes the getConnection method.
9. The application server returns the Connection object reference to the application.
10. The application performs one or more operations on the connection.
11. The application closes the connection.
12. The application server delists the resource when notified by the resource adapter about the connection close.
13. The transaction manager invokes XAResource.end to disassociate the transaction from the XAResource.
14. The application server asks the transaction manager to commit the transaction.
15. The transaction manager invokes XAResource.prepare to inform the resource manager to prepare the transaction work for commit.
16. The transaction manager invokes XAResource.commit to commit the transaction.

Which seems to suggest Connection1 is given back to the resource pool to be reused before the EJB transaction is committed.

Payara Enterprise Support Answer

The customer's question is targeted to the general Java EE body of specifications. The problem is that none of its specifications and APIs define the behavior that application servers should apply in the case of connection pool resources. The answer that was given to the customer was in the context of how connection pools work in Payara Server.

Before answering this question we have to make the distinction between logical connections and physical connections:

• Physical connections are the ones provided directly by the database vendor and can be obtained normally by using either the DriverManager or DataSource classes to connect directly to the database using explicit connection parameters (username, password, server name, URL, etc). Physical connection objects are destroyed when garbage collected after they are explicitly closed. When developing Java EE applications, physical connections are discouraged.
• Logical connections are provided by connection pools and their life-cycle is managed by them. When an application requests a connection (like in the code sample above when calling DataSource.getConnection), the pool simply hands an available connection (or will create a new one if needed). When an application is done with the connection (by explicitly closing it or the underlying transaction is completed), the connection is simply returned to the pool. It will be destroyed (and garbage collected) only then the pool manager decides it so.

In the code sample, two connections are acquired from the pool and when the method is finished and the transaction is successfully committed (or a rollback occurs), the connections will be released and returned to the pool.

However, under a non-transactional context (for example, when using TransactionAttributeType.NOT_SUPPORTED or TransactionAttributeType.NEVER) connections will be released either when they are explicitly closed by the application or when the current business method finishes (on a Stateless EJB bean this happens when the invoker finishes its execution on the caller's side). I tested this intensively by running load tests again using this code sample and on multiple times, connections were released immediately after the invoker call finished. Only on rare cases, connections were released when explicitly closing them.

Will the connections remain occupied until the transaction is completed? Or are they freed to the database connection pool and JEE handles the commit in an invisible way?

YES, connections will remain acquired when they have been requested by any application component and they will be released when explicitly closed or the underlying transaction completes (committed successfully, rollback, or times out). The connection pool will never handle the completion of any underlying JTA transaction, but it will commit the database transaction as instructed by the transaction manager when the transaction is signaled for commit.

When I have a connection pool with 50 connections can I call this method 50 times without a problem, or can I call this method 25 times before the pool runs out of connections?

Connection pools behave by restricting the number of connections given to applications. This behaviour is controlled by the following pool parameters/properties:

• Initial/Minimum Size: The number of logical connections that should be always available at the connection pool from the start. Defaults to 8.
• Maximum Pool Size: The maximum number of logical connections that can be created by the pool. If the pool reaches this number, it won't create any more connections, but it won't necessarily deny requests if all connections have been acquired already. Defaults to 32.
• Idle Timeout: The maximum number of seconds that a connection can remain idle in the pool. Idle connections are connections that have been created and are free in the pool or connections that have been previously released to the pool too. Defaults to 300s.
• Pool Resize Quantity: The maximum number of connection pools that should be removed after their idle timeout expires. This is done by the pool manager to free resources. The manager will destroy connections even if the number of available connections gets under the minimum value. Defaults to 2
• Max Wait Time: The maximum number of milliseconds that a caller (application component) waits for a new connection request. If the pool has reached its maximum and there are no available connections to be given back, the caller will simply wait until a new one is available (or in the case of transactional context, until the transaction times out). If no more connections are available in the pool and this timeout expires, an exception will be thrown out by the pool to the caller. Defaults to 60000ms=60s.

Taking all of this into account, the answer is: It depends on the parameters of the pool. Using the default parameters, launching 50 concurrent calls to the same method will work without problems:

1. Every time a method is called a new connection is requested to the pool. Initially, there are 8 connections in the pool, so it will max out to 32 after the 32nd call to the method. New connections will be created on the 9th call onwards.
2. Since no more connections can be given, the 33rd to 50th calls will wait for new connections to be given for at least 60s.
3. After the first 32 calls finish the SQL update being issued, 32 connections will be released to the pool again and they will be immediately acquired by the remaining 18 method calls. The remaining 32 first calls then will start the waiting period for 30s.
4. After the 33th to 50th method calls finish their first SQL updates, 18 connections will be released to the pool, and these method calls will enter their sleep period.
5. After the first to 32th method calls finish their sleep period, 32 connections will be acquired by them and their SQL updates will be issued. Assuming an immediate response to the database, these connections will be released immediately.
6. The same will happen to the remaining 18 method calls.
7. For the next 5 minutes, connections will be gradually destroyed by the pool by a factor of 2 until at least one connection remains on the pool. The pool will start creating new connections to reach a minimum of 8 connections.

So for the span of this scenario (10 minutes), 100 connections were acquired, 100 connections released, 40 created and 32 destroyed. The following Grafana graphs show these metrics in a timeline:

NOTE: I do not configure the JEE application server to use 'Pinned-To-Thread' or 'associate-with-thread' application server specific database connection pool settings.

The Associate With Thread option does not affect the behaviour of this scenario since the sample code will release the connections to the pool by explicitly closing them.

I come as close as how an application server may handle a connection request from the application

1. The application server invokes the getConnection method.
2. The application server returns the Connection object reference to the application.
3. The application performs one or more operations on the connection.
4. The application closes the connection.
5. The application server delists the resource when notified by the resource adapter about the connection close.
6. The transaction manager invokes XAResource.end to disassociate the transaction from the XAResource.
7. The application server asks the transaction manager to commit the transaction.
8. The transaction manager invokes XAResource.prepare to inform the resource manager to prepare the transaction work for commit.
9. The transaction manager invokes XAResource.commit to commit the transaction.

This process is "mostly" accurate. However, JDBC resources (connections) are not necessarily delisted when the connection is closed. They can either remain enlisted in the same transaction when the connection is reused in the span of the original JTA transaction.

So in summary, JDBC connection pools will behave accordingly based on the parameters used to configure its size and idle behavior. We encourage users of Payara Server that want to better understand how connection pools  work to experiment with them after reading the concepts that I referenced.

Need support for Payara Platform in production? Learn more about Payara Enterprise: