.. _txnmgmnt:

.. currentmodule:: oracledb

*********************
Managing Transactions
*********************

A database transaction is a grouping of SQL statements that make a logical data
change to the database. When statements like :meth:`Cursor.execute()` or
:meth:`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 :meth:`Connection.commit()` and
:meth:`Connection.rollback()`. For example to commit a new row:

.. code-block:: python

    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 :meth:`Connection.close()`,
or when variables referencing the connection go out of scope, any uncommitted
transaction will be rolled back.

When `Data Definition Language (DDL) <https://www.oracle.com/pls/topic/lookup?
ctx=dblatest&id=GUID-FD9A8CB4-6B9A-44E5-B114-EFB8DA76FC88>`__ 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
:ref:`txnasync`.


Autocommitting
==============

An alternative way to commit is to set the attribute
:attr:`Connection.autocommit` of the connection to ``True``.  This ensures all
:ref:`DML <dml>` statements (INSERT, UPDATE, and so on) are committed as they
are executed.  Unlike :meth:`Connection.commit()`, this does not require an
additional :ref:`round-trip <roundtrips>` 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:

.. code-block:: python

    # 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)


Explicit Transactions
=====================

The method :meth:`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 :attr:`Connection.internal_name` and
:attr:`Connection.external_name` attributes must be set.

.. _distributedtxns:

Distributed Transactions
========================

For information on distributed transactions, see the chapter :ref:`tpc`.

.. _sessionlesstxns:

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
:ref:`Thick <enablingthick>` 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

.. _sessionlesstxnswithrac:

You can use Sessionless Transactions on all Oracle Databases including with
`Oracle Real Application Clusters (RAC) <https://www.oracle.com/pls/topic/
lookup?ctx=dblatest&id=GUID-D04AA2A7-2E68-4C5C-BD6E-36C62427B98E>`__. 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
<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-AF535DC1-E45B-
412D-95F2-5B6C1F18415D>`__ 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
:ref:`distributedtxns`. 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 <https://www.oracle.com/pls/topic/
lookup?ctx=dblatest&id=GUID-7F76D67C-4470-4DA3-BAAE-8E243D9FA87B>`__.

For more information on Sessionless Transactions, see `Developing Applications
with Sessionless Transactions <https://www.oracle.com/pls/topic/lookup?ctx=
dblatest&id=GUID-C1F67D04-CE72-416E-8CED-243E5710E83D>`__ in the Oracle
Database Development Guide.

.. _starttxns:

Starting Sessionless Transactions
---------------------------------

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

.. code-block:: python

    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
:meth:`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) <https://www.rfc-editor.org/rfc/rfc4122.txt>`__ is
  generated and returned by
  :meth:`~Connection.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 :meth:`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
  :meth:`Cursor.execute()` or :meth:`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
  :ref:`round-trips <roundtrips>` 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.

.. _suspendtxns:

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
:meth:`Connection.suspend_sessionless_transaction()`:

.. code-block:: python

    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
:meth:`Cursor.execute()` or :meth:`Cursor.executemany()`. This setting
suspends the transaction if the executed statement or PL/SQL block completes
successfully. This helps reduce the number of
:ref:`round-trips <roundtrips>` to the database which in turn improves
performance. For example:

.. code-block:: python

    # 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.

.. _resumetxns:

Resuming Sessionless Transactions
---------------------------------

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

.. code-block:: python

    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
:meth:`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
  <https://docs.oracle.com/en/error-help/db/ora-25351>`__ is raised. If another
  connection completes the transaction, the error `ORA-24756
  <https://docs.oracle.com/en/error-help/db/ora-24756>`__ is raised. These
  error messages are only thrown for non-RAC instances. For information on
  using Oracle RAC, see :ref:`Sessionless Transactions with Oracle RAC
  <sessionlesstxnswithrac>`.

- ``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
  :ref:`round-trips <roundtrips>` to the database.

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

.. _commitorrollbacktxns:

Committing or Rolling Back Sessionless Transactions
---------------------------------------------------

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

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

.. _examplesessionlesstxns:

Example of Using Sessionless Transactions
-----------------------------------------

An example of using Sessionless Transactions is:

.. code-block:: python

    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:

.. code-block:: python

    # 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')]

.. _viewsessionlesstxns:

Viewing Sessionless Transactions
--------------------------------

The Oracle Database `V$GLOBAL_TRANSACTION <https://www.oracle.com/pls/topic/
lookup?ctx=dblatest&id=GUID-85BD524A-FA12-417F-AC12-4863314E0349>`__ 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() <https://docs.oracle.com/en/database/oracle/oracle
-database/23/sqlrf/NVL.html>`__:

.. code-block:: sql

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

The `GET_TRANSACTION_ID Function <https://www.oracle.com/pls/topic/lookup?ctx=
dblatest&id=GUID-5E1C1B63-207F-4587-8259-0CED93EB9643>`__ of the
DBMS_TRANSACTION package returns the transaction identifier that is used in
the current connection.
