This discussion is archived
6 Replies Latest reply: May 4, 2013 10:15 AM by 1005818 RSS

Handling Concurrency in Oracle Service Bus11g

1005818 Newbie
Currently Being Moderated
Hi,

I"m searching for how to handle concurrency within OSB.

Scenario: +I've a proxy service which listens to MQ and whenever a message is picked up, it routes it to READProxy service and followed by CREATEProxy service or UPDATEProxy service.+

However, the message rate is ~1000 per hour. So there could be a chance such a way two or more messages can be picked up by LISTENERProxy service at different managed servers at a time.

Could somebody help me how to make sure READProxy service reads right data by considering the nature of concurrency?

Thank you

Edited by: 1002815 on May 2, 2013 10:50 AM
  • 1. Re: Handling Concurrency in Oracle Service Bus11g
    Anuj Dwivedi Guru
    Currently Being Moderated
    This is a re-sequencing use case so please use Mediator if you have SOA Suite as well. If you do not have SOA Suite then you may consider using the JMS Unit of order feature -

    Processing JMS messages in sequence one after another
    Sequential proxy execution in OSB

    Regards,
    Anuj
  • 2. Re: Handling Concurrency in Oracle Service Bus11g
    1005818 Newbie
    Currently Being Moderated
    Thanks Anuj for a quick reply

    As per my requirement, I've neither SOA Suit nor JMS Queue in scope. I should be able to achieve this within given technology stack: OSB and DB adatpers only.

    When a READProxy service reads a row(s) from a table, that row has to be locked until it's subsequent CREATEProxy or UPDATEProxy service is completed.
    I believe there are some row level locking mechanisms to achieve this.However, it's reduces performance and it requires Autocommit to be enabled in Oracle DB (or at service level). So is there a way where I can achieve this within OSB itself by doing any custom implementation.

    I appreciate your help

    Thanks

    Edited by: 1002815 on May 2, 2013 11:23 AM

    Edited by: 1002815 on May 2, 2013 11:23 AM
  • 3. Re: Handling Concurrency in Oracle Service Bus11g
    Anuj Dwivedi Guru
    Currently Being Moderated
    I should be able to achieve this within given technology stack: OSB and DB adatpers only.
    Well, if all READ, CREATE and UPDATE are DB operations in a same DB or in multiple local DB's which can have DBLinks between each other then why don't you wrap these operations in a stored procedure or in a DB function and invoke that SP/Function through DB adapter? By this way, you may utilize the better control of PL/SQL and at the same time, no need to worry about concurrency at OSB as well.

    Regards,
    Anuj
  • 4. Re: Handling Concurrency in Oracle Service Bus11g
    1005818 Newbie
    Currently Being Moderated
    Well. I'm not an expert at Oracle database. Could you please explain more detail how we can achieve this with SP PL/SQL.
    Does it take care by default? or we need to enable some settings if any?
    For now, we're directly calling SELECT, UPDATE, INSERT DML statements but not SP...
    If we make it SP, we'll have to make some changes in already implemented DB calls, right?

    Thank you
  • 5. Re: Handling Concurrency in Oracle Service Bus11g
    Anuj Dwivedi Guru
    Currently Being Moderated
    Could you please explain more detail how we can achieve this with SP PL/SQL.
    Nothing special. Just implement your select, update, insert logic in a SP and call that SP from SOA using DB adapter. Use "SELECT FOR UPDATE" locking feature of Oracle DB in your SELECT statement to lock the row(s).
    Make sure to not use transaction controls (commit and rollback) in your SP and use XA datasource to call it so that all the operations get performed in single global transaction. Just try to get help with someone who knows PL/SQL in your org or go through some tutorials over internet.
    If we make it SP, we'll have to make some changes in already implemented DB calls, right?
    Yes, instead of having three DML calls to DB, you will end up having only one call and that too to Stored Procedure.

    Regards,
    Anuj
  • 6. Re: Handling Concurrency in Oracle Service Bus11g
    1005818 Newbie
    Currently Being Moderated
    Yes, you're right and this is easy and one of the best strategies..however,

    Locking Strategies: --> [http://www.dba-oracle.com/t_locking_strategies.htm]

    select for update - This holds an exclusive lock on the target row and is 100% reliable.  The downside is with disconnected session which may require DBA intervention to release the locks.  In general, the "select for update" is not used in web-based systems, or on applications with unreliable network connectivity.

    So we tried to implement Timstamp based locking but couldn't succeed as it involves millisecs and requires comparision xml timestamp to oracle timestamp..

    Just try to get help with someone who knows PL/SQL in your org or go through some tutorials over internet.
    Thanks for your suggestion. I can manage to understand the basic level of SQL and PL/SQL

    I'm trying to understand whether I missed out any best approach

    Thank you

    Edited by: 1002815 on May 4, 2013 10:13 AM

Legend

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