RE: Autonomous transaction not committed

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.

--
http://www.freelists.org/webpage/oracle-l

Other related posts: