Oracle Distributed Transaction
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,
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
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.
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
Click Here to Expand Forum to Full Width