Oracle SOA Database Adapters provide a polling mechanism that will periodically query a table to see if a there is a new or changed record. If so, it can trigger a BPEL process. This is enormously useful. However, on one client I ran into a series of issues with database pollers in a clustered environment when they … collided. (queue dramatic music)
The issue began one quiet morning early in the project after I started my WebLogic Admin Server and 2 SOA 11g Managed Servers. I then deployed my process with my shiny new database poller. I was very excited to see it work in the Development Environment. I had tested it the night before in my local VM and it worked great.
I won’t bore you with the details of what the overall process did, but suffice it to say, I was not excited with the database error I received when the pollers fired. You heard me right … pollers.
I did NOT anticipate that SOA would have a separate database poller for each managed server for the same process. However, after consulting with colleagues, I found that they too had seen this rather odd and seemingly illogical behavior.
Luckily after a bit of sleuthing, I found a nice solution – the Distributed Polling flag. As usual, the A Team came to the rescue with an outstanding article that nicely explained the inner guts of how this works: DB Adapter – Distributed Polling (SKIP LOCKED) Demystified.
In short, this flag doesn’t allow more than 1 server to issue a lock on a table at a given time. In effect, this ensures that you end up with 1 database poller for your process and not 1 per managed server (however, there is a twist to this that I’ll discuss in a moment).
After finding this fix, I redeployed my process and the sun came out, the angels sang and everything started working perfectly.
However…
Several days later, I ran into another more complex issue when I had to take down one of my two SOA managed servers and bring it back up a bit later after fixing some memory settings.
In this system, there was an almost constant stream of new records that were flowing into the table that was being polled. The database poller was doing a logical delete against this table to mark that the record was being processed. The poller ran every 2 minutes which the client assured me would give us plenty of time to run the many updates against the downstream databases and systems. Easy peasy, lemon squeezy … or so I thought.
The issue that I ran into occurred when the updates for 1 record took longer than 2 minutes – which I’d been assured could NEVER happen. Unfortunately, it did and in that case, the database poller fired up, grabbed the next record and before we knew it, one of the downstream legacy (seriously ancient) systems that didn’t like to be rushed started getting more updates than it could handle after this situation started repeating itself over and over. Eventually the system crashed because the legacy system couldn’t handle new requests coming in when old ones were still processing. This reminded me of the I Love Lucy episode at the pie factory.
I was not a happy boy that day, but luckily the next day I found a very simple and effective solution to this problem. It occurred to me, that since pollers can’t be shut down, that I could create a Poller Control Table to control precisely when BPEL invocations were allowed to happen. To be clear, this is a table that I created in my application’s database schema.
In other words, instead of using the database poller to look for changes in the source table every X seconds, I set up a database poller to query a simple Poller Control Table. This provided the perfect solution to my problem.
Using this, I could have my Database Poller looking at this Control Table and only firing the BPEL process if the POLLER_ACTIVE flag = “Y”. In this case, it would do a logical delete by setting the flag to “N”.
This configuration prevented the database poller from triggering another instance while our Jurassic legacy system was busy generating a report by a chiseling out a stone tablet.
When the legacy system was finished, the process would reset the flag on the Control Table to “Y” which would then be picked up by the database poller when it was automatically fired.
The other advantage to this solution was that now I had a way to turn off the database poller by simply updating the POLLER_ACTIVE flag to “N”.
While Oracle SOA database adapters are very powerful, they do have several idiosyncrasies which can present challenges. In addition to this article, I have written several others documenting how I was able to overcome some of these challenges:
- Oracle SOA Database Adapter 101 – WebLogic Configuration Steps
- This article provides a detailed example of how to configure WebLogic to use Oracle’s SOA Database Adapter. It also includes a zip file with a sql script to set up a test database and a SOA 11g project that demonstrates the use of the database adapter to query a table in this database.
- How to Make BPEL Gulp
- If you’re using Oracle BPEL, how do you deal with copying huge resultsets of data from one database to another when your system has an annoying habit of running out of memory or expiring JTA transactions all the time?
Now that the world is safe again from colliding database pollers, in upcoming blogs I’ll discuss other puzzling predicaments that I have found myself in which typically include BPM and SOA process patterns, software development best practices and development methodologies.
