Transactional haywire...

[Peter]

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.

There are several reasons (on which I won't elaborate here) why we
can't use a single user to do the work.

Any comments appreciated.

Peter

[Tony]

Re: Transactional haywire...

peter1@mail.be (Peter) wrote in message news:<7912d63f. 0402040340.75f1 3943@posting.go ogle.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.
>
There are several reasons (on which I won't elaborate here) why we
can't use a single user to do the work.
>
Any comments appreciated.
>
Peter

Of course the answer is "you can't". But you knew that already,
didn't you? You really should elaborate on why you have to do it that
way. It might give an insight into the solution.

[Vladimir Andreev]

Re: Transactional haywire...

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

[Mark D Powell]

Re: Transactional haywire...

flado@imail.de (Vladimir Andreev) wrote in message news:<7b208869. 0402040751.1414 80e7@posting.go ogle.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 --