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

JMX Programs

Using JMX interface to manage SIB queues

A service integration bus provides location transparent messaging, meaning that an application can connect to any bus member and send to (or receive from) a destination deployed to any bus member. The destination does not need to be deployed to the same bus member that the application connected to,

the bus routes the message to (or from) the destination. This section will describe how to use the Java application programming interfaces (APIs) in order to create a tool to navigate through the messaging components of a SIBus.

The code below will show how to view the current depth and message contents of all queues from all messaging engines. One can use this code via the AdminClient API (if the code will be used as a standalone) or via the MBeanServer API (if the code will be run inside a WebSphere JVM).

For a standalone application the first step is to create the AdminClient class in order to provide the client side APIs for the remote AdminService.  This is shown below:

If the code will run inside a WebSphere JVM (i.e. as an MBean) then the following code should be used:

It should be noted in this regard that the above code should be run inside the Messaging infrastructure JVM if a deployment environment is used (not a single server in a single cell).

The next step is to find all SIBMessagingEngines:

Then for each SIBMessagingEngine we are looking for all attached SIBQueuePoints:

For each SIBQueuePoint we are quering for the following attributes:

• depth
• state
• id
• highMessageThreshold
• sendAllowed

If the current depth of this SIBQueuePoint is greater than zero (could be greater than a specific threshold if you need to implement a simple monitor solution) then we get the queue messages:

For each message within a queue we print message attributes:

Then we print the queue message details:

Finally we print the message contents:

References

[1] IBM Information Center - “WebSphere Application Server Version 7.0 -Interface AdminClient"  Date of access: June 2013. http://pic.dhe.ibm.com/infocenter/wasinfo/v7r0/index.jsp?topic=%2Fcom.ibm.websphere.javadoc.doc%2Fweb%2Fapidocs%2Fcom%2Fibm%2Fwebsphere%2Fmanagement%2FAdminClient.html

A taste from JAVA EE Transactions

Container-managed transactions examples in EJB 3.0

This post series will introduce some useful examples of how transactions (JTA) can be used efficiently  within a Java EE container. The correct usage of JTA transaction will be explained and some common pitfalls to avoid will be demonstrated. This post assumes that the reader has an understanding of what a transaction is, as well as what kind of problems transaction management can solve. This series is organized as follows: At first, an introduction to JTA transactions will be given in order to show how they can be enabled and used. 

Transaction Boundary

This part provides examples of how the scope of a JTA transaction (transaction boundary) can be influenced by using EJB’s transactional behavior attributes. 

In general, most EJB’s have one of the following three effects on the current transaction:

• commit the current transaction, 
• commit the current transaction and start a new transaction
• no effect on the current transaction

Single Transaction

The figure below illustrates a service that runs in a single thread and in a single transaction.

Figure 1: Service participating in the same transaction

The above figure illustrates the following:

• The TransactionManagementType annotation at the class level indicates that both session beans have Container Managed Transaction (CMT).
• The TransactionAttributeType annotation at the class level indicates that both session beans have the SUPPORTS value for all methods.
• 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 methodA and methodB methods in BeanA and BeanB respectively, overwrites the class level declaration and indicates that these specific methods have REQUIRED transaction value. This means:

• Methods must always execute in a transaction context.
• If there is a transaction already running, then the bean will participate 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 service invoked through BeanA (methodB of BeanB) participates in the global transaction and does not run in its own transaction.
• The changes that are made by the service invoked to the transactional resources are persisted when global transaction commits.
• If one of the operations within a transaction fails, then all of them are rolled-back so that the application is returned to its prior state.
• The transaction lasts only for a certain amount of time (the value can be set globally or per EJB).
• No transaction boundary is introduced as the transactional attributes defined in the EJBs allow the propagation of the global transaction across components.

Chained Transaction

The figure below illustrates a service that spans across two transactions.

Figure 2: Service spans in two transactions

The above figure illustrates the following:

• The TransactionManagementType annotation at the class level indicates that both session beans have CMT transaction.
• The TransactionAttributeType annotation at the class level indicates that both session beans have 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 TransactionAttributeType annotation at the methodB in BeanB, overwrites the class level declaration and indicates that this specific method has REQUIRED_NEW transaction value.This means:
• A new transaction is always started when a method is called.
• If there is a transaction running, it is suspended.
• The container will start a new transaction and at the end of the method execution, it will commit or abort it.
• After that, the container will resume the client transaction.
• The methodA of BeanA runs in a propagated transaction if present, otherwise in a new global transaction.
• The service invoked through BeanA (methodB of BeanB) runs in its own new transaction and does not participate in the current BeanA transaction.
• Each transaction lasts only for a certain amount of time (the value can be set globally or per EJB).
• A transaction boundary is introduced as the transactional attributes defined in the EJBs does not allow the propagation of the global transaction across components.
• The changes that are made by the service invoked to the transactional resources are persisted when transaction of BeanB 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.
• Potential duplicate messages may be introduced due to transaction timeouts (e.g. TXa ends before TXb commits)

References

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

[2] Oracle Documentation - 6 authors - “The Java EE 5 Tutorial For Sun Java System Application Server 9.1.” June 2010. Date of access:May 2013. http://docs.oracle.com/javaee/5/tutorial/doc/bncij.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

Oracle ADF: Shooting your own leg examples - Part I

Hello world!

This is my very first post... I have waited a long time, before I took the decision to stay more time... awake and share with you my ideas and my experience in various technology areas.

I will start my journey in sharing knowledge with Oracle ADF. More specifically I plan to write a small series of articles on how to try "no to shoot your own leg" while developing with ADF.

I believe that Oracle ADF is a mature framework and so all the related blogs or books for it. Since I have not been involved with ADF early in its development,  I will try to avoid writing things that have already presented by others. I will try to focus on concepts and techniques, that I generally find that are not well understood by ADF developers. I will do this by either presenting some serious mistakes that I have seen in real production code, or I will create some fictitious errors or bad practices in order to present a better development approach.

How to bring and ADF application down... Learning from mistakes.

For my first post I have decided to spent some time to present and try to clarify the importance of some tuning parameters:

• "in Batches of" (FetchSize) and AccessMode tuning attributes of View Objects.
• FetchSize of af:table property
• Iterator Range Size. (@ Bindings - executables)

These are some of the tuning parameters that someone has to really take care if she needs to have a healthy and fast ADF application. Many people have tried to explain how to tune ADF applications, but I realize that in real projects developers cannot really apply these best practices. In my understanding one of the reasons that this is happening is because "tuning" and performance excellence in production deployment is not always a concern of the development team. Most of the times the concepts around tuning the artifacts that developers work are purely understood or even worse heavily misunderstood!

One the other hand and if developers are well informed about the effects that their choices in configuration parameters make, will make them at least more cautious and ready to face the real problems. Real problems come, when their application get out of the lab. So in this context, I believe that one of the many ways that we can learn is through studying the "bad examples". Bad examples can show us how can a production deployment of our code can really suffer from our choices made during development and poor designed testing. We can learn what to avoid and possibly provide an incentive to search for a better solution.

I often find that simple APIs or complex frameworks are not correctly used due to misconceptions. Examples and tutorials usually document the typical way of using an API, but usually miss showing what can go wrong in various choices that a developer can make. In my understanding this drives ones thinking in one direction. As a consequence the "obvious" pitfalls that an experience architect or developer can identify, are left out of the visibility of the average developer.

Coming back to the tuning parameters mentioned earlier, I will first describe how you can set a combination of the above attributes that can really bring a production environment on its knees! I will set the "bad example" and I hope to learn from it...

Then I will try to present how you could possibly diagnose such a situation on a Weblogic server running on JRockit with JRockit Mission Control.

And finally I will explain the "obvious" (or not "obvious" is for you to decide)  problematic set of values that lead to the performance degradation and how to resolve the problem setting the right values.

Stay tuned...