Querying multiple databases using one-phase capable JDBC resources

1-2 Phase Commit (PC)

Within a one phase commit a transaction manager (responsible for managing the transactional operations of transactional components) sends out the commit message after a unit of work is executed.

In a two phase commit the transaction manager first checks with each local resource manager regarding whether they are ready to commit the transaction (sends a prepare for commit message) and based on the response from each resource manager, the transaction manager instructs all resource managers to commit the transaction if all agree, or to roll back the transaction if at least one disagrees. 

Using more than one 1PC resources in the same transaction 

Assuming the following scenario:

Figure 1 : Querying more than one 1PC resources example

Where:

Figure 2 : Querying more than one 1PC resources Beans detail

Both figures above illustrate the following:

• WebSphere is responsible for managing the transactional operations of transactional components (components involved in a transaction).
• All Session Beans do not have a TransactionManagementType annotation declared at the class level which indicates that all  have CMT transaction (default for session beans)
• All Session Beans do not have a TransactionAttributeType annotation declared at the class level which indicates that all have the REQUIRED value for all methods.This means:
• Methods must always execute in a transaction context.
• If there is a transaction already running, the bean participates in that transaction. 
• If there is no transaction, the EJB container starts a transaction on behalf of the bean.
• The method in FacadeBean runs in a propagated transaction if present, otherwise in a new global transaction. This means:
• The changes that are made by the services invoked to the transactional resources (from BeanA and BeanB) are persisted when the global transaction commits.
• The JDBC driver implementation class used for DS1 is com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource (i.e One-Phase Transaction Support - 1PC).
• The JDBC driver implementation class used for DS2 is com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource (i.e One-Phase Transaction Support - 1PC).
• All resources get enlisted by WAS on the current global transaction so that is can be managed.

The result of the above configuration is the following exception:

These errors occur because there are more than one one-phase capable (1PC) resources within a global transaction.

Two alternative configurations are illustrated below in order to resolve the above problem.

Last Participant Support

With Last Participant Support (LPS), it is allowed to have one one-phase capable resource with any number of two-phase capable resources in the same global transaction.

Figure 3 : One-phase resource participates in a two-phase transaction with one two-phase resources

Also using the administrative console:

Figure 4 : Enable last participant support extension

Resource manager local transaction (RMLT)

A resource manager local transaction (RMLT) is a resource manager's view of a local transaction; that is, it represents a unit of recovery on a single connection that is managed by the resource manager. A local transaction containment is used to define the application server behavior in an unspecified transaction context. One example is the coordination of multiple one-phase resource managers that do not support XA transaction coordination. A session bean starts starts an ActivitySession and perform its RMLTs within the scope of that ActivitySession The ActivitySession service provides an alternative unit-of-work scope to the scope that is provided by global transaction contexts. 

The above figure illustrates the following:

• The TransactionAttributeType annotation at the methodB in BeanB, overwrites the class level declaration and indicates that this specific method has NOT_SUPPORTED transaction value. This means:
• Method cannot be involved in a transaction.
• Method will always execute outside the transaction context. 
• If there is a transaction running, it is suspended and will be resumed after the end of those methods.
• In the deployment descriptor of the BeanB, in the Local Transactions section, the Resolver attribute is set to Application and the Unresolved action attribute is set to Rollback. This means:
• The component processing occurs within a WebSphere local transaction containment  that is managed by the application.
• The Resolver option define how the local transaction is to be resolved before the local transaction context ends. The application option indicates that is the responsibility of the application to commit or roll back transactions. 
• The unresolver-action options are commit or rollback and specify the action the container will take if the resources are uncommitted by an application in a local transaction.
• BeanB starts an ActivitySession and perform its RMLTs within the scope of that ActivitySession.
• The code is responsible for forcing commitment by using the javax.resource.cci.LocalTransaction.begin() and javax.resource.cci.LocalTransaction.commit() i.e. code delineates the transaction (another option is to have setAutocommit(true) - each statement will run in its own transaction).
• In case the code does javax.resource.cci.LocalTransaction.begin() but does not call the javax.resource.cci.LocalTransaction.commit()  or sets setAutocommit(false) after obtaining a JDBC connection and not add any code to control the transaction, then the state of the transaction at the end of the method would be ambiguous. To resolve this problem the  unresolver-action option is set. 

References

[1] IBM Support Portal - “WTRN0062E and J2CA0030E Errors While Trying To Do Last Participant Support Extensions"  Date of access: July 2013. http://www-01.ibm.com/support/docview.wss?uid=swg21244805

[2] IBM Redbooks - Ueli Wahli, Giuseppe Bottura, Jacek Laskowski, Nidhi Singh - “WebSphere Application Server Version 6.1 Feature Pack for EJB 3.0." September 2008. Date of access: April 2013. http://www.redbooks.ibm.com/abstracts/sg247611.html

[3] Oracle Documentation - "The Java EE 6 Tutorial" . January 2013. Date of access: June 2013. http://docs.oracle.com/javaee/6/tutorial/doc/bncih.html

[4] IBM Information Center - “JDBC provider summary"  Date of access: July 2013. http://pic.dhe.ibm.com/infocenter/wasinfo/v7r0/index.jsp?topic=/com.ibm.websphere.nd.multiplatform.doc/info/ae/ae/udat_minreq.html

[5] IBM Information Center - “Local transaction containment"  Date of access: June 2013. http://pic.dhe.ibm.com/infocenter/wasinfo/v8r0/index.jsp?topic=%2Fcom.ibm.websphere.express.doc%2Finfo%2Fexp%2Fae%2Frjta_useltran.html