RE: Autonomous transaction not committed
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:
,ksuudsna ,ksusepro ,ksspaown
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.Received on Fri Jan 30 2009 - 22:07:40 CST