Thanks for the question, Oraboy.

Asked: December 18, 2011 - 8:16 pm UTC

Last updated: December 20, 2011 - 8:01 am UTC

Version: 11.2.0.2

Viewed 10K+ times! This question is

You Asked

Hi Tom -
Just happened to notice this weird behavior at my client place.. Part of my streams (capture) troubleshooting, I was tracing back the duration of active transactions.

I just had a simple script polling v$transaction polling info and spooling the duration into a text file (duration = sysdate - start_date) for active transactions. Then I added v$session to the query to get session info (v$session.taddr = v$transaction.addr used as join).. I doubted a few culprit rows were missing and I made it at outer join and now I am getting few more transactions without a session.

Question is.
a) have you seen this behavior of transaction being active in v$transaction without any matching row in v$session.. Any clues on what could be the root cause would be much appreciated.

( Environment: 3 tier archiceture with transaction manager sitting somewhere in middle tier (Weblogic). All are XA transactions from application and database calls are mostly distributed transactions )

Thanks in advance & Happy holidays.

PS: I just happened to see "Submit your question" and am sending this.. I will get access to server on Monday and can upload the actual monitoring script as is, if needed.

and Tom said...

if a session has more than one transaction going - and I believe you'll see that a lot in XA as they are an external resource manager and some fairly odd things take place due to that - then there isn't a single transaction associated with a session - but many and the simple data model of a one to one relation doesn't work

ops$tkyte%ORA11GR2> create table t ( x int );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace procedure p( p_count in number )
  2  as
  3      pragma autonomous_transaction;
  4  begin
  5      if ( p_count = 0 )
  6      then
  7          dbms_output.put_line( 'at the end: ' );
  8          for x in ( select to_char(rownum,'09') || ') transaction ' || rawtohex(vt.addr) ||
  9                            ' associated with session (' || vs.sid || ', ' || vs.serial# || ')' data
 10                       from v$transaction vt, v$session vs
 11                      where rawtohex(vt.addr) = vs.taddr (+) )
 12          loop
 13              dbms_output.put_line( x.data );
 14          end loop;
 15          for x in ( select username || ' - ' || taddr  data
 16                       from v$session
 17                      where username = user )
 18          loop
 19              dbms_output.put_line( x.data );
 20          end loop;
 21      else
 22          insert into t values ( p_count );
 23          p( p_count-1 );
 24      end if;
 25  
 26      commit;
 27  end;
 28  /

Procedure created.

ops$tkyte%ORA11GR2> insert into t values ( 0 );

1 row created.

ops$tkyte%ORA11GR2> select 'transaction ' || rawtohex(vt.addr) || ' associated with session (' || vs.sid || ', ' || vs.serial# || ')' data
  2    from v$transaction vt, v$session vs
  3   where rawtohex(vt.addr) = vs.taddr (+);

DATA
-------------------------------------------------------------------------------
transaction 26C64010 associated with session (74, 381)

ops$tkyte%ORA11GR2> select username || ' - ' || taddr  data
  2    from v$session
  3   where username = user;

DATA
-----------------------------------------
OPS$TKYTE - 26C64010

ops$tkyte%ORA11GR2> exec p(10);
at the end:
01) transaction 26C64010 associated with session (, )
02) transaction 26C5C11C associated with session (, )
03) transaction 26C5D408 associated with session (, )
04) transaction 26C64664 associated with session (, )
05) transaction 26C5B484 associated with session (, )
06) transaction 26C5CDB4 associated with session (, )
07) transaction 26C5A1A8 associated with session (, )
08) transaction 26C5BAD8 associated with session (, )
09) transaction 26C5C770 associated with session (, )
10) transaction 26C5AE40 associated with session (, )
11) transaction 26C5DA4C associated with session (, )
OPS$TKYTE -

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select 'transaction ' || rawtohex(vt.addr) || ' associated with session (' || vs.sid || ', ' || vs.serial# || ')' data
  2    from v$transaction vt, v$session vs
  3   where rawtohex(vt.addr) = vs.taddr (+);

DATA
-------------------------------------------------------------------------------
transaction 26C64010 associated with session (74, 381)

ops$tkyte%ORA11GR2> select username || ' - ' || taddr  data
  2    from v$session
  3   where username = user;

DATA
-----------------------------------------
OPS$TKYTE - 26C64010

ops$tkyte%ORA11GR2> commit;

Commit complete.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent explanation

Oraboy, December 19, 2011 - 9:40 am UTC

Thanks Tom. Couldn't have asked for a better explanation.

I clearly missed the point, a session could have multiple active transactions. But I am still unclear on how the application code could do this.

To my limited understanding, the application code should grab a connection/session from connection pool and use that pass on its db work & after its done, it releases back to connection pool. Since each unit of work would be capsuled within its transaction boundary, shouldn't the relation between transaction and session be always 1:1?

I can understand "pragma transaction" in plsql..but this application does not use any DB procedure. Your response actually made me more curious , now I want to dig through application code and its transaction management to understand more.

Thanks once again for this invaluable site.

December 19, 2011 - 4:41 pm UTC

XA is magic, it does strange things - really odd things. I don't have the ability to test it - but I'm pretty sure it would be the cause.

.. shouldn't the relation
between transaction and session be always 1:1? ..

not with XA. It in general is many to many.

Autonomous transactions?

Nathan Marston, December 19, 2011 - 9:54 pm UTC

Wouldn't this cause the relationship between transactions and sessions to be many to one also?

December 20, 2011 - 8:01 am UTC

no, it would be one to many. An autonomous transaction belongs to exactly one session. A session can have many autonomous transactions.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Excellent explanation

Autonomous transactions?

More to Explore

PL/SQL demos

PL/SQL docs