Pooling

Strategy

JBossJCA uses a

ManagedConnectionPool

to perform the pooling. The

ManagedConnectionPool

is made up of subpools depending upon the strategy chosen and other pooling parameters.

ByNothing

OnePool

ByApplication

PoolByCRI

ByContainer

PoolBySubject

ByContainerAndApplicaton

PoolBySubjectAndCri

NOTE: The xml names imply this is just about security. This is misleading.

FAQ (read the spec): For <security-domain-and-application/> the Subject always overrides any user/password from createConnection(user, password) in the CRI (

ConnectionRequestInfo

)

Transaction stickness

You can force the same connection from a (sub-)pool to get reused throughout a transaction

with the <track-connection-by-tx/> flag

NOTE: this is the only supported behaviour for "local" transactions

NOTE2: this element is deprecated in JBoss-5.x where transaction stickiness is enabled by default. XA users can explicitly enable interleaving with <interleaving/> element.

Workaround for Oracle

Oracle does not like XA connections getting used both inside and outside a JTA transaction. To workaround the problem you can create separate sub-pools for the different contexts using <no-tx-separate-pools/>

Pool Access

The pool is designed for concurrent usage.

Upto <max-pool-size/> threads can be inside the pool at the same time (or using connections from a pool).

Once this limit is reached, threads wait for the <blocking-timeout-seconds/> to use the pool

before throwing a "No Managed Connections Available"

Pool Filling

The number of connections in the pool is controlled by the pool sizes.

• <min-pool-size/> - When the number of connections falls below this size, new connections are created

• <max-pool-size/> - No more than this number of connections are created

• <prefill/> - Feature Request has been implemented for 4.0.5. Note: the only pooling strategy that supports this feature is OnePool, or ByNothing pooling criteria.

The pool filling is done by a separate "Pool Filler" thread rather than blocking application threads.

Idle Connections

You can configure connections to be closed when they are idle. e.g. If you just had a peak period

and now want to reap the unused ones. This is done via the <idle-timeout-minutes/>.

Idle checking is done on a separate "Idle Remover" thread on an LRU (least recently used) basis.

The check is done every idle-timeout-minutes divided by 2 for connections unused for idle-timeout-minutes.

The pool itself operates on an MRU (most recently used) basis. This allows the excess connections to be easily identified.

Should closing idle connections cause the pool to fall below the min-pool-size, new/fresh connections

are created.

NOTE: If you have long running transactions and you use interleaving (i.e. don't track-connection-by-tx)

make sure the idle timeout is greater than the transaction timeout. When interleaving the connection is returned to the pool for others to use. If however nobody does use it, it would be a candidate for removal before the transaction is committed.

Dead connections

The JDBC protocol does not provide a natural

connectionErrorOccured()

event when a connection is broken. To support dead/broken connection checking there are a number of plugins.

Valid connection checking

The simplest format is to just run a "quick" sql statement

<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>

before handing the connection to the application. If this fails, another connection is selected until there are no more connections at which point new connections are constructed.

The potentially more performant check is to use vendor specific features, e.g. Oracle's or MySQL's  pingDatabase() via the

<valid-connection-checker-class-name></valid-connection-checker-class-name>

Errors during SQL queries

You can check if a connection broke during a query by the looking the error codes or messages

of the SQLException for FATAL errors rather than normal SQLExceptions. These codes/messages can

be vendor specific, e.g.

<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>

For

FATAL

errors the connection will be closed.

Changing/Closing/Flushing the pool

• change or flush() the pool

• closing/undeploying the pool will do a flush first

Other pooling

• Thirdparty Pools - only if you know what you are doing