Re: Allowing Oracle transactions to span client processes

Thanks Mark, your suggestions pretty much reinforce what I suspected.

> > I want to be able to do work on the database from multiple processes
> > and have them all commit as a single transaction.

> > One process will always 'control' the transaction (i.e. it is
> > responsible for starting a transaction, calling other processes and
> > then commiting or rolling back the transaction).
>
> Ben, there is no nifty feature to allow this. You will either have to
> buy a transaction manager or write a simple one.

I'm intrigued by this option. I had briefly considered whether it would be possible to do such a thing relatively easily, but don't really know where to start. Does anyone know of any resources that might get me started (or at least let me work out the size of the task I'd be taking on)?

>
> You could have all your programs communicate with one program that
> actually performs all the updates via sockets or other communication
> features. You could make use of Oracle features such as dbms_alert
> and dbms_pipe to pass the data to the "transaction manager" Depending
> on exactly what you need to do you might be able to use a packaged set
> of routines and a holding table to collect data from various sources,
> associate the rows into units of work, identify the associated rows
> with a application "transaction" number, process the associated rows,
> and indicate success or failure to the various programs

I should have mentioned that we've bandied this idea around a lot already and come to the conclusion that it would be a massive amount of redesign and recoding, as well as having performance impacts on certain clients.

> But whatever you do you will have to do it manage it using Oracle's
> standard transaction management logic.

Yeah, I'm hearing you. :)

Thanks again for the comments - this sort of information from others is invaluable in this minefield. Received on Fri Sep 05 2003 - 02:35:51 CDT