Using Non-Transactional Connections

You can specify a non-transactional database connection in any of these ways:

• Check the Non-Transactional Connections box on the JDBC Connection Pools page in the Admin Console. The default is unchecked. For more information, click the Help button in the Admin Console.

• Specify the --nontransactionalconnections option in the asadmin create-jdbc-connection-pool command. For more information, see the Sun Java System Application Server 9.1 Reference Manual.

• Use the DataSource implementation in the Application Server, which provides a getNonTxConnection method. This method retrieves a JDBC connection that is not in the scope of any transaction. There are two variants.

  public java.sql.Connection getNonTxConnection() throws java.sql.SQLException

  public java.sql.Connection getNonTxConnection(String user, String password) throws java.sql.SQLException

• 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.