RE: Autonomous transaction not committed
- From: "Tanel Poder" <tanel@xxxxxxxxxx>
- To: <yong321@xxxxxxxxx>
- Date: Sat, 31 Jan 2009 06:07:40 +0200
Hi, answers below:
> Yes, I saw your earlier message. But I don't see how that
> solved the OP's question. We all know it's easy to associate
> a row (or two) in v$transaction with a row in v$session. But
> there's really no difference in all columns, including flag,
> of v$transaction (x$ktcxb.ktcxbflg which includes 'recursive'
> bit i.e. bit x20), between a regular transaction and an
> autonomous one. Let me know if I miss anything in your
> message. I only checked a few x$kt% tables, with no success.
> My test is on both 10.2.0.1 and 10.2.0.4. An autonomous
> transaction does not create its own session.
I ran my test case again on 10.2.0.1 on Windows and it looks I was wrong,
the recursive session is created regardless autonomous transaction, when I
execute a transaction from inside a package. It doesn't happen on 10.2.0.4
so I suspect its some bug which leaves this state object lingering around.
Note that recursive sessions can't be seen from v$session, you need to query
x$ksuse for that with somethin like this:
select
decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?')
,ksuudsna
,ksusepro
,ksspaown
from
x$ksuse
where ksusepro=hextoraw('<process PADDR from v$process/v$session>')
The ksspaown field shows the owner of session state object. For recursive
sessions, the owner is not the process state object (v$process.addr) but a
call state object instead (which you'll see from a system/process state
dump).
This helps to indicate whether a transaction was started from top-level call
(an users SQL statement) or from a recursive call.
Note that the above explains recursive sessions and it doesn't' doesn't
address OPs question though.
>
> Now here's something I have to do before I can sleep. I made
> a mistake again, in assuming OP's session always already has
> an active transaction before starting an autonomous one.
> That's a very wrong assumption! If a session starts a
Hehe, you're right, I assumed that as well, thanks for pointing this out.
> transaction as an autonomous one, there's no way we can tell
> it's autonomous or not. (If the sessions he's interested in
> are guaranteed to create a "dummy" transaction first e.g.
> insert into junk values (1), then what I proposed works, and
> it works only if the parent transaction is guaranteed not to
> commit or rollback, which sounds absurd.)
I looked into couple of system state dumps in hope to see the recursive
transaction state objects belong under a call state object (not the session
state object as normal top-level transcations), but it wasn't the case... So
it looks like there's no easy way for figuring this out...
Tanel.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
