Two Phase Transaction / Collaborative Transaction

Question

The script is

We have two applications A and B, both of which run in separate database transactions (Oracle 9i)

Appendix A - inserts some data into the database, then calls Appendix B Appendix B - inserts some data into the database associated (via foreign keys) with the data A. Returns the "ID" to Appendix A Appendix A - uses the ID to insert additional data including identifier from B

Now, since these are separate transactions, but both rely on data from each other's transactions, we need to make each application between calls. This, of course, is very difficult to roll back if something goes wrong.

How do you approach this problem with minimal code refactoring. Surely this is such a common problem in the world of SOA?

------ Update --------

I could not find anything in Oracle 9i, however Oracle 11g provides DBMS_XA , which does exactly what I was after.

Answer 1

You have three options:

• Redesign the application so that you do not have two different processes (both connecting to the database) that are written to the database and collapsed them into one application.

• Create a C application that processes all database transactions for A and B.

• Fold your biphasic fix. Appendix C acts as the focal point. C signals A and B to ask if they are ready to commit. A and B execute their processing and respond to C with the answer “ready” or “failure” (note that C must have a timeout to avoid endless waiting if one process freezes or dies). If both answers are ready, then C tells them to commit. Otherwise, it sends a rollback signal.

Note that you may run into problems with option 3 if application A relies on foreign keys from application B (which you did not specify, so this may not be a problem). Consent to read Oracle is likely to prevent this from being allowed, as the transaction of application A will begin before application B. Just a warning.

Answer 2

A few suggestions:

• Use Transaction Compensation . Basically, you can undo a transaction that you did previously. The hard part is figuring out which transactions are rolling back.

• Record application data A and B in the database using a flag indicating that it is temporary. Then, after everything has been verified, change the flag to indicate that the data is final. Run a batch job overnight to clear data that has not been completed.

Answer 3

Perhaps you could insert data from application A into the time domain so that application B can insert insertions of both A and B without significant changes in any of the applications. It is not particularly elegant, but it can do the trick.

In another scenario, you can add a “confirmation” field to your data, which is updated after the successful completion of the entire process. If it crashes at some point, it may be easier to track the records necessary for the rollback (in fact, delete it).

Answer 4

I like both of the solutions presented, so I avoided posting this for a while. But you can also make an update to the main table, having previously saved the state of the affected rows in the cache.

This could be combined with a two-level one (the Zathrus motion control system was proposed) - because it really was not needed to solve the neon use of the table or “sketch” tables. The disadvantage of this is that you will need your procs / logic to consult the main table from the workspace or the workspace from the main table - or maybe save your flag in the main table and set it back when you transfer data to the main table.

The lady in our team is developing something similar for our real-time system using constant work tables.

Answer 5

App_A =={0}=> database # App_A stores information for App_B App_A ------> App_B # App_A starts App_B App_B <={0}== database # App_B retrieves the information App_B =={1}=> database # App_B stores more informaion App_A <={2}== App_B # App_B returns 'ID' to App_A App_A ={2,3}> database # App_A stores 'ID' and additional data 

Is this just me, or does it seem that application B is essentially just a subroutine A. I mean that application B does nothing until A asks for it, and application A does nothing until B returns the identifier. This means that it makes no sense to have them in different applications or even separate threads.