Verse Of The Day

Wednesday, May 23, 2012

OSB Table Poller Issue Setting Status

After building quite a few Oracle Service Bus table pollers, my last project should have been something I could do with my eyes closed. Poll a table looking for certain 'actions', and call a few web services to make that 'action' happen in another enterprise system.

A very simple service flow:
1. JCA table poller adapter, configured for logical delete (change an indicator from one value to another), set up for distributed polling so it will work in a clustered environment. Basically look for records with status of N, switch it to P.
2. Wrap that in a proxy that simply routes to one of several other proxies based on the 'action'. (local transport)
3. Those other proxies call web services based on the values in the original message. On success, the response pipeline will set the status to C. If any of those services fails or returns an error, an error is raised to the proxy's error handler, which sets to the status to E.

That's it, dead simple. If there are no errors, everything works great, record goes to C, we're all good. 

However, if an error is raised on the request pipeline, the status doesn't get changed to E. Using the same service callout that works for C, does not work for E.

I watch my WebLogic logs and my TOAD query at the same time and notice some things:
1. For a success, I see in TOAD the record stays in N status until all the web services have been called. Once the final service returns, it immediately goes to P then C. All good.
2. If an error is raised along the way, or even if I manually try to set the status anywhere on the request pipeline, that call hangs until the transaction timeout occurs, then that update to E fails, as well as the update to P. End result, the record stays in N and gets pulled again, and again, and....

(As an extra red herring to keep me busy, I get a log full of XA transaction type errors, so I spend way too much time working with the DBA trying to set up XA permissions.)

So it seems that all along the request pipeline, the OSB has that row locked for update, but hasn't done the update to P yet. If I try to set it in my flow, the two updates deadlock until the XA timeout occurs. However, when setting it on the response pipeline, the update to P has already happened, so it works there.

I could have rewritten all my proxies to only change the status on the response pipeline, but that would have been a painful rewrite of the flows. An easier solution was to have the poller proxy immediately drop the message on a JMS queue, and then have a queue listener proxy pick those up and route them. Doing this, I see the record go to P immediately, no matter what, and then success or failure, my status changes without any timeouts or XA errors.