Overview

We will demonstrate the use of Oracle’s sessionless transaction feature in the Oracle JDBC thin driver. Using a flight booking REST API example, we’ll show the benefits of using sessionless transactions compared to other alternatives.
More information about sessionless transactions can be found in https://docs.oracle.com/en/database/oracle/oracle-database/23/jjdbc/sessionless-transactions.html.

Database Schema

• Flights:
  • Stores flights details, each with a fixed price.
• Seats:
  • To keep the schema simple the seats table is linked directly to flights rather than a planes table.
  • Once booked, a seat is marked as unavailable.
• Bookings and Tickets:
  • A user can book multiple tickets in one booking (i.e transaction).
  • Each ticket references the booking and the reserved seat.
• Receipts:
  • After confirmation, the booking links to a receipt with payment details.
• Payment_methods:
  • Payments should be handled by a different service, the Payment_methods can be a reference to the user’s debit card or any alternative payment methods.

Requirements

The booking system must implement these transaction behaviors:

• Users can add seats to a temporary reservation cart.
• Seats are exclusively locked when added to a cart. This prevents other transactions from modifying or reserving the same seat until the booking is confirmed or cancelled.
• Purchase must be cancelled if:
  • User explicitly cancels
  • A timeout is reached

Diagram below demonstrates the process to implement:

Design options

Before investigating the use of sessionless transactions to implement our application let’s examine traditional approaches and their limitations.

Option 1: Standard database transaction

Key Characteristic:

The entire cart state is managed within the database transaction – no external session storage.

Implementation Details:

1. Transaction Start:

A booking record represents the cart

INSERT INTO bookings (created_at) VALUES (SYSTIMESTAMP) RETURNING ID INTO ?

 2. Seat Selection with Exclusive Locking:

Find available seats while preventing other transactions from accessing them.
SKIP LOCKED avoids waiting for locked rows.

CREATE OR REPLACE PROCEDURE fetch_seats(
      f_id IN NUMBER,
      n_rows IN INT,
      t_data OUT DBMS_SQL.NUMBER_TABLE
    ) AS
      CURSOR c IS
        SELECT id FROM seats
        WHERE available = true AND flight_id = f_id
        FOR UPDATE SKIP LOCKED;
    BEGIN
        OPEN c;
        FETCH c BULK COLLECT INTO t_data LIMIT n_rows;
        CLOSE c;
    END fetch_seats;

3. Adding items to cart:
Associate selected seats with our booking/cart

INSERT INTO tickets (seat_id, booking_id) VALUES (?, ?)

4. Updating seat availability:

UPDATE seats SET available=FALSE WHERE id = ?

Transaction Management:

• All locks are automatically managed by the database
• Cart state exists purely in the transaction scope
• Commit makes changes permanent, rollback releases everything

Critical Limitations:

1. Connection Pinning:
   • Each active cart holds a database connection open
   • 2,000 concurrent carts → 2,000 active connections
   • Connection pooling becomes ineffective at scale
2. Strict Session Binding
   • Clients are permanently tied to the Java instance that initiated their transaction
   • Load balancers cannot reroute requests without breaking transaction states

Option 2: Saga transactions

This method implements Saga pattern logic in the middle-tier (the java application) – a common microservices solution for distributed transactions. Instead of relying on database transactions, we break the process into compensatable steps:

Implementation Requirements:

1. State Tracking:
   • Middle-tier must persist booking state (e.g., in Redis or a `pending_booking` table)
   • Stores which seats are temporarily reserved
2. Compensation Logic:
   • Every booking action needs a corresponding undo operation.
   • Required for both timeouts and explicit cancellations
3. Timeout Handling:
   • Scheduled jobs/workers must scan for expired reservations.

Key Challenges:

• No atomic guarantees
• requires idempotent operations
• Complex recovery scenarios (e.g., partial failures)

Compared to Option 1’s database transactions, this adds significant complexity but enables better horizontal scaling (no connection pinning).

Sessionless transactions

A better alternative to Options 1 and 2 is Oracle’s sessionless transactions. Sessionless transactions are the same as the typical database transaction but as its name indicates they are not tied to a database session, they can be easily suspended/detached from a database connection and later resumed in an entirely separate connection.

This means we can retain all ACID guarantees while eliminating connection pinning, for example 2000 concurrent carts can share 200 database connections – each cart retains its own transactional state, while connections rotate efficiently.

Key benefits:

• ACID Compliance: Retains ACID properties.
• No Connection Pinning: use of fewer connections than traditional transactions.
• Simpler Than Sagas: No orchestration – state managed by the database.

Now let’s look into how each of the application processes can be implemented using Sessionless transactions with JDBC.

Transaction start

Starts a booking session and reserves initial seats

// Transaction scope with auto-rollback if reservation fails
    try (OracleConnection conn = (OracleConnection) connectionPool.getConnection();  
         AutoCloseable rollback = conn::rollback;) {  // Rollback if reservation fails
      conn.setAutoCommit(false);  
      byte[] gtrid = conn.startTransaction(timeout * 60);  // Global transaction ID
      
      long bookingId = createBooking(conn);  // Initial cart creation
      
      List<Long> seats = lockAndBookSeats(conn, bookingId, flightId, numberOfSeats);
      conn.suspendTransaction(); 
    }
    

Resuming Transaction

Adds more seats to existing booking

// Transaction scope with auto-suspend on completion or failure.
    try (OracleConnection conn = (OracleConnection) connectionPool.getConnection();  
         AutoCloseable suspend = conn::suspendTransaction;) {
      conn.setAutoCommit(false);
      conn.resumeTransaction(transactionId);  // Reattach using stored ID
      
      List<Long> seats = lockAndBookSeats(conn, bookingId, flightId, numberOfSeats);
    } catch (SQLException ex) {  
      if (ex.getErrorCode() == TRANSACTION_NOT_FOUND_ERROR) {  
        throw new TransactionNotFoundException(transactionId);  
      }  
      throw ex;
    } 
    

Final Checkout

Completes payment and persists booking

try (OracleConnection conn = (OracleConnection) connectionPool.getConnection();) {  
      conn.setAutoCommit(false);  
      conn.resumeTransaction(gtrid);
      try {  
        tickets = getTickets(conn, bookingId);  
        sum = tickets.stream().map(TicketDTO::price).reduce(0F, Float::sum);  
        receipt = paymentService.pay(sum, paymentMethodId);  
        saveReceipt(conn, receipt, sum, bookingId, paymentMethodId);  
      } catch (Exception ex) {  
        conn.suspendTransaction();  // Preserve for retry
        throw ex;  
      }  
      conn.commit();  // Complete transaction
    } catch (SQLException ex) {  
      if (ex.getErrorCode() == TRANSACTION_NOT_FOUND_ERROR) {
        throw new TransactionNotFoundException(transactionId);  
      }
      throw ex;  
    }
    

Cancelling a Transaction

Explicitly aborts the booking and releases all locks

try (OracleConnection conn = (OracleConnection) connectionPool.getConnection();) {
      conn.setAutoCommit(false);
      conn.resumeTransaction(transactionId);
      conn.rollback();
    } catch (SQLException ex) {
      if (ex.getErrorCode() == TRANSACTION_NOT_FOUND_ERROR) {
        throw new TransactionNotFoundException(transactionId);  // Already expired
      }
      throw ex;
    }
    

Conclusion

Sessionless transactions allow database transactions to be suspended and resumed across different database sessions. It is a great design option for scalable, ACID-compliant applications combining the reliability of database transactions with the flexibility of stateless architectures.
Our flight booking example demonstrated these capabilities using Oracle JDBC thin driver.
 

References

Demo Source Code

Developing Applications with Sessionless Transactions

JDBC Developer’s Guide