In 23ai, we introduced several new technologies related to database transactions, including Priority Transaction which allows prioritization of transactions according to their importance , Lock-Free Column Reservations to reduce hotspots on data that is frequently updated, and Value-Based Concurrency Control designed for stateless applications using ETAGs.  If you are interested in learning more about these features, have a look at the following videos and postings: Priority Transaction (YouTube, blog),  Lock-Free Column Reservations (YouTube, blog), and Value-Based Concurrency Control with ETAGs (YouTube, blog).

Additionally, there are even more enhancements concerning transactions in 23ai. During a discussion with one of my colleagues about the release update features in 23ai, we both concluded that the new functionality known as Sessionless Transaction is “game-changing” when it comes to database transactions. With Oracle database release update 23.6, it is accessible in the FREE editions, Exadata, ODA, or through Oracle Cloud.

Let me describe it briefly: As you may know, database sessions or connections can only be released once the transaction is complete. By using Sessionless Transactions, you can suspend and resume a transaction at any point after starting it. This allows the session or connection to be released back to the pool, making it available for other transactions to use. As a result, you can effectively manage multiple transactions and sessions/connections simultaneously.   

How can you use it? Sessionless Transactions on the server can be used with the additional functions of the PL/SQL package DBMS_TRANSACTION and on the client with APIs, such as Oracle Call Interface (OCI) or Oracle JDBC (see also “Further Reading” section below).

A very interesting use case provides Oracle REST Data Services (ORDS). As you may know ORDS exposes REST APIs on top of the Oracle Database. It uses stateless requests, i.e. complete independent requests are sent which include all the data needed to fulfil the requests. In this case you need to provide a complete transaction on every request. Now with the Sessionless Transaction database feature, you can have transactions across multiple HTTPs calls. You may read Jeff Smith’s excellent blog posting on Sessionless Transactions in Database 23ai & your REST APIs to get an idea how to use it with ORDS. 

How does Sessionless Transactions works?
It enables users to:

  • start a transaction on a database session by providing a unique transaction identifier, submit a unit of work and suspend the transaction
  • and continue the same transaction on another session using the same transaction identifier.

In the end, the same transaction can be finished with COMMIT or ROLLBACK from yet another session. 
 
In this posting I’d like to explain the basics with the PL/SQL API DBMS_TRANSACTION and illustrate how to start, suspend, and resume a Sessionless Transaction. In our simple example we will use 2 sessions and a database user such as SCOTT to insert and update and commit a row in a Sessionless Transaction.

In Session 1: 
Connect to user SCOTT and create a table TESTTR.

SQL> connect scott/tiger@<servicename>
Connected.

-- check the environment
SQL> select sys_context('USERENV','SESSION_USER') who, 
            sys_context('USERENV','CON_NAME') PDB,
            sys_context('USERENV','SID') id;
WHO        PDB        ID
---------- ---------- ----------
SCOTT      WKAB       7637

SQL> create table TESTTR (t number);
Table created.

Before we start a Sessionless Transaction, we verify the transaction type with DBMS_TRANSACTION.GET_TYPE. The result could be TRANSACTION_TYPE_LOCAL, TRANSACTION_TYPE_SESSIONLESS, TRANSACTION_TYPE_XA, or NULL (if no transaction is active). We use a script called gettrans.sql to check the type of transaction in between.

SQL> start gettrans.sql
SQL> set serveroutput on
SQL> begin
     if DBMS_TRANSACTION.GET_TRANSACTION_TYPE() = DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
      then
        dbms_output.put_line('This is a sessionless transaction with GTRID: '||                
                                UTL_RAW.CAST_TO_VARCHAR2(DBMS_TRANSACTION.GET_TRANSACTION_ID()));
      else
        dbms_output.put_line('This is NOT a sessionless transaction');
      end if;
     end;
    /
This is NOT a sessionless transaction
PL/SQL procedure successfully completed.

Every Sessionless Transaction is identified by a unique transaction identifier called Global Transaction ID (GTRID), so that the client driver or the database server can perform the right action. You can either provide your desired GTRID or let Oracle generate a GTRID to identify the Sessionless Transaction to be started.

We will provide a unique identifier for the transaction. For this, we use the function CAST_TO_RAW to convert our user defined identifier string ‘my_test1’  into a RAW value. In addition, we specify a timeout value here 2000, which defines the duration in seconds this transaction can be resumed after it is suspended. 

Now we start a new Sessionless Transaction with DBMS_TRANSACTION.START_TRANSACTION.

SQL> set serveroutput on
SQL> declare
  2    gtrid VARCHAR2(128);
  3  begin
  4    gtrid := DBMS_TRANSACTION.START_TRANSACTION
  5    ( XID              => UTL_RAW.CAST_TO_RAW('my_test1')
  6    , transaction_type => DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
  7    , timeout          => 2000 
  8    , flag             => DBMS_TRANSACTION.TRANSACTION_NEW
  9    );
 10   dbms_output.put_line('GTRID is: ' || gtrid);
 11  end;
 12  /

GTRID is: 6D795F7465737431
PL/SQL procedure successfully completed.

Check the transaction again. The transaction is sessionless.

SQL> start gettrans.sql
This is a sessionless transaction with GTRID: my_test1
PL/SQL procedure successfully completed.

Insert one row and query our table TESTTR.

SQL> insert into testtr values (1);
1 row created.
SQL> select * from testtr;
         T
----------
         1

Suspend the session with the following command.

SQL> execute DBMS_TRANSACTION.SUSPEND_TRANSACTION;

Let’s query our table again. 

SQL> select * from testtr;
no rows selected

In Session 2:

Start a transaction as user SCOTT in another session and continue to associate the transaction with the same session and instance until the transaction is finalized with COMMIT. First check the session and the table content.

SQL> select sys_context('USERENV','SESSION_USER') who, 
            sys_context('USERENV','CON_NAME') PDB,  
            sys_context('USERENV','SID') id;

WHO        PDB        ID
---------- ---------- ----------
SCOTT      WKAB       11259

SQL> select * from testtr;
no rows selected

SQL> start gettrans.sql;
This is NOT a sessionless transaction
PL/SQL procedure successfully completed.

Because of the timeout value 2000 we still have time left to resume the transaction. What would happen if you cannot meet the timeout duration to resume the transaction after it is supended? In this case you will get the following error:

DECLARE
*
ERROR at line 1:
ORA-26218: sessionless transaction with GTRID 6D795F7465737431 does not exist.
ORA-06512: at "SYS.DBMS_TRANSACTION", line 299

Let’s resume the session with the following command. We use the same function START_TRANSACTION with the same unique transaction identifier but with the flag value DBMS_TRANSACTION.TRANSACTION_RESUME.

SQL> set serveroutput on
SQL> declare
  2      gtrid VARCHAR2(128);
  3  begin
  4      gtrid := DBMS_TRANSACTION.START_TRANSACTION
  5      ( xid              => UTL_RAW.CAST_TO_RAW('my_test1')
  6      , transaction_type => DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
  7      , flag             => DBMS_TRANSACTION.TRANSACTION_RESUME
  8      );
  9  dbms_output.put_line('Resumed GTRID: '||gtrid);
 10  end;
 11  /
Resumed GTRID: 6D795F7465737431
PL/SQL procedure successfully completed.

-- check the transaction
SQL> start gettrans.sql
This is a sessionless transaction with GTRID: my_test1
PL/SQL procedure successfully completed.

And query and update the table…

SQL> select * from testtr;
T
----------          
1 

SQL> update testtr set t=2; 
1 row updated. 

SQL> select * from testtr;          
T 
----------          
2

You can finish the transaction now. We decide to execute a COMMIT.

SQL> commit;
Commit complete

-- check the transaction
SQL> start gettrans
This is NOT a sessionless transaction
PL/SQL procedure successfully completed.

Summary and Additional Considerations

Sessionless Transaction is a feature for managing transactions efficiently in a database application. It provides you with the flexibility to suspend and resume the transaction during its life cycle. The Sessionless Transaction feature provides applications with a native mechanism to commit or roll back a transaction and breaks the coupling between the transaction and the session. Once you start a transaction, it does not need to be tied to a session or connection. You can free the session or connection, allowing it to be used by another client. Therefore, there is no risk of in-doubt transactions and no need for any recovery mechanism.

When you use Sessionless Transactions, you do not need to use a transaction manager when communicating with the Oracle Database (single or multi-instance). You can use Sessionless Transactions on a RAC or a non-RAC deployment. The database does all the work of coordinating the transaction for you. Moreover, the database internally coordinates the two-phase commit (2PC) protocol, without the need for any application-side logic.

How can you use it? A very interesting scenario is in conjunction with Oracle REST Data Services (ORDS) as we already mentioned in the introduction. With the Sessionless Transaction database feature, you have the ability to have transactions across multiple HTTPs calls. For example you can use DBMS_TRANSACTION for developing your POST handler to take advantage in this feature. Jeff Smith demonstrates this with a very comprehensible example in his posting here.

But that’s not all. You can work with Sessionless Transactions not just on the server side, but on the client side too, using APIs like Oracle Call Interface (OCI) or Oracle JDBC. So, whether you’re coding on the server or building applications on the client side, you can still keep your transactions smooth and seamless. 

Further Readings