DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Oracle Distributed Transaction

  1. #1
    Join Date
    Jun 2008
    Posts
    2

    Oracle Distributed Transaction

    Hi
    I am coding a middleware managing distributed transactions.
    the distributed transaction has 2n transactions branches and involves n oracle DBMSs (oracle)
    and has the following form:

    -connects to n DBMSs,
    for each,
    send a read sql statement, get a resultset consisting of one record
    do some processing on the attributes
    send an update sql statement of the same record

    I don't know how write such code, the sample code http://download-uk.oracle.com/docs/c...4/xadistra.htm
    doesn't show such case, indeed each pair of branches are dependent
    cheers

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Nothing new in your scenario, you may want to read a little about...

    1- J.C. Date definition and expected features of a Distributed Database -it is nice to know something about what you are talking about.

    2- Two-phase commit and Oracle RECO process -that's the way Oracle implements it.

    3- Oracle Database Administrators Guide, Managed Distributed Transactions chapter-I know this is something that might surprise you but Oracle documentation is pretty good.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jun 2008
    Posts
    2

    re

    Hi,
    thank you for you reply, my software architecture plays a role
    hereafter the code commented

    Iterate on involved DBs (they are n)
    // look up for record prKey
    String sqlB1 = "SELECT * FROM item WHERE recordKey = "+ prKey;
    //create a transaction branch instance TB from sqlB1
    //enqueue TB in the transaction branches queue of the distributed transaction T
    //assume here that I get pAttrOld1 and pAttrOld2 in the resultset of sqlB1, that I don't understand how i will obtain

    //do some processing
    //the computation is complex
    String pAttr1 = pr.computeRecordAttribute(pAttrOld1, p, dfLevel, dataAttr1);
    String pAttr2 = pr.computeRecordAttribute(pAttrOld2, p, dfLevel, dataAttr2);

    // update record prKey
    sqlB2 = "UPDATE Item " +
    "SET pAttr1 TO "+ pAttr1 + "," +
    "pAttr2 TO "+ pAttr2 + "," +
    "dKey0 TO "+ drKey +
    "WHERE recordKey = "+ prKey;
    //create a transaction branch instance from sqlB2
    //enqueue TB in the transaction branches queue of the transaction T
    // end iterate

    Finally enqueue T in the working queue of a Transaction Manager
    the latter will enqueue the transactions branches in the working queues of threads connecting to the different DBs and wait a time out until all replies arrive
    the connecting threads will enqueue the prepare result, XAResource, Connection XAConnection related to each transaction branch in the reply queue of the transaction manager
    the transaction manager decides to commit or rollback T depending on the prepare results obtained (it will also close connections ...)
    as you see here I need sqlB1 resultset to execute sqlB2, how can I manage that with this architecture?
    any advise concerning the architecture is welcome

    cheers
    rym

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width