Re: anyway to commit/rollback for another session?

Hi NetComrade,

if your intent is only distributed queries, then you might want to try 'set transaction read only' before you fire-up the distributed query. that way, there won't be any TX locks nor a rollback segment being active for ever.

hth,
Stan

NetComrade wrote:
> e.g. can i commit for session sid=100, serial#=100?
>
> This has to do with distributed queries.
> After we've been committing after each select over the link, we have
> changed the code to savepoint/rollback to savepoint around the select
> query over the link. I can almost swear I tested it, but it doesn't
> seem to work (TX lock remains)
>
> Since I don't want to commit after each select over the link, I'd like
> to commit for sessions that have certain properties (e.g. have a TX
> lock open, program set to application server).
>
> Johnatan L. at some point suggested:
> (http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=aBKs6.604%24GG4.11358%40news1.oke.nextra.no&rnum=41&prev=/groups%3Fq%3Dcommit%2B%2522other%2Bsession%2522%2Bgroup:*oracle*%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26start%3D40%26sa%3DN)
> "
> However, committing after every select is an undesirable
> overhead - the cost of starting a new transaction is not trivial.
> It is interesting to note that Oracle's web application
> server always finishes a dialogue with 'reset package
> states' and 'rollback'. If your web app is connecting
> and disconnecting all the time, then it will not scale -
> so you should probably emulate Oracle's approach,
> with a near-permanent connection that 'resets' itself
> after each dialogue. This should supply the single 'rollback'
> you need to clear the local transaction. Since Web apps tend
> to be short snappy dialogues, this will probably be
> sufficient to avoid any side effects on size of rollbacks.
> "
> This is not easy to do with our app, so I'd like to just manually
> rollback or commit for sessions that hold a TX lock for too long.
> Right now I just have a script that kills sessions (very infrequently)
> that do not allow a rollback segment to be recycled.
>
> Thanks.
>
> .......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email
Received on Sat Jul 26 2003 - 15:20:04 CDT