CODEWITHSUNDEEP
javajbosswildflyjava-ee-8
ps0604
ps0604

Reputation: 1071

Datasource pool connection not closed in Wildfly 20

I have a Wildfly Java application running with a MariaDB database. Initially the connection works fine, but after 20 connections (the default) the next time it tries to connect the server hangs and after around one minute it throws the following exception:

javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasources/IndustryDS

This is how I connect and close the datasource:

    private InitialContext context = null;
    private DataSource ds = null;
    private Connection conn = null;

    try {
        context = new InitialContext();
        ds = (DataSource)context.lookup(pool);
        conn = ds.getConnection();           // <--- here is where it hangs 
        
        // use the connection


        if (conn != null)
            conn.close();

        if (context != null)
            context.close();

    } 
    catch (NamingException e) {
        logger.error(e.getMessage());
        throw new DAOException(e.getMessage());
    } 
    catch (SQLException e) {
        logger.error(e.getMessage());
        throw new DAOException(e.getMessage());  // <--- this error is thrown
    }

The datasource configuration in standalone.xml

            <datasource jta="true" jndi-name="java:jboss/datasources/IndustryDS" 
                        pool-name="IndustryDS" enabled="true" use-java-context="true">
                <connection-url>jdbc:mariadb://localhost:3306/industry</connection-url>
                <driver>mariadb</driver>
                <security>
                    <user-name>user</user-name>
                    <password>xxxxxx/password>
                </security>
            </datasource>

By default, MariaDB supports 150 connections so the database shouldn't be the problem. The default maximum pool size in Wildfly is 20 and I'm the only user in the system. Every time I initiate a function in my application I request two connections and then disconnect.

Why the datasource connections are not available even when I close them?

Upvotes: 1

Views: 3325

Answers (3)

user1712798
user1712798

Reputation: 48

Here's what worked for me.

  1. Enable cached connection manager in debug mode

    <cached-connection-manager debug="true" error="true"/>

  2. Look for this text in your log file - "Closing a connection for you. Please close them yourself". This will help help you find the leak in your code.

  3. In my case, jdbcTemplate.getConnection().createClob() was causing the pool to exhaust.

    try {
         Connection conn = jdbcTemplate.getConnection()
         ....
         conn.createClob();
         ...
    } catch() {
      ...
    } finally {
       conn.close()
    }

So properly closing the connection as shown above worked for us.

Hope this saves a lot of time for someone.

Upvotes: 2

stdunbar
stdunbar

Reputation: 17435

I've done this a very different way using JPA and never had an issue. My code looks something like:

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;


public class MyClass {
    @PersistenceContext
    private EntityManager entityManager;

    public SomeObject getSomeObject() {
        // as an example query
        Query query = entityManager.createQuery("select ...")
    }
}

There is some additional configuration needed in META-INF/persistence.xml that looks like:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
             http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
             version="2.1">
    <persistence-unit name="somethingPU" transaction-type="JTA">
        <jta-data-source>jdbc/yourJNDIName</jta-data-source>
    </persistence-unit>
</persistence>

In this way you're not ever dealing with connection management - the container (Wildfly in your case) takes care of it for you.

Upvotes: 1

Stephen C
Stephen C

Reputation: 718708

One problem with your code is that the context and connection may not be closed if there is an exception.

The old way to solve this was to close the resources in in a finally block. The modern way is to use try with resources. For example:

try (InitialContext context = new InitialContext();
     Connection conn = ((DataSource) context.lookup(pool)).getConnection()) {
    // use the connection
} catch (NamingException e) {
    logger.error(e.getMessage());
    throw new DAOException(e.getMessage());
} catch (SQLException e) {
    logger.error(e.getMessage());
    throw new DAOException(e.getMessage()); 
}

The try with resources starts with resource declarations where the resources are declared and initialized. Then there is a body where the resources are used. Finally you have (optional) catch and finally blocks.

The secret sauce is that the try with resources construct will automatically close each of the (non-null) resources, in reverse order that they were opened. Exceptions thrown by the close calls will be dealt with appropriately. And so on.

(You can achieve (more or less) the same thing in the old way with finally blocks, but it is complicated.)

Upvotes: 1

Related Questions