Sharing Connections

When multiple connections acquired by an application use the same JDBC
resource, the connection pool provides connection sharing within the
same transaction scope. For example, suppose Bean A starts a transaction
and obtains a connection, then calls a method in Bean B. If Bean B
acquires a connection to the same JDBC resource with the same sign-on
information, and if Bean A completes the transaction, the connection
can be shared.

Connections obtained through a resource are shared only if the
resource reference declared by the J2EE component allows it to be
shareable. This is specified in a component’s deployment descriptor
by setting the res-sharing-scope element to Shareable for the particular resource reference. To turn off connection
sharing, set res-sharing-scope to Unshareable.

Obtaining a Physical Connection from a Wrapped
Connection

The DataSource implementation in the Application Server provides
a getConnection method that retrieves the JDBC
driver’s SQLConnection from the Application Server’s Connection wrapper. The method signature
is as follows:

Using Non-Transactional Connections

The DataSource implementation in the Application Server provides
a getNonTxConnection method, which retrieves a JDBC connection that is not in the scope of any transaction.
There are two variants, as follows:

Another way to get a non-transactional connection is to create
a resource with the JNDI name ending in __nontx.
This forces all connections looked up using this resource to be non
transactional.

Typically, a connection is enlisted in the context of the transaction
in which a getConnection call is invoked. However,
a non-transactional connection is not enlisted in a transaction context
even if a transaction is in progress.

The main advantage of using non-transactional connections is
that the overhead incurred in enlisting and delisting connections
in transaction contexts is avoided. However, use such connections
carefully. For example, if a non-transactional connection is used
to query the database while a transaction is in progress that modifies
the database, the query retrieves the unmodified data in the database.
This is because the in-progress transaction hasn’t committed.
For another example, if a non-transactional connection modifies the
database and a transaction that is running simultaneously rolls back,
the changes made by the non-transactional connection are not rolled
back.

Here is a typical use case for a non-transactional connection:
a component that is updating a database in a transaction context spanning
over several iterations of a loop can refresh cached data by using
a non-transactional connection to read data before the transaction
commits.

Not all database vendors support all transaction isolation levels available in the JDBC API.
The Application Server permits specifying any isolation level your database
supports. The following table defines transaction isolation levels.

To verify that a level is supported by your database management
system, test your database programmatically using the supportsTransactionIsolationLevel() method
in java.sql.DatabaseMetaData, as shown in the following
example: