Interplay between datasource configuration and a database's max connections

NB: this is likely application-server agnostic stuff.

There are different failure modes involved based on the relation between:

It may seem confusing at first but eventually it makes perfect sense.

Let's assume a scenario of having a number of threads more than the datasource's max-pool-size asking for a connection at the exact same time. Or equivalently asking for a connection within a couple of seconds and each holding long running transactions.

This, in itself will not result in failure. Effectively each datasource implements a FIFO queue and so if a thread asks for a connection and it so happens that currenty all the connections of the pool are busy, the call to getConnection on the DataSource object will simply block. I.e. no exception or error will be thrown at that point in time. The thread asking for the connection will simply have to wait a little longer before obtaining a connection and doing its thing.

If, however, none of the other connections return to the pool anytime soon (e.g. because they are running long transactions), then eventually, an exception will be thrown, when the blocking time exceeds the blocking-timeout-millis parameter (see this note).

The above is all nice and well and very reasonable. Nobody could expect anything different

Note that under no circumstances should the max-pool-size parameter exceed the number of max connections configured in the database. This makes no sense and can result in immediate failure if more than max connections thread request a connection from the datasource simultaneously. I stress that the failure will be immediate without having to wait for the configured value of blocking-timeout-millis. This, too, makes perfect sense. The reasoning is that the datasource detects that something is wrongly configured as it cannot obtain more connections from the database and yet it hasn't reached its cap (the max-pool-size limit).

To explore the different failure modes I wrote some testing code. The following definitions apply:

max pool size configured for the datasource
max open connections in the database
number of threads
It is assumed that each thread executes a very lengthy transaction.

Scenario 1

P < D and T > P

Outcome: I get IJ000453 caused by IJ000655.

Unable to get managed connection for java:jboss/datasources/proposal
No managed connections available within configured blocking timeout (30000 [ms])

This happens after some time (a bit more than the configured blocking timeout).

Notice how the wording of IJ000655 beautifully concurs with the intuitive mental model of how datasources / pools ought to work.

Scenario 2.

P > D and T > D

this is really a misconfiguration as explained above because you should nevel really have P > D

Outcome: I get IJ000453
... caused by IJ000658: Unexpected throwable while trying to create a connection: null
... caused by javax.resource.ResourceException: Could not create connection
... caused by java.sql.SQLException: JZ00L: Login failed. Examine the SQLWarnings chained to this exception for the reason(s).

This happens almost immediately as explained above.