Oracle SOA Database Adapter Polling

I had a requirement to poll a database table, pass some values from the table to a service endpoint, and update the original record with values from the response. This is a fairly simple use case; however, I encountered some odd behavior when the solution was deployed to a clustered environment. Namely,

1. Multiple SOA instances were being created for a single database record, one for each DbAdapter deployment on the SOA managed servers, and
2. I was updating the Logical Delete Field in a BPEL process, and once the database adapter thread was finished, it too was updating the Logical Delete Field after I updated it.

Multiple Instances

Oracle documentation references capability called Skip Locking used in Distributed Polling. That is to say that the polling SQL will fetch the resultSet into a cursor. Any record that is locked in the fetch will be skipped. Unfortunately, it doesn't lock the record from another database adapter instance polling the same table and updating that record. There is also documentation that indicates an alternative approach using a MarkReservedValue that says, if you use a reserved value, skip locking will not be used.

Well, using a combination of providing a MarkReservedValue and enabling Distributed Polling was the only way I could get the database adapter to stop creating multiple instances for the same record.

Logical Delete Field Updated When Thread Completes

You cannot update the Logical Delete Field inside of a BPEL process and expect that value to be persisted. The last thing that the database adapter thread does before it sleeps is update the value of the Logical Delete Field with whatever you specified as the Read Value in the configuration.

The better solution is to add a new field to the database table and use it as the Logical Delete Field. Then, you can invoke another database adapter to update the value of whatever field you choose, and it will be persisted.