RE: Autonomous transaction not committed

From: Tanel Poder <>
Date: Thu, 22 Jan 2009 03:06:37 +0200
Message-ID: <B290000CB5754112925322B5806B479B_at_porgand>


> Yes. But he still needs to differentiate between different
> types of recursive transactions. Right? Some are autonomous,

I didn't see OP asking for that...

> some are code in PL/SQL block or procedure/trigger etc
> without using autonomous_transaction pragma, and some are

I don't really see a problem here (unless you have some transaction state object leak bug). Recursive/autonomous transactions don't just stay lingering on once the recursive call completes.

When you exit a block with active autonomous transaction, it will roll back with error below:

SQL> declare
  2 procedure p is pragma autonomous_transaction; begin insert into t values(9); end;
  3 begin
  4 p;
  5 end;
  6 /
ERROR at line 1:

ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at line 2
ORA-06512: at line 4

> PDML. I wish v$transaction had a column or a bit in flag
> suggesting which type.

There's a flags column and also few additional columns decoded from flags in v$transaction, but not all types are covered... There's a "recursive" column there for example.

For better picture, a process state dump (search for "trans" state objects) may give more detail.

Tanel Poder

Received on Wed Jan 21 2009 - 19:06:37 CST

Original text of this message