Re: Transactional haywire...

flado_at_imail.de (Vladimir Andreev) wrote in message news:<7b208869.0402040751.141480e7_at_posting.google.com>...
> > Does anyone know a way to implement following scenario in a single
> > transaction:
> >
> > From a VB application we connect to Oracle over OleDB to execute a
> > couple of stored procs in following way:
> >
> > - Application goes to the DB as user uA executing procedure pA
> > - Application goes to the DB as user uB executing procedure pB
> > - Commit or rollback (all or nothing)
> >
> > pA does a couple of things in the DB that allow pB later on to delete
> > data without being faced with foreing key constraints, so it's not an
> > option to have pA not commit because pB needs to be able to see the
> > results of pA while being logged to Oracle under another user account.
>
> No way in a single transaction -- for the reason you stated yourself:
> uA must commit, before uB does its stuff, because there is no way uB
> und uA can share a transaction.
> So, your question then translates to "How to undo a committed
> transaction?"
> Prior to 9i, you have no chance. In 9i and above, you may look at the
> flashback query feature to show you the table(s) as they were when pA
> started. This should allow you to reverse the changes if need be.
> But of course this assumes that you are still in control (e.g., the
> instance hasn't crashed meanwhile) after pB finishes, in which case
> you probably don't want to "rollback" anyway. So sorry, bad luck -- no
> automatic rollback for you.
>
> You can design and implement some kind of homemade two-phase commit
> mechanism, but you will definitely run into inconsistencies sooner or
> later.
> Or you can collect pA's changes instead of applying them directly, and
> then have pB apply them in uB's transaction, but using uA's rights (by
> calling a definer's rights stored procedure owned by uA). But I fail
> to see how is that different from calling pA in uB's session.
>
> So, in your case, I would question the reasons you don't want to
> elaborate on. It's hard to imagine *valid* reasons for such a
> constraint.
>
> Have a nice day,
> Flado

Peter, if you need two separate sessions to perform work together then I suspect you need a messaging system and a message processor. Perhaps session-A and session-B could use dbms_pipe to send the data to a deamon process, sessionC. Session-C could hold requests from one session until the matching request came in from another session and then perform the db update task. On success or failure a message is sent to both session-A and session-B indicating success or failure. Based on the consistency model of Oracle and without knowing more specifics of what it is you are trying to accomplish I do not see any way around using some kind of work coordinator process that actually performs the database update on behalf of the user sessions.

HTH -- Mark D Powell -- Received on Fri Feb 06 2004 - 15:27:01 CET