WPS: Multiple database lookup primitives in a mediation flow = XA

In IBM WebSphere Process Server, mediation flow is one of the two most used high-level building blocks (Process is another). And one of standard building blocks of a mediation flow is a Database Lookup primitive. It enriches your message by referencing a user-supplied database table.

One interesting fact is that if your mediation flow navigates through more then one database lookup against different databases, you must use XA data sources. The reason for this is that mediation flow runs in a single implicit transaction. When Database Lookup primitive executes, it first attempts to obtain a connection from configured data source. Transaction Manager attempts to enlist the data source into transaction. If this implicit transaction already has a resource (data source) enlisted, a different resource may only be enlisted if it supports XA. If it does not, your Database Lookup will fail right on this spot, unable to obtain a connection from data source. It is a little puzzling because you do not attempt to drive a transaction against the data source – you just want to perform a read-only lookup, but that’s how the primitive is coded.

Just to be clear, I’m talking this specific scenario: your mediation flow actually navigates through 2 database lookups that use different data sources – then you must use XA. If you have multiple Database Lookups in different branches of the flow that are not reached in the same invocation – no problem.