berxblog: März 2025

Sessionless Transactions are quite new to Oracle.

Some might argue this is not true: XA transactions are available since Oracle 7.

The new Sessionless Transactions seem to be quite similar to global transactions - just without any central lock manager.

Let's have a look how these new transactions can be observed during their lifetime.

I have a small setup of 4 sessions, the last one doesn't do a lot beside observing:


select inst_id, sid, serial#, STATUS, module, SADDR, blocking_instance, blocking_session, final_blocking_instance, final_blocking_session  
from gv$session
where module like 'Session %'
order by 5;

   INST_ID        SID    SERIAL# STATUS   MODULE         SADDR            BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION
---------- ---------- ---------- -------- -------------- ---------------- ----------------- ---------------- ----------------------- ----------------------
         5      48093      13343 INACTIVE Session A      000000115E3C4D90                                                                                  
         5      49891      61877 INACTIVE Session B      00000011463D1B18                                                                                  
         5      50497      51425 INACTIVE Session C      000000113E278D10                                                                                  
         5      43280      24898 ACTIVE   Session D      0000001139BCDBF0

Now in Session A, the first transaction starts:


--SET APPINFO 'Session A'
set serveroutput on;
DECLARE
    gtrid VARCHAR2(128);
BEGIN
    gtrid := DBMS_TRANSACTION.START_TRANSACTION
      ( UTL_RAW.CAST_TO_RAW('Transaction Insert 1') 
      , DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
      , 2000 
      , DBMS_TRANSACTION.TRANSACTION_NEW
      );
   dbms_output.put_line(gtrid);
END;
/

5472616E73616374696F6E20496E736572742031


insert into sessionless (id) values (1);

1 row inserted.

and the observer sees:


  INST_ID        SID    SERIAL# STATUS   MODULE         SADDR            BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION
---------- ---------- ---------- -------- -------------- ---------------- ----------------- ---------------- ----------------------- ----------------------
         5      48093      13343 INACTIVE Session A      000000115E3C4D90                                                                                  
         5      49891      61877 INACTIVE Session B      00000011463D1B18                                                                                  
         5      50497      51425 INACTIVE Session C      000000113E278D10                                                                                  
         5      43280      24898 ACTIVE   Session D      0000001139BCDBF0                                                                                  


select s.inst_id, s.sid, s.module, START_DATE, START_SCN , XID ,t.SES_ADDR --, t.*
from v$transaction t, gv$session s
where t.SES_ADDR = s.saddr (+);

   INST_ID        SID MODULE         START_DATE                START_SCN XID              SES_ADDR        
---------- ---------- -------------- ------------------- --------------- ---------------- ----------------
         5      48093 Session A      2025-03-05 13:03:08  44419149767413 09002000BA170000 000000115E3C4D90
         5      54695 SQL Developer  2025-03-05 08:51:39  44419144472312 0A000E00E9680000 000000116A24BC78

To make things more interesting (and observable) in Session B another transaction starts:


SET APPINFO 'Session B'
insert into sessionless (id) values (1);

and it hangs due to the PK on sessionless.id:

  
   INST_ID        SID    SERIAL# STATUS   MODULE         SADDR            BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION
---------- ---------- ---------- -------- -------------- ---------------- ----------------- ---------------- ----------------------- ----------------------
         5      48093      13343 INACTIVE Session A      000000115E3C4D90                                                                                  
         5      49891      61877 ACTIVE   Session B      00000011463D1B18                 5            48093                       5                  48093
         5      50497      51425 INACTIVE Session C      000000113E278D10                                                                                  
         5      43280      24898 ACTIVE   Session D      0000001139BCDBF0                                                                                  
         
   INST_ID        SID MODULE         START_DATE                START_SCN XID              SES_ADDR        
---------- ---------- -------------- ------------------- --------------- ---------------- ----------------
         5      48093 Session A      2025-03-05 13:03:08  44419149767413 09002000BA170000 000000115E3C4D90
         5      49891 Session B      2025-03-05 13:04:04  44419149780350 08000900AC0D0000 00000011463D1B18
         5      54695 SQL Developer  2025-03-05 08:51:39  44419144472312 0A000E00E9680000 000000116A24BC78


select sql_exec_start, event,  BLOCKING_SESSION_STATUS, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, BLOCKING_INST_ID, min(sample_time)mist , max(sample_time) mast
from v$active_session_history ash 
where session_id = 49891
  and sample_time > '2025-03-05 12:27:56.885000000' -- previous tests
group by  sql_exec_start, event,  BLOCKING_SESSION_STATUS, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, BLOCKING_INST_ID;

SQL_EXEC_START      EVENT                          BLOCKING_SE BLOCKING_SESSION BLOCKING_SESSION_SERIAL# BLOCKING_INST_ID MIST                          MAST                         
------------------- ------------------------------ ----------- ---------------- ------------------------ ---------------- ----------------------------- -----------------------------
2025-03-05 13:04:04 enq: TX - row lock contention  VALID                  48093                    13343                1 2025-03-05 13:04:05.645000000 2025-03-05 13:05:02.696000000

Note the wrong BLOCKING_INST_ID? It should be 5, not 1!

Now the tricky part begins:
Session A gets rid of its transaction:

  
exec    DBMS_TRANSACTION.SUSPEND_TRANSACTION;

PL/SQL procedure successfully completed.

with slightly changes:

 
SQL_EXEC_START      EVENT                          BLOCKING_SE BLOCKING_SESSION BLOCKING_SESSION_SERIAL# BLOCKING_INST_ID MIST                          MAST                         
------------------- ------------------------------ ----------- ---------------- ------------------------ ---------------- ----------------------------- -----------------------------
2025-03-05 13:04:04 enq: TX - row lock contention  GLOBAL                                                                 2025-03-05 13:05:22.014000000 2025-03-05 13:06:37.285000000
2025-03-05 13:04:04 enq: TX - row lock contention  VALID                  48093                    13343                5 2025-03-05 13:05:19.980000000 2025-03-05 13:05:20.998000000
2025-03-05 13:04:04 enq: TX - row lock contention  VALID                  48093                    13343                1 2025-03-05 13:04:05.645000000 2025-03-05 13:05:18.962000000

And the blocking Session changed. After some time, the INST_ID got fixed. but more important, BLOCKING_SESSION_STATUS is GLOBAL now. Do you remember the similarities?

When Session A disconnects:

 
   INST_ID        SID    SERIAL# STATUS   MODULE         SADDR            BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION
---------- ---------- ---------- -------- -------------- ---------------- ----------------- ---------------- ----------------------- ----------------------
         5      49891      61877 ACTIVE   Session B      00000011463D1B18                                                                                  
         5      50497      51425 INACTIVE Session C      000000113E278D10                                                                                  
         5      43280      24898 ACTIVE   Session D      0000001139BCDBF0                                                                                  


   INST_ID        SID MODULE         START_DATE                START_SCN XID              SES_ADDR        
---------- ---------- -------------- ------------------- --------------- ---------------- ----------------
         5      43280 Session D      2025-03-05 13:08:38  44419149859826 07001E00290C0000 0000001139BCDBF0
         5      49891 Session B      2025-03-05 13:04:04  44419149780350 08000900AC0D0000 00000011463D1B18
         5      54695 SQL Developer  2025-03-05 08:51:39  44419144472312 0A000E00E9680000 000000116A24BC78
                                     2025-03-05 13:03:08  44419149767413 09002000BA170000 000000115E3C4D90
                                     
SQL_EXEC_START      EVENT                          BLOCKING_SE BLOCKING_SESSION BLOCKING_SESSION_SERIAL# BLOCKING_INST_ID MIST                          MAST                         
------------------- ------------------------------ ----------- ---------------- ------------------------ ---------------- ----------------------------- -----------------------------
2025-03-05 13:04:04 enq: TX - row lock contention  GLOBAL                                                                 2025-03-05 13:05:22.014000000 2025-03-05 13:09:03.785000000
2025-03-05 13:04:04 enq: TX - row lock contention  VALID                  48093                    13343                5 2025-03-05 13:05:19.980000000 2025-03-05 13:05:20.998000000
2025-03-05 13:04:04 enq: TX - row lock contention  VALID                  48093                    13343                1 2025-03-05 13:04:05.645000000 2025-03-05 13:05:18.962000000

we see v$transaction has an orphaned SADDR.

Now Session C picks up:

 
  set serveroutput on;
DECLARE
    gtrid VARCHAR2(128);
BEGIN
    gtrid := DBMS_TRANSACTION.START_TRANSACTION
         (UTL_RAW.CAST_TO_RAW('Transaction Insert 1') 
        , DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
        , 2000
        , DBMS_TRANSACTION.TRANSACTION_RESUME);
END;
/

PL/SQL procedure successfully completed.

which leads to

   
   INST_ID        SID MODULE         START_DATE                START_SCN XID              SES_ADDR        
---------- ---------- -------------- ------------------- --------------- ---------------- ----------------
         5      43280 Session D      2025-03-05 13:08:38  44419149859826 07001E00290C0000 0000001139BCDBF0
         5      49891 Session B      2025-03-05 13:04:04  44419149780350 08000900AC0D0000 00000011463D1B18
         5      54695 SQL Developer  2025-03-05 08:51:39  44419144472312 0A000E00E9680000 000000116A24BC78
                                     2025-03-05 13:03:08  44419149767413 09002000BA170000 000000115E3C4D90  

SQL_EXEC_START      EVENT                          BLOCKING_SE BLOCKING_SESSION BLOCKING_SESSION_SERIAL# BLOCKING_INST_ID MIST                          MAST                         
------------------- ------------------------------ ----------- ---------------- ------------------------ ---------------- ----------------------------- -----------------------------
2025-03-05 13:04:04 enq: TX - row lock contention  VALID                  50497                    51425                1 2025-03-05 13:09:51.587000000 2025-03-05 13:11:19.137000000
2025-03-05 13:04:04 enq: TX - row lock contention  GLOBAL                                                                 2025-03-05 13:05:22.014000000 2025-03-05 13:09:50.568000000
2025-03-05 13:04:04 enq: TX - row lock contention  VALID                  48093                    13343                5 2025-03-05 13:05:19.980000000 2025-03-05 13:05:20.998000000
2025-03-05 13:04:04 enq: TX - row lock contention  VALID                  48093                    13343                1 2025-03-05 13:04:05.645000000 2025-03-05 13:05:18.962000000

we see v$transaction still does not know about Session B now holding 09002000BA170000 . But at the same time, ASH shows multiple lines of (the same) blocking status. Of course, to release all the tension, Session B does a simple rollback - and everything is fine.

berxblog

2025-03-05

Sessionless Transactions - the moving target

Follower

Über mich