Was this helpful?
Transactions
A transaction is one or more query statements that make up a logical unit of work. This unit of work is either executed in its entirety, or it is totally or partially rolled back.
With OpenAPI, three types of transactions can occur. They are:
Multi-statement transaction
This type of transaction only affects a single database through a single connection. A transaction is started by IIapi_query() when the qy_tranHandle parameter is NULL. The transaction is committed using IIapi_commit() or rolled back using IIapi_rollback().
Distributed transaction
This type of transaction uses the two-phase commit mechanism to ensure that committal of a distributed transaction occurs in all participating databases through multiple connections.
Distributed transactions are identified by a transaction ID handle returned by IIapi_registerXID(). This handle is used to start transactions on each participating connection by being passed as the qy_tranHandle value when calling IIapi_query(). The transaction ends by calling IIapi_prepareCommit() for each connection followed by IIapi_commit() or IIapi_rollback(). The transaction ID handle is freed by calling IIapi_releaseXID().
Autocommit transaction
This type of transaction causes each individual query to be automatically committed when complete. If a cursor is opened, the commit occurs when the cursor is closed.
Autocommit transactions are started by IIapi_autoCommit() using the connection handle as input. An autocommit transaction ends by calling IIapi_autoCommit() with the autocommit transaction handle as input.
XA transaction
The XA transaction specification is a standardized interface for a distributed transaction processing system. This type of transaction uses a two-phase commit mechanism to ensure that committal of a distributed transaction occurs in all participating databases through multiple connections.
XA transactions are identified by an XA transaction ID. An XA transaction branch is started and associated with a particular connection by calling IIapi_xaStart(). The association is dropped by calling IIapi_xaEnd().
More information:
How Transactions Work
The following sections detail how an application begins and ends a transaction, how distributed transactions are used, and how savepoints are used.
How an Application Begins a Transaction
An application specifies the beginning of a new transaction by calling IIapi_query() with a input parameter qy_tranHandle. If the parameter is a NULL pointer or is a transaction ID handle created by IIapi_registerXID(), a new transaction is begun and a transaction handle is allocated and returned in qy_tranHandle. If the qy_tranHandle input value is a transaction handle returned by a previous call to IIapi_query() or IIapi_xaStart(), the query is performed as part of the already-opened transaction. If the qy_tranHandle input value is an autocommit transaction handle returned by IIapi_autoCommit(), the query is executed and the results are immediately committed by the server.
How an Application Ends a Transaction
At the end of a transaction, the application calls IIapi_commit() or IIapi_rollback() before starting another transaction within the connection. IIapi_commit() ends the transaction by committing all SQL statements upon completion, thereby guaranteeing that changes to the database are permanent. IIapi_rollback() ends the transaction by aborting all query statements being executed within the transaction unless a savepoint handle is specified. For XA transactions, the association between the transaction and connection is dropped by calling IIapi_xaEnd().
How Distributed Transactions Are Used
If the transaction is distributed, IIapi_prepareCommit() must be called for each connection participating in the transaction prior to calling IIapi_commit() or IIapi_rollback(). For distributed transactions, the resources allocated by IIapi_registerXID() are freed by calling IIapi_releaseXID() once the transaction has been fully committed or rolled back.
How Savepoints Are Used
In a multi-statement transaction, savepoints can be defined using the IIapi_savePoint() function. IIapi_savePoint() allocates a savepoint handle to identify each savepoint, which can be used to perform a partial rollback when calling IIapi_rollback(). If a savepoint is specified with IIapi_rollback(), only the query statements executed following the savepoint are aborted and the transaction remains active. When a transaction is committed or fully rolled back, all associated savepoint handles are automatically released.
The underlying GCA protocol accepts only one transaction at a time within a connection. Once a transaction is started, the application must use the same transaction within that connection for all query statements until the transaction is committed or rolled back.
How XA Transactions Are Used
For distributed XA transactions, IIapi_xaPrepare() must be called for each transaction branch after calling Iiapi_xaEnd(). Each branch can then be committed or rolled back by calling IIapi_xaCommit() or IIapi_xaRollback(). XA transaction branches can also be completed directly (one-phase) by omitting the call to IIapi_xaPrepare().
Last modified date: 01/30/2023