Connection pool exhausted in Spring Boot with JdbcTemplate

Question

I have an issue with connection pool being exhausted when querying a database using Spring Boot and JdbcNamedTemplate.

This is how it is supposed to work:

1. Get a request from outside, with some parameters about how the house is supposed to be built.
2. Based on parameters recieved, use some REST endpoints and two DAOs to gather data.
3. Use data to create House object and return it.
4. Rcieve more requests...

How it works as of now:

1. Request is good.
2. Data from REST endpoints - OK. Data with DAO -> OK only for first 50 requests with two DAOs, after that NOT OK. When one of the DAOs is disabled, no connections are blocked.
3. After 50 houses are built OK, rest will take forever to finish and have no windows in the end.
4. Makes it unusable for more requests as they will simply timeout.

I get this exception when I call endpoint more than 50 times (max pool size):

    com.atomikos.jdbc.AtomikosSQLException: Connection pool exhausted - try increasing 'maxPoolSize' and/or 'borrowConnectionTimeout' on the DataSourceBean.

And it will stay like this until I restart the app. It seems like there is something off about my DAO or configuration, but I haven't been able to figure out what, despite searching all day. If anyone can help, I will be thankfull.

Extra info: No other exceptions are thrown that I am aware of. All the other data is retrieved correcly. Send help.

UPDATE: I did some more experiments: This app uses another dao that I didnt mention before because I forgot. It works almost the same, only it connects to a different database, so it has a separate configuration. It also takes advantage of JdbcNamedTemplate and @Qualifier is used to select the correct one.

Now, what I discovered is that disbling one or the other DAO will not eat the connections anymore. So the question is: What can't they coexist in peace?

This is the dao.

    @Component
    public class WindowsDao {

        private static final String PARAM_1 = "param";

        private final String SELECT_ALL = ""
                + " SELECT "
                + "   STUFF "
                + " FROM TABLE " 
                + " WHERE "
                + "     THING =:" + PARAM_1
                + " WITH UR";

        @Autowired
        private NamedParameterJdbcTemplate myTemplate;

        public Optional<List<BottomDealerText>> getWindows(
                final WindowsCode windowCode {

            final MapSqlParameterSource queryParameters = new MapSqlParameterSource()
                    .addValue(PARAM_1, windowCode.getValue())

            final Optional<List<Window>> windows;
            try {
                windows = Optional.of(myTemplate.query(
                        SELECT_ALL,
                        queryParameters,
                        new WindowsRowMapper()));
            }
            catch (final EmptyResultDataAccessException e) {
                LOG.warn("No results were found.");
                return Optional.empty();
            }
            return windows;
        }
    }

DAO is called from this service:

    @Service
    @Transactional
    public class WindowsService {

        @Autowired
        private WindowsDao windowsDao;

        public Optional<List<Stuff>> getWindows(
                final WindowCode windowCode) {
            final Optional<List<Window>> windows = windowsDao.getWindows(
                    windowCode;
            return windows;
        }
    }

Which is called from this service:

    @Service
    @Transactional
    public class AssembleHouseService { 
        // some things
        @Autowired
        private WindowsService windowsService;

        public House buildHouse(final SomeParams params) {
            // This service will fetch parts of the house
            HouseBuilder builder = House.builder();
            // call other services and then...
            builder.windows(windowsService.getWindows(args).orElse(/*something*/));
            //and then some more things...
        }
    }

This is what I use to configure the datasource:

    myDb:
    driver: db2
    schema: STUFF
    unique-resource-name: STUFF
    database-name: STUFF1
    server-name: myServer
    port: 12312
    username: hello
    password: world
    driver-type: 4
    min-pool-size: 2
    max-pool-size: 50

RowMapper:

public class WindowsRowMapper implements RowMapper<Window> {

    @Override
    public Windows mapRow(final ResultSet rs, final int rowNum)
            throws SQLException {
        return new BottomDealerText(
                re.getString("WSIZE"),
                rs.getString("DESCRIPTION"),
                rs.getString("COLOR"));
    }
}

Answer 1

Just posting here for those who look for a workaround:

If you cant change to different version of atomikos (or just ditch it), what worked for me was adding

Propagation.REQUIRES_NEW

to services that used those different data sources, so it would be:

@Service
@Transactional(propagation = Propagation.REQUIRES_NEW)

It seems like putting this two read operations into separate transactions makes atomikos close the transaction and release connection properly.

Answer 2

If you have two readonly DAOs in the same transaction then you may have hit a known bug in the Atomikos open source edition that manifests itself only in this particular scenario.

It's been fixed in the commercial edition but not (yet) in the open source.

Hope that helps