WebSphere distributed transactions with SQL Server Linked Server

SQL Server Linked Server

Linked server enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Linked servers offer the following advantages:

• The ability to access data from outside of SQL Server.
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
• The ability to address diverse data sources similarly.

Invoking a linked server stored procedure from a WebSphere environment using Global Transaction and REPEATABLE_READ

Assuming the following scenario:

Figure 1 : Global Transaction Example with WebSphere as the transaction manager

The above figure illustrates the following:

• WebSphere is responsible for managing the transactional operations of transactional components (components involved in a transaction).
• The TransactionManagementType annotation at the class level indicates that BeanA has CMT transaction.
• The TransactionAttributeType annotation at the class level indicates that BeanA has the SUPPORTS value for all methods. This means:
• The bean participates in a running transaction but does not require it.
• If there is no transaction, the method executes without a transaction.
• The TransactionAttributeType annotation at the methodA in BeanA, overwrites the class level declaration and indicates that this specific method has REQUIRED transaction value. 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 methodA of BeanA runs in a propagated transaction if present, otherwise in a new global transaction.
• The changes that are made by the service invoked to the transactional resources are persisted when transaction of BeanA commits.
• If one of the operations within each transaction fails, then all of them are rolled-back so that the application is returned to its prior state.
• The JDBC driver implementation class used is the com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource (i.e One-Phase Transaction Support - 1PC).
• The default isolation level in WebSphere is TRANSACTION_REPEATABLE_READ for all databases that support the RepeatableRead isolation level. This means:
• repeated reads of the database result with the same data values.
• used when we have to update the database records often.
• this prevents data from being modified by other concurrent transactions.
• The SQL query is a distributed query executed through a linked server. This means:
• SQL Server parses the command and sends requests to OLE DB

The result of the above configuration is the following exception:

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

Invoking a linked server stored procedure from a WebSphere environment using Local transaction containment

Figure 2 : Local transaction containment

The above figure illustrates the following:

• The TransactionAttributeType annotation at the methodA in BeanA, 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 BeanA, 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.
• 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. 

Invoking a linked server stored procedure from a WebSphere environment using TRANSACTION_READ_COMMITTED

Figure 3 : READ COMMITTED Isolation level

The above figure illustrates the following:

• The isolation level in WebSphere is TRANSACTION_READ_COMMITTED. This means:
• transactions will read committed data only
• data read is always consistent.
• used for report-generating programs that use the current state of the database at the time of report generation.

SCA Java Component Configuration

The following configuration should be used if a Java SCA component invokes the SQL Server Linked Server (instead of the Session Bean described in the above examples).

Figure 4 : Java SCA Component Configuration

References

[1] The Microsoft Developer Network - “Linked Servers (Database Engine)"  Date of access: June 2013. http://msdn.microsoft.com/en-us/library/ms188279.aspx

[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] IBM Support Portal - “Changing the default isolation level for non-CMP applications and describing how to do so using a new custom property webSphereDefaultIsolationLevel"  Date of access: June 2013. http://www-304.ibm.com/support/docview.wss?uid=swg21224492

[4] 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

[5] IBM Information Center - "EJB 3.0 application bindings overview" Date of access: June 2013. http://pic.dhe.ibm.com/infocenter/wasinfo/v7r0/index.jsp?topic=%2Fcom.ibm.websphere.express.doc%2Finfo%2Fexp%2Fae%2Fcejb_bindingsejbfp.html