Sessionless Transactions in Database 23ai(23.6)

In Oracle database, transactions traditionally depend on sessions. When a session is closed, the associated transaction is terminated. However, starting with Oracle 23ai(23.6), the Sessionless Transactions feature allows transactions to be suspended and resumed in different sessions. This feature effectively removes the dependency between transactions and sessions.

In other words, With Sessionless Transactions, each transaction is assigned a unique identifier. Even if a session is terminated, the transaction persists in the database. Another session can then resume and continue the transaction using its unique identifier.

To utilize this feature, it is essential to be familiar with the DBMS_TRANSACTION.START_TRANSACTION function. This function has the following parameters:

DBMS_TRANSACTION.START_TRANSACTION(
   xid              in raw default null,
   transaction_type in pls_integer default TRANSACTION_TYPE_LOCAL,
   timeout          in pls_integer default 60,
   flag             in pls_integer default 0
) RETURN VARCHAR2;

· XID: The name of the transaction.

· TRANSACTION_TYPE: The type of transaction, such as TRANSACTION_TYPE_LOCAL, TRANSACTION_TYPE_SESSIONLESS, or TRANSACTION_TYPE_XA.

· TIMEOUT: Specifies the duration (in seconds) that the transaction can be resumed after suspension.

· Flag: The flag can be either TRANSACTION_NEW for starting a new transaction or TRANSACTION_RESUME for resuming a suspended transaction.

Demonstration

Step 1: Create a Table

First, create a table for demonstration purposes:

SQL> create table tbl_sessionless (id number,First_name varchar2(20),last_name varchar2(30));
Table created.

Step 2: Start a Sessionless Transaction
In a new session, start a sessionless transaction and insert a row into the table. Then suspend the transaction(Arsenal01) and close the session:

Session 1:

SQL> select sid from v$mystat where rownum=1;
       SID
----------
       138

SQL> set serveroutput on
SQL> declare
  TRANID VARCHAR2(128);
begin
  TRANID := DBMS_TRANSACTION.START_TRANSACTION
  ( XID              => UTL_RAW.CAST_TO_RAW('Arsenal01')
  , transaction_type => DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
  , timeout          => 5
  , flag             => DBMS_TRANSACTION.TRANSACTION_NEW
  );
 dbms_output.put_line('TRANID is: ' || TRANID);
end;
/
TRANID is: 417273656E616C3031
PL/SQL procedure successfully completed.

SQL> insert into tbl_sessionless values(1,'Vahid','Yousefzadeh');
1 row created.

SQL> select * from tbl_sessionless;
        ID FIRST_NAME           LAST_NAME
---------- -------------------- ------------------------------
         1 Vahid                Yousefzadeh

SQL> execute DBMS_TRANSACTION.SUSPEND_TRANSACTION;
PL/SQL procedure successfully completed.

SQL> exit

Step 3: Resume the Transaction in Another Session
Start another session and resume the transaction(Arsenal01):

Session 2:

SQL> select sid from v$mystat where rownum=1;
       SID
----------
       507

SQL> select * from tbl_sessionless;
no rows selected

SQL> set serveroutput on
SQL> declare
    TRANID VARCHAR2(128);
begin
    TRANID := DBMS_TRANSACTION.START_TRANSACTION
    ( xid              => UTL_RAW.CAST_TO_RAW('Arsenal01')
    , transaction_type => DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
    , flag             => DBMS_TRANSACTION.TRANSACTION_RESUME
    );
dbms_output.put_line('Resumed TRANID: '||TRANID);
end;
/
Resumed TRANID: 417273656E616C3031
PL/SQL procedure successfully completed.

SQL> select * from tbl_sessionless;
        ID FIRST_NAME           LAST_NAME
---------- -------------------- ------------------------------
         1 Vahid                Yousefzadeh

The query now returns the inserted row. The transaction(Arsenal01) is active in the new session, as shown by querying v$transaction:

SQL> select s.sid,s.TADDR,s.PROGRAM from v$transaction t,v$session s where t.ADDR=s.TADDR;
       SID TADDR            PROGRAM
---------- ---------------- ------------------------------
       507 00000000CB5651E8 sqlplus@OEL9 (TNS V1-V3)

Step 4: Commit the Transaction
Finally, commit the transaction(Arsenal01) to terminate it:

SQL> commit;
Commit complete.

SQL> select * from tbl_sessionless;
        ID FIRST_NAME           LAST_NAME
---------- -------------------- ------------------------------
         1 Vahid                Yousefzadeh

After committing, attempting to resume the transaction will result in an error:

SQL> set serveroutput on
SQL> declare
    TRANID VARCHAR2(128);
begin
    TRANID := DBMS_TRANSACTION.START_TRANSACTION
    ( xid              => UTL_RAW.CAST_TO_RAW('Arsenal01')
    , transaction_type => DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
    , flag             => DBMS_TRANSACTION.TRANSACTION_RESUME
    );
dbms_output.put_line('Resumed TRANID: '||TRANID);
end;
/
ORA-26218: sessionless transaction with GTRID 417273656E616C3031 does not
exist.
ORA-06512: at "SYS.DBMS_TRANSACTION", line 299
ORA-06512: at line 4
Help: https://docs.oracle.com/error-help/db/ora-26218/