10. Managing Transactions

A database transaction is a grouping of SQL statements that make a logical data change to the database. When statements like Cursor.execute() or Cursor.executemany() execute SQL statements like INSERT or UPDATE, a transaction is started or continued. By default, python-oracledb does not commit this transaction to the database. You can explictly commit or roll it back using the methods Connection.commit() and Connection.rollback(). For example to commit a new row:

cursor = connection.cursor()
cursor.execute("insert into mytab (name) values ('John')")
connection.commit()

Transactions are handled at the connection level, meaning changes performed by all cursors obtained from a connection will be committed or rolled back together.

When a database connection is closed, such as with Connection.close(), or when variables referencing the connection go out of scope, any uncommitted transaction will be rolled back.

When Data Definition Language (DDL) statements such as CREATE are executed, Oracle Database will always perform a commit.

This chapter covers python-oracledb’s synchronous programming model. For information on managing transactions in asynchronous connections, see Managing Transactions Using Asynchronous Methods.

10.1. Autocommitting

An alternative way to commit is to set the attribute Connection.autocommit of the connection to True. This ensures all DML statements (INSERT, UPDATE, and so on) are committed as they are executed. Unlike Connection.commit(), this does not require an additional round-trip to the database so it is more efficient when used appropriately.

When executing multiple DML statements that constitute a single transaction, it is recommended to use autocommit mode only for the last DML statement in the sequence of operations.

Warning

Overuse of the mode can impact database performance. It can also destroy relational data consistency when related changes made to multiple tables are committed independently, causing table data to be out of sync.

Note that irrespective of the autocommit value, Oracle Database will always commit an open transaction when a DDL statement is executed.

The example below shows a new customer being added to the table CUST_TABLE. The corresponding SALES table is updated with a purchase of 3000 pens from the customer. The final insert uses autocommit mode to commit both new records:

# Add a new customer
id_var = cursor.var(int)
connection.autocommit = False  # make sure any previous value is off
cursor.execute("""
        INSERT INTO cust_table (name) VALUES ('John')
        RETURNING id INTO :bvid""", bvid=id_var)

# Add sales data for the new customer and commit all new values
id_val = id_var.getvalue()[0]
connection.autocommit = True
cursor.execute("INSERT INTO sales_table VALUES (:bvid, 'pens', 3000)",
        bvid=id_val)

10.2. Explicit Transactions

The method Connection.begin() can be used to explicitly start a local or global transaction.

Without parameters, this explicitly begins a local transaction; otherwise, this explicitly begins a distributed (global) transaction with the given parameters. See the Oracle documentation for more details.

Note that in order to make use of global (distributed) transactions, the attributes Connection.internal_name and Connection.external_name attributes must be set.

10.3. Distributed Transactions

For information on distributed transactions, see the chapter Using Two-Phase Commits (TPC).

10.4. Sessionless Transactions

A Sessionless Transaction is a transaction that can be suspended and resumed during its lifecycle. It breaks the coupling between transactions and connections, that is, a transaction is no longer tied to a specific connection. This enables connections to be released for use by other users while a transaction remains open and can be resumed later. With Sessionless Transactions, you do not need to use a transaction manager since Oracle AI Database manages coordination of transactions.

Sessionless Transactions are supported in both python-oracledb Thin and Thick modes. Oracle AI Database 26ai (or later) is required. For python-oracledb Thick mode, Oracle Client 23.6 (or later) is additionally required.

Each sessionless transaction is identified by a unique transaction identifier. This can either be user-chosen or generated by python-oracledb.

Sessionless Transactions are ideal for interactive applications with user “think time”. If one user starts a database transaction and then does not perform database operations for some time (that is the “think time”), the transaction can be suspended and the database connection can be released and used by another user. When the first user is ready to continue work, a database connection can be obtained and their transaction resumed. Without Sessionless Transactions, both users would need their own connections for the entire duration of their interaction with the system, including during any think time.

With python-oracledb, you can:

  • Start a sessionless transaction on a database connection by specifying a unique transaction identifier

  • Perform database operations in the transaction

  • Suspend the transaction from the connection after the database operations are completed

  • Resume the transaction on the same connection or a different connection using the same transaction identifier

  • Commit or roll back the transaction on the same connection or on a different connection if the transaction has been suspended by the previous connection

You can use Sessionless Transactions on all Oracle Databases including with Oracle Real Application Clusters (RAC). For RAC databases, you can start and suspend a sessionless transaction on one RAC database instance and resume it on another RAC database instance. To commit or rollback a sessionless transaction, it must be active on only one of the RAC instances. If multiple RAC instances have this sessionless transaction active, the database server waits for the DISTRIBUTED_LOCK_TIMEOUT time to allow other instances to suspend this transaction before proceeding with a commit or rollback.

Note that there are some constraints when using Sessionless Transactions. You cannot rollback to a savepoint of the sessionless transaction in a previous connection. Sessionless Transactions cannot be promoted to Distributed Transactions. Session states such as all parameters set by ALTER SESSION, temporary LOB states, and PL/SQL states are not carried over to the new connection. For more information on other constraints, see Restrictions for Sessionless Transactions.

For more information on Sessionless Transactions, see Developing Applications with Sessionless Transactions in the Oracle Database Development Guide.

10.4.1. Starting Sessionless Transactions

To start a sessionless transaction, use Connection.begin_sessionless_transaction(), for example:

txn_id = b"new_sessionless_txn"
connection.begin_sessionless_transaction(transaction_id=txn_id, timeout=120,
    defer_round_trip=False)

You can pass the following parameters to Connection.begin_sessionless_transaction():

  • transaction_id: This parameter is the unique identifier of the transaction which is used to manage the transaction from start to end. If you do not specify the transaction_id value, a unique universally-unique identifier (UUID) is generated and returned by begin_sessionless_transaction(). An example is “36b8f84d-df4e-4d49-b662-bcde71a8764f”.

  • timeout: This parameter is the number of seconds this transaction can stay suspended when Connection.suspend_sessionless_transaction() is later called, or if the transaction is automatically suspended when the suspend_on_success parameter is set to to True in Cursor.execute() or Cursor.executemany(). The default value is 60 seconds. If the transaction is not resumed within the specified duration, the transaction will be rolled back.

  • defer_round_trip: This parameter determines whether the request to start a sessionless transaction should be sent immediately or with the next database operation. The default value is False, that is, the request is sent immediately. When set to True, the request is sent with the next database operation on the connection which reduces the number of round-trips to the database.

Once a transaction has been started, all SQL statements are executed as a part of it.

A sessionless transaction is active from the time it is newly started or resumed to the time it is suspended, committed, or rolled back.

10.4.2. Suspending Sessionless Transactions

After you execute database operations, an active sessionless transaction can be explicitly suspended, or optionally can be automatically suspended on the next database operation if an execute operation completes successfully. This detaches the transaction from the current connection.

Explicitly Suspending Transactions

To explicitly suspend an active transaction, use Connection.suspend_sessionless_transaction():

connection.suspend_sessionless_transaction()

This suspends the active transaction. This transaction is no longer tied to the connection.

Suspending a Transaction After a Database Operation

To automatically suspend an active transaction after the next database operation, set the suspend_on_success parameter to True in Cursor.execute() or Cursor.executemany(). This setting suspends the transaction if the executed statement or PL/SQL block completes successfully. This helps reduce the number of round-trips to the database which in turn improves performance. For example:

# Suspend after execute
cursor.execute(
    "insert into slt_table (name) values ('John')",
    suspend_on_success=True
)

Once the transaction is suspended, further database operations are not part of that transaction until it is resumed.

If the execute operation throws an exception, then the transaction will not be suspended.

If there are no active Sessionless Transactions, this parameter is ignored.

10.4.3. Resuming Sessionless Transactions

To resume a suspended sessionless transaction, use Connection.resume_sessionless_transaction(), for example:

connection.resume_sessionless_transaction(transaction_id=txn_id, timeout=80,
  defer_round_trip=False)

The transaction_id parameter must contain the identifier of an existing transaction.

You can set the following parameters in Connection.resume_sessionless_transaction():

  • timeout: This parameter specifies how long this connection should wait to resume a sessionless transaction if it is currently in use by another connection. In this case, the current connection waits for the transaction to be suspended within this timeout period. If the transaction remains in use by the other connection after the timeout period, the error ORA-25351 is raised. If another connection completes the transaction, the error ORA-24756 is raised. These error messages are only thrown for non-RAC instances. For information on using Oracle RAC, see Sessionless Transactions with Oracle RAC.

  • defer_round_trip: This parameter determines whether the request to resume a sessionless transaction should be sent immediately or with the next database operation. The default value is False, that is, the request is sent immediately. When set to True, the request is sent with the next database operation on the connection which reduce the number of round-trips to the database.

Once resumed, the transaction is considered to be active and database operations are part of that transaction.

10.4.4. Committing or Rolling Back Sessionless Transactions

A new or resumed transaction can be committed using Connection.commit() and rolled back using Connection.rollback().

Once a transaction has been committed or rolled back, it ends, and cannot be resumed, suspended, or used for additional database operations.

10.4.5. Example of Using Sessionless Transactions

An example of using Sessionless Transactions is:

import oracledb

connection1 = oracledb.connect(user="hr", password=userpwd, dsn="localhost/orclpdb")

txn_id = b"sessionless_txnid"
cursor1 = connection1.cursor()

cursor1.execute("create table sessionlessTxnTab (id number, name varchar2(50))")

# Start a new sessionless transaction
connection1.begin_sessionless_transaction(transaction_id=txn_id, timeout=15)

# Execute a database operation
cursor1.execute("insert into sessionlessTxnTab values(1, 'row1')")

# Insert another row
cursor1.execute("insert into sessionlessTxnTab values(2, 'row2')")

# Suspend the sessionless transaction
connection1.suspend_sessionless_transaction()

result = cursor1.execute("select * from sessionlessTxnTab")
rows = result.fetchall()
print(rows)
connection1.close()

In the above sample, the transaction is not committed before being suspended. Hence the inserted data will not be visible and this prints [] as the output.

The transaction, txn_id, has a 15 second timeout in which it needs to be resumed successfully on another connection. In the example below, a different connection resumes the transaction. The same transaction identifier must be used:

# Resume the transaction in another connection
connection2 = oracledb.connect(user="hr", password=userpwd, dsn="localhost/orclpdb")
connection2.resume_sessionless_transaction(transaction_id=txn_id)

cursor2 = connection2.cursor()
cursor2.execute("insert into sessionlessTxnTab values(3, 'row3')")

connection2.commit()

result = cursor2.execute("select * from sessionlessTxnTab")
rows = result.fetchall()
print(rows)

This prints the following output (including the rows inserted in the first code snippet):

[(1, 'row1'), (2, 'row2'), (3, 'row3')]

10.4.6. Viewing Sessionless Transactions

The Oracle Database V$GLOBAL_TRANSACTION view displays information on the currently active transactions on the database server.

To view the active transaction in the current connection, you can use the following query with NVL():

SELECT NVL(dbms_transaction.get_transaction_id, 'NULL transactionId')
FROM dual;

The GET_TRANSACTION_ID Function of the DBMS_TRANSACTION package returns the transaction identifier that is used in the current connection.