Overview
In Oracle database, transactions are usually strongly coupled with sessions. When a transaction is in progress, the session and connection cannot be released back to the pool, even if the application, or mid-tier, is not performing database related work. While this may simply be a case of a client application needing time to perform an operation, it can lead to poor session and connection utilization. In cases where there are many client applications, the database instance could run out of sessions and/or connections.
A better way to utilize connections and sessions, is to be able to get a session from the pool, perform database related work, and release the session back to the pool. Later, when the application or mid-tier would like to submit some more statements, it can request a session, perform database related work, and finally commit the transaction.
For even better utilization, applications should be agnostic to whether they picked the very same session from the pool or a new one. Before Oracle Database 23ai, this required applications to use XA transaction paradigm and incorporate an external Transaction Manager (TM) to coordinate the XA transaction. This complicates the application pipeline, and moreover does not guarantee any better outcomes since the TM itself is a stateful service that is prone to failures. For example, if the TM fails, locks are held in the database, leading to cascading hangs and other failures.
Introducing Sessionless Transactions
Sessionless Transactions, a feature introduced in Oracle Database 23ai, eliminates the need for an external TM, or the requirement to coordinate commit and recovery of transactions when communicating with the same database (RAC or non-RAC), while allowing applications to achieve better session and connection utilization.
The Sessionless Transactions feature enables users to start a transaction on a database session by providing a unique transaction identifier, submit a unit of work, suspend the transaction, and continue the same transaction on another session using the same transaction identifier. In the end, the same transaction can be committed from yet another session.
The Sessionless Transactions feature provides applications with a native mechanism to commit, or roll back a transaction without employing an external transaction manager to coordinate the XA protocol. The transaction commit is coordinated internally by the database to ensure data integrity across multiple database instances or sessions.
For example, using Sessionless Transactions, a transaction is started in Session 1, continued in Session 2, and finally committed from Session 3. Here is the logical representation:
Session 1
– Start transaction // Returns a transaction identifier
– Insert row (‘Smith’, 800)
– Suspend the transaction
– Release session
Session 2
– Resume the transaction using the transaction identifier
– Insert row (‘Allen’, 1600)
– Suspend the transaction
– Release session
Session 3
– Resume the transaction using the transaction identifier
– Select inserted rows // Returns rows for Smith and Allen
– Commit
Since the commit protocol is coordinated by the Oracle database, the application/client only has to issue a simple commit. The COMMIT can be issued from any connection/session.
You can use Sessionless Transactions on a RAC, or a non-RAC deployment. If it is a RAC deployment, a Sessionless transaction, whenever resumed, could use a session on different instances. On a non-RAC deployment, you have a single instance, and a Sessionless transaction could use different sessions on the same database instance when it is resumed.
For those of you who are familiar with distributed transactions and XA transactions, Sessionless Transactions never go “in-doubt” or into a “pending” state that requires a heuristic commit or abort, or requires the RECO background process to perform distributed transaction recovery.
Generally, any interactive application that has think time, and computation logic, will benefit from Sessionless Transactions.
Usage Example: Starting, suspending, and resuming Sessionless Transactions
For JDBC clients:
– To start a new Sessionless transaction, execute startTransaction() which returns a Global transaction identifier (GTRID).
– An application can optionally accept a global transaction identifier (GTRID) as input, and a timeout that is used to automatically rollback a suspended transaction.
– For suspending a Sessionless transaction, you can use suspendTransaction() which is sent to the server with the next round-trip and suspendTransactionImmediately() which is sent to the server immediately, as if it were a standalone statement.
– For resuming a suspended Sessionless transaction, we provide a convenient API resumeTransaction() which accepts the GTRID as input.
In the example below, the ORDERS table has 4 columns, all are NUMBER columns: (ID, PRODUCT_ID, QUANTITY, TOTAL_PRICE).
Perform DML by starting a Sessionless transaction:
final byte[] gtrid = oc.startTransaction();
try(PreparedStatement p = c.prepareStatement("insert into orders(product_id,quantity,total_price) values(?,?,?)"))
{
p.setInt(1,1);
p.setInt(2,3);
p.setBigDecimal(3,new BigDecimal(19.90 ).multiply( new BigDecimal( 3 )));
p.executeUpdate();
}
oc.suspendTransactionImmediately();
}
To commit:
System.out.print("Please provide a global transaction ID: ");
System.out.flush();
byte[] buffer = System.in.readNBytes(32);
try (Connection c = Oracle.dataSource.getConnection()) {
c.setAutoCommit(false);
final OracleConnection oc = c.unwrap(OracleConnection.class);
final byte[] gtrid = HexFormat.of().parseHex(new String(buffer));
oc.resumeTransaction(gtrid);
c.commit();
System.out.println("Transaction committed!");
}
For detailed usage syntax and examples, please see the Oracle documentation on Sessionless Transactions here (Using Sessionless Transactions)
Feature Benefits
The following are benefits of using Sessionless Transactions in your applications.
– No Need to Setup and Manage a (stateful) TM Infrastructure
With XA transactions, resumption of the transaction may create several ‘branches’and applications are required to manage state of each branch and coordinate a two-phase commit protocol. Applications often use TMs to perform these complicated state tracking operations, but TMs are difficult to setup and are yet another stateful component in the infrastructure.
With Sessionless Transactions, applications can offload state management and commit protocol coordination to the Database. Moreover, since Sessionless Transactions act on a single Database, Sessionless Transactions cannot go in-doubt, and require no additional interventions from the application than submitting a commit or a rollback statement.
– Performance Improvements at DML Time
XA transactions implement APIs for suspending and resuming a transaction. Sessionless Transactions uplevel this functionality by allowing applications to piggyback suspend and resume actions with other database related work.
For instance, applications can submit a DML statement with a flag that indicates that the transaction must be suspended after the DML statement is complete on the server, which reduces the overall number of roundtrips to the database for executing a statement in a Sessionless transaction, improving observed performance by the application as well as network usage.
– Performance Improvements at Commit Time
In addition to simplifying state management, offloading the coordination of commit protocol to the database also improves the commit performance of Sessionless Transactions as compared to XA. XA transactions require one round-trip per XA branch each during the Prepare phase and Commit phase, which can be improved to one round-trip for each XA branch during the Prepare phase and a single round-trip for the Commit phase, if the TM is aware that the server is an Oracle RAC database instance.
With Sessionless Transactions, the application only needs to submit a commit statement to the server i.e. a single round-trip, while all the intermediate steps of the commit protocol are performed using high-performance inter-instance communication by the server. In cases where the client and server are not collocated, this can tremendously improve performance and lower network usage.
– Transaction Can Be Resumed across RAC Database Instances
Unlike the XA protocol, a Sessionless transaction can be started and suspended on one RAC database instance and resumed on another RAC database instance. An application does not need to know about which RAC database instance the transaction uses.
In Oracle XA, to suspend and resume an XA transaction on different RAC database instances, the external transaction managers must have the knowledge of each RAC database instance, create at least one branch per instance touched by the XA transaction, and manage the 2PC of these branches. The XA driver cannot suspend an XA transaction on one instance and resume it on another. The users are forced to create branches on a new instance to continue the work under a given XA transaction.
– Database-Managed Recovery
Oracle Database is responsible for transaction recovery and commit operations for Sessionless Transactions. There is no transaction management required by the client application, or an external transaction manager. Transactions also never go in-doubt. Transaction recovery often happens automatically for a suspended Sessionless transaction, if it remains suspended for a longer duration than the timeout value used for the transaction.
Restrictions and Best Practices
The following are restrictions when using Sessionless Transactions.
– Promoting a Sessionless Transaction to an XA Transaction Is Not Supported in 23.6 Release
A local transaction can be promoted to an XA transaction. When that happens, the original local transaction becomes a transaction branch of the XA transaction. However, a Sessionless transaction cannot be promoted to an XA transaction. This functionality is planned for a future release.
– Rollback to Savepoint Is Not Supported Completely in 23.6 Release
Rollback to savepoint does not fully work with Sessionless Transactions. If a Sessionless transaction savepoint is created in one session and the transaction is later resumed in another session, the savepoint cannot be used, and an ORA-1086 error is raised. This functionality is planned for a future release.
– Session States Are Not Carried Across Sessions by Sessionless Transactions in 23.6 Release
When resuming a Sessionless transaction in a session that is different from the session the transaction was last suspended from, the session states (such as all parameters set by ALTER SESSION, Temp LOB states, and PL/SQL states) are not carried over to the new session. The application must ensure that it re-establishes the same session states to retrieve the same results (such as NLS settings). We plan to provide an option to re-establish simple session state automatically in a future release.
– Insert Direct Load, Online Direct Load, and Parallel DML Are Not Supported in 23.6 Release
The following are best practices for Sessionless Transactions.
– Create session pools to facilitate request and release of sessions when a Sessionless transaction is ready to be resumed or suspended
– Divide application tasks into serialized units of work such that each unit of work requires no think time, and the application performs
logical/compute-intensive operations after finishing a unit of work and suspending the Sessionless transaction
– Use UUID as GTRID to reduce the probability of conflicts
More information
For more information, please see the following Oracle documentation:
Developing Applications with Sessionless Transactions
Session Pooling and Connection Pooling
Thanks to the valuable guidance, and contributions from contributing authors: Sukhada Pendse, Natesh Kedlaya, Ajit Mylavarapu, Swamy Prasanna Siddeshwara, Sven Hwang, Loic Lefevre and Todd Little