Main Menu

Monday, November 8, 2010

A common pattern, or anti-pattern, seems to be using LogicalDeletePollingStrategy, where the DbAdapter updates the status from 'N' to 'Y', then the BPEL process itself further updates the status from 'Y' to 'P'.

There are pitfalls with this pattern if the end-end process is synchronous. By synchronous the DbAdapter polling does a synchronous post to BPEL, the complete BPEL process is executed synchronously, then control is passed back to the adapter, which does post processing updates and commits the global transaction that it started.

First of all the DbAdapter raises to BPEL (or Mediator) first, and then when it commits the global transaction it updates say 'N' to 'Y'. So in a synchronous end-end process the update from 'Y' to 'P' is likely to happen before the update from 'N' to 'Y'.

Second it is highly likely that with XA the same SQL connection will not be used for the 'N' to 'Y' and 'Y' to 'P' updates. Hence the updates may deadlock each other. If you were to use an emulated one-phase commit, internally the data source may use a single SQL connection as a way to guarantee XA, but otherwise there is no guarantee.

Third in a synchronous end to end process the DbAdapter polling update only occurs if the global transaction, and hence the BPEL process, completes successfully. Hence updating the logical delete status column twice is superfluous, as with a synchronous process and XA no meaningful intermediary step exists.

For these three reasons please only do this 2-phase logical delete pattern when using an asynchronous post to BPEL (or using parallel routing in Mediator). There you have two transactions (DbAdapter->BPEL, and BPEL), hence it makes sense to have an intermediary status value for the logical delete column. If you optimize your process to be synchronous, have the logical delete directly update from 'N' to 'P'.

If your process is asynchronous and you are using local transactions (non-XA) on inbound, the problem is similar. This time the post from DbAdapter to BPEL doesn't happen in a global transaction, so instances once delivered to BPEL can be executed immediately. By definition without a global transaction the two are not synchronized. It then becomes a race condition to see whether the DbAdapter can finish its transaction before a BPEL engine thread executing the process instance does the final 'Y' to 'P' update (in a third transaction). If you had select for update polling, you may see deadlocks. What can make this even worse is having MaxTransactionSize > MaxRaiseSize. This could result in the DbAdapter posting multiple times to BPEL during its local transaction. After the first non-synchronized post BPEL can immediately start executing the 'N' to "Y' process asynchronously. But the DbAdapter still needs to go back and call many more posts before it can commit its 'N' to 'Y' transaction. It will almost certainly lose that race condition in this case.

Hence if you are to use the two-phase logical delete make sure the process is both asynchronous and that inbound is using an XA (or at least emulated XA) data source. To verify the last step make sure your connection factory sets a value for xADataSourceName (in place of or in addition to a non-XA data source referenced by dataSourceName).