RE: Autonomous transaction not committed
- From: "Tanel Poder" <tanel@xxxxxxxxxx>
- To: <yong321@xxxxxxxxx>
- Date: Fri, 30 Jan 2009 05:16:19 +0200
Hi Yong Huang,
I'm not sure whether you received my earlier reply to this thread, but I
mentioned this about identifying owner sessions for transactions:
"Every recursive/autonomous transaction has its own transaction state
object, so you can join v$transaction.ses_addr with v$session.saddr to find
out which transactions belong to which session."
Yep, unless using XA then if a session is having multiple transactions, the
newer ones are recursive or autonomous ones. Bit 0x20 in v$transaction.flag
shows if a transaction is recursive transaction (recursive data dictionary
update) but if this flag is not set, then that transaction should be an
user-defined autonomous one.
I haven't checked whether audit transcations are considered as recursive or
autonomous ones as far as the flag is considered.
Btw, Oracle uses recursive sessions which are created under your top-level
session when recursive data dictionary updates are done, also I've seen
recursive sessions created for plain autonomous transactions (maybe due a
bug) in 10.2.0.1.
Tanel Poder
http://blog.tanelpoder.com
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Yong Huang
> Sent: 30 January 2009 00:56
> To: oracle-l@xxxxxxxxxxxxx
> Cc: post.ethan@xxxxxxxxx
> Subject: Re: Autonomous transaction not committed
>
> Ethan,
>
> I think I find a way to identify a transaction to be
> autonomous, or identify the session that currently has an
> autonomous transaction. It's based on the fact that a session
> can only keep two transactions open at the same time, and the
> second one must be an autonomous transaction. (Does anybody
> object?) You can test this case like this:
>
> delete from t1 where rownum = 1;
> declare
> pragma autonomous_transaction;
> begin
> delete from t2 where rownum = 1;
> dbms_lock.sleep(10);
> end;
> /
>
> In another session, find the two transactions in
> v$transaction whose ses_addr is saddr of v$session for the
> above session. The one in v$transaction with a later
> start_time must be an autonomous transaction.
>
> If the second transaction started within the same second of
> the first one, you can't tell which one is autonomous. But
> you can still identify *the session* that has an autonomous
> transaction, i.e. the session having two transactions.
>
> My earlier assumption is wrong. I thought an autonomous
> transaction would open a new session (as in the case of
> parallel executions).
>
> Yong Huang
>
> --- On Wed, 1/21/09, Yong Huang <yong321@xxxxxxxxx> wrote:
>
> > From: Yong Huang <yong321@xxxxxxxxx>
> >
> > Sorry. What I proposed was not working. Bad QA.
> >
> > Yong
> >
> > --- On Wed, 1/21/09, Yong Huang <yong321@xxxxxxxxx> wrote:
> >
> > > From: Yong Huang <yong321@xxxxxxxxx>
> > >
> > > Interesting question. There's no statistic specifically for that.
> > > But you can infer from "implicit" session logons.
> > > Suppose your
> > > audit_trail is turned on. Audit session. Sample the value
> of 'logons
> > > cumulative' statistic, minus 'queries parallelized',
> minus number of
> > > times any job ran, minus the number of new audit trail
> entries for
> > > action_name 'LOGON'. Those "implicit"
> > > session logons should be,
> > > or at least include if I miss any possibility, autonomous
> > > transactions.
> > >
> > > Yong Huang
>
> > > > Is there a sesstat which I can look at to monitor the number of
> > > > active autonomous transactions in a session. I would like to
> > > > monitor this in trace or my debug package to resolve an issue.
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
