Re: anyway to commit/rollback for another session?

andreyNSPAM_at_bookexchange.net (NetComrade) wrote in message news:<3f157ba2.768376867_at_nyc.news.speakeasy.net>...
> Not quite sure what problem I am imagining, but here's a display of
> existance of a TX lock.
>
> SYS user:
>
> SQL> select * from v$lock where sid=255;
> no rows selected
>
> DBLINK user:
>
> SQL> select 1 from dual_at_remote;
> ---------
> 1
>
> SYS user:
> 1* select * from v$lock where sid=255
> SQL> /
>
> ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME
> BLOCK
> -------- -------- ---- -- ------- ------- ----- ------- -----
> ----------
> 01A8B230 01A8B2FC 255 TX 262173 718487 6 0 166
> 0
>
> DBLINK user:
> SQL> rollback;
>
> SYS user:
> 1* select * from v$lock where sid=255
> SQL> /
>
> no rows selected
>
>
> SAVEPOINT scenario:
>
> dblink user:
> SQL> savepoint before_dblink;
>
> Savepoint created.
> SQL> select 1 from dual_at_remote;
>
> 1
>
> SYS user:
> 1* select * from v$lock where sid=255
> ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME
> BLOCK
> -------- -------- ---- -- ------- ------- ----- ------- -----
> ----------
> 01ABAFA8 01ABB074 255 TX 655383 703857 6 0 20
> 0
>
>
> DBLINK user:
> SQL> rollback to before_dblink;
>
> Rollback complete.
>
> SYS user:
> SQL> /
>
> ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME
> BLOCK
> -------- -------- ---- -- ------- ------- ----- ------- -----
> ----------
> 01ABAFA8 01ABB074 255 TX 655383 703857 6 0 73
> 0
>
> DBLINK user:
> SQL> rollback
> 2 /
>
> Rollback complete.
>
> SYS user:
> SQL> /
>
> no rows selected
>
>
>
> On Tue, 15 Jul 2003 16:44:18 -0700, Daniel Morgan
> <damorgan_at_exxesolutions.com> wrote:
>
> >NetComrade wrote:
> >
> >> On 14 Jul 2003 19:27:46 -0700, Mark.Powell_at_eds.com (Mark D Powell)
> >> wrote:
> >>
> >> >
> >> >Netcomrade, I do not believe there is any way to commit or rollback
> >> >for another user's session.
> >> >
> >> >Are you sure that the distributed applications 1- all got changed, 2-
> >> >that the changes got moved into production, and 3- in the case of
> >> >desktop software all users received the new version?
> >>
> >> Mark,
> >> I've read and re-read your questions, and I think you misunderstood my
> >> problem.
> >> All distributed SELECTS require a TX lock which can only be taken off
> >> by a commit. All DML is committed already, I just want to commit for
> >> idle sessions that could potentially be preventing a rollback segment
> >> from recycling.
> >> .......
> >> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> >> remove NSPAM to email
> >
> >The answer to your question ... is No!
> >
> >But I should add that unless you have evidence to the contrary I don't
> >think the problem you imagine can possibly exist and am inclined to refer
> >you to the Concepts and Architecture manuals.
> >--
> >Daniel Morgan
> >http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> >damorgan_at_x.washington.edu
> >(replace 'x' with a 'u' to reply)
> >
> >
>
> .......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email

Netcomrade, the rollback_to_savepoint does not end the transaction, it just throws part of it away. The rbs entry is taken at the start of the transactions and since the transaction is still active it is still held. This is the behavior I would expect based on the documentation in the Distributed manuals, which state that the lock is released on commit or rollback, which end transaction. In your case the transactions are still technically active. I believe this behavior has existed since at least version 7.0 if not earlier.

Related articles at the cooperative FAQ: Why does it seem that a select over a db link requires a commit after execution?
 http://www.jlcomp.demon.co.uk/faq/dblink_commit.html

How do I find distributed queries / transactions (either issued from or connecting to this instance)?
http://www.jlcomp.demon.co.uk/faq/find_dist.html

Is it possible to change some of the savepoint calls or rollback_to_savepoint statements to commits/rollbacks. Ending the transaction is the only solution I see to your problem.

HTH -- Mark D Powell -- Received on Thu Jul 17 2003 - 08:44:08 CDT