This discussion is archived
7 Replies Latest reply: Dec 27, 2012 3:48 AM by Bagagem RSS

Parallel execution of a bpel process

Bagagem Newbie
Currently Being Moderated
Hello,

I have a DbAdapter that is mapping a table and exposes operations to insert, update and select data.
This DbAdapter is being referenced in two bpel processes - bpelProcess1 and bpelProcess2:
1- bpelProcess1 -> this process firstly performs a select operation on the DbAdapter, querying for a certain row. If it returns a record, then an Id of the record is saved for further usage. If it returns nothing, then the DbAdapter is invoked again to perform an insert, and the Id of the newly created record is saved as well.
Then, this bpelProcess1 also invokes the second bpel process (bpelProcess2, the same that references the DbAdapter) and provides the Id retrieved before by the select/insert operation;
2- bpelProcess2 -> this process, after a few conditions, performs the update operation of the DbAdapter, using the Id provided by the bpelProcess1.

Now, what is the best approach to avoid incoherent data in the database?
If the process bpelProcess1 is invoked twice in a row, both instances of the process perform the select, searching for the same row - and none of them will get any data. Therefore both follow to the insert process. Only one of them should insert, the second one should already find it in the database.

The database is SqlServer, so if I change the DbAdapter from mapping a table to call a ...:
- function --> SqlServer doesn't allow to perform DML inside functions;
- procedure --> how can I return the values I need from insert/select?

What are your thoughts?

Best regards,
Bagagem
  • 1. Re: Parallel execution of a bpel process
    vladodias Guru
    Currently Being Moderated
    Bagagem wrote:
    Now, what is the best approach to avoid incoherent data in the database?
    If the process bpelProcess1 is invoked twice in a row, both instances of the process perform the select, searching for the same row - and none of them will get any data. Therefore both follow to the insert process. Only one of them should insert, the second one should already find it in the database.
    That's a classical concurrency control problem... It will arise in any language, not only in BPEL... You can solve it by using optimistic or pessimistic currency control... See link bellow for reference...
    http://publib.boulder.ibm.com/infocenter/soliddb/v6r3/index.jsp?topic=/com.ibm.swg.im.soliddb.sql.doc/doc/pessimistic.vs.optimistic.concurrency.control.html

    I believe an optimistic approach for your case will be to create a unique index and let both instances insert the row... One of the instances will not be able to commit at the end of the transaction (because of the unique index)... So the last instance will fail, but will execute just fine after retry (it will find the row)... A pessimistic approach will be more complex to achieve as you can not lock something that is not there yet, you may then need some extra control table to implement a pessimistic approach...

    Hope this helps...

    Cheers,
    Vlad
  • 2. Re: Parallel execution of a bpel process
    Bagagem Newbie
    Currently Being Moderated
    Thanks for your attention, Vlad

    From the approaches you mentioned, I think the most suitable would be the pessimistic:
    Pessimistic concurrency control (or pessimistic locking) is called "pessimistic" because the system assumes the worst — it assumes that two or more users will want to update the same record at the same time, and then prevents that possibility by locking the record, no matter how unlikely conflicts actually are.
    This will definitly happen, I can't assume otherwise.
    I was more thinking on something like... limiting the bpel process to a single thread of execution, to avoid parallel execution. This process will not be heavily loaded, if a serialization is possible then performance won't be affected (much).
    Is it possible?

    Before the message reaches the bpel process bpelProcess1, it comes from an OSB proxy service; this proxy service calls a business service (still OSB); the business service invokes a direct binding in the SOA composite, which redirects to a mediator which finaly delivers the message to the bpelProcess1.

    Does any of these components allow serialization?

    Best regards,
    Bagagem
  • 3. Re: Parallel execution of a bpel process
    vladodias Guru
    Currently Being Moderated
    Hi Bagagem,

    This is a very interesting topic... As far as I know BPEL has is no explicit support to things like mutual exclusion, critical areas, semaphores and so on, like we have in java and other languages...

    There are some articles available on BPEL singleton (Matt Wright) and semaphores (Marc Kelderman)... Those solutions may suit your use case...
    http://blogs.bpel-people.com/2009/04/writing-singleton-bpel-process.html
    http://orasoa.blogspot.com.au/2008/01/bpel-handling-semaphores.html

    Even though, I'm not sure if those approaches will be safe in clustered/distributed environments...

    Sorry, but I don't see any easy solution here... Even though the requirement can sound quite simple, possible solutions tend to be complex...

    Cheers,
    Vlad
  • 4. Re: Parallel execution of a bpel process
    Bagagem Newbie
    Currently Being Moderated
    Hi Vlad,

    Regarding the articles you mentioned, I only have seen the first one, about BPEL singleton, and a few others mentioning the same.
    About the semaphores, as far as I understood the solution is based on the singleton option...
    I am avoiding the singleton option because conceptually it is not what I pretend: my requirements allow me to have several instances of the bpel process, they just can't be parallel!

    I find it weird that Oracle BPEL doesn't provide a way to define a maximum number of parallel threads for a given process...

    Nevertheless, - I still think this is a subject of discussion - I could manage a workaround to overcome my issue, by changing the accesses to the database.

    Originally I has a first step - a db adapter invoke - which consisted on a select to the database, and depending on the retrieved data I needed to perform a second step - another db adapter invoke - : an insert.
    After changing a bit the process, I could gather both in a single stored procedure in the database, managing the concurrency issue inside the database.

    Best regards,
    Bagagem
  • 5. Re: Parallel execution of a bpel process
    vladodias Guru
    Currently Being Moderated
    managing the concurrency issue inside the database
    Yep, I believe that's been the most common approach for this type of case...
  • 6. Re: Parallel execution of a bpel process
    NeerajSehgal Journeyer
    Currently Being Moderated
    Hi Bagagem,

    If you're happy to change the architecture of your process, Mediator Re-sequencing can help you. Poll the messages through a Mediator and process them in FIFO order (if the same ID repeats) else they'll be processed in parallel. There could be recovery challenges though for which even I'm seeking a robust approach.

    For more details see below links:
    Re: Regarding recovery in One way Bpel Process
    http://docs.oracle.com/cd/E17904_01/integration.1111/e10224/med_resequencer.htm
    http://eelzinga.wordpress.com/2010/05/14/oracle-soa-suite-11g-resequence-messages-in-mediator/

    Regards
    Neeraj Sehgal
  • 7. Re: Parallel execution of a bpel process
    Bagagem Newbie
    Currently Being Moderated
    Hello Neeraj, thanks for your input.

    At the moment I don't have time and availability to try a different approaches of the process architecture, as the process has been working nice for the last couple weeks with the current approach.

    It is a very insteresting way to implement a solution, in the near future if I come across a similar challange I may try mediator re-sequencing :)

    Thanks,
    Bagagem

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points