Working with Sessionless Transactions Using OCI Functions

25 Working with Sessionless Transactions Using OCI Functions

Sessionless transactions enable 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. The same transaction can be committed or rolledback from another session to end the transaction.

For example, the transaction can be started on instance session 1, continued on instance session 2, and finally committed on instance 3 from the session 3.

This feature provides functions on the Database server and the client to perform the following activities:

Set the user specified unique transaction identifier on the transactions
Start a new transaction with the specified transaction identifier
Suspend the transaction
Resume an existing transaction identified by a transaction identifier
End the transaction using either COMMIT or ROLLBACK functions

Users can call Oracle Call Interface functions to interact with Oracle Database server. Different types of service context handles are used to maintain states.

25.1 Concepts Used in Sessionless Transactions

This section describes the concepts used in Sessionless transactions.

The following are some of the concepts used in Sessionless transactions:

25.1.1 Global Transaction ID

Every Sessionless transaction must be identified by a user specified unique transaction identifier called Global Transaction ID (GTRID). It is used to manage the Sessionless transactions lifecycle from start to end including recovery of the transactions.

25.1.2 Round-trip

A server round-trip is defined as the request or data sent from the client to the server and the response sent back to the client. A round-trip must have one main call and may contain piggyback functions.

See Also:

Piggyback Functions

25.1.3 Piggyback Functions

To reduce the number of round-trips to the server, OCI client library does not always incur a round-trip when receiving requests from the users. Instead, the client library stores the requests as piggyback requests and attaches them to the next request that must be served as the main call of a round-trip. When the Oracle server handles the round-trip, it typically first executes the piggyback function requests, called as pre-call piggyback and then executes the main call.

User can request to suspend the transaction after the next main call, called as post-call piggyback suspend. Server still processes such piggyback requests before the main call, but instead of acting immediately, it notes down this request before the main call and defers the suspend operation to happen after the main call.

Example 25-1 Pre-call piggyback

int main()
{
  ...
  OCITransStart(svchp, errhp, 60, OCI_TRANS_SESSIONLESS | OCI_TRANS_NEW);  /* a pre-call piggyback which starts a Sessionless transaction */
  OCIStmtExecute(...) /* a call that incurs a round-trip to server. This is the "main call". */
  ...
}

In this example, the first OCI call (OCITransStart) is a pre-call piggyback. It is returned immediately after OCI client library records this request. However, the request is not sent to the server yet. When OCIStmtExecute is called, the client library sends the OCITransStart pre-call piggyback request along with the OCIStmtExecute main call. When the server receives this package, it learns that OCITransStart is the pre-call piggyback and OCIStmtExecute is the main call. Therefore, it invokes the procedure corresponding to OCITransStart before invoking the procedure of OCStmtExecute.

If any pre-call piggyback returns an error, then the main call and the possible post-call piggyback suspend is not invoked.

Example 25-2 Post-call piggyback suspend

int main()
{
   ...
  OCITransDetach(svchp, errhp, OCI_TRANS_SESSIONLESS | OCI_TRANS_POST_CALL));  
  /* a post-call piggyback which suspends a Sessionless transaction */
  OCIStmtExecute(...) /* a call that incurs a round-trip to server. This is the "main call". */
  ...
}

In this example, the first OCI call (OCITransDetach) is a post-call piggyback. It is returned immediately after the client library records this request. However, the request is not sent to the server yet.

When OCIStmtExecute function is invoked, the client library sends the OCITransDetach post-call piggyback request along with the OCIStmtExecute main call. When the server receives this package, it learns that OCITransDetach is the post-call piggyback and OCIStmtExecute is the main call. Therefore, it first invokes the procedure corresponding to the main call, that is, OCIStmtExecute, and then invoke the procedure corresponding to OCITransDetach.

If the preceding example is run when the session is associated with a Sessionless transaction, it is effectively executing a statement (within a Sessionless transaction) and then suspends from such Sessionless transaction, all taking place in a single round-trip. If the main call returns an error, then the possible post-call piggyback suspend is not invoked.

25.1.4 Sessionless Transaction

Sessionless transaction has the capability to start, suspend, resume, commit, and roll back from any session in the database. For example, the transaction can be started on Sesion 1, continued on Session 2, and finally committed from the Session 3. Note that these sessions can come from different instanaces in the RAC configuration.

25.1.5 Active Sessionless Transaction

The Sessionless transaction that is currently associated to a session is said to be active. After starting a new Sessionless transaction or resuming an existing Sessionless transaction, such transaction is said to be an active Sessionless transaction. When the Sessionless transaction is being suspended, committed, or rolled back, it is no longer active.

25.2 Lifecycle of Sessionless Transactions on OCI

Lifecycle of the Sessionless transaction has the capability to be started, suspended, resumed, committed and rolled back from any session that connects to the same database.

A Sessionless transaction can be started on a server or on a client. The set of functions to start, resume, and suspend Sessionless transactions on a server is not interoperable with that on the client. The client-side functions to start, suspend, or resume transactions keep returning an error until the transaction becomes inactive (that is, suspended, committed, or rolled back) on the server and vice versa.

If a GTRID is not specified then the Oracle Call Interface (OCI) client generates a GTRID along with the start transaction request. Subsequent units of work submitted to the Oracle Database server are in the transaction context. Over the course of time, the transaction could be suspended and resumed between units of work multiple times. The transaction ends when you commit or roll back the transaction.

Topics:

See Also:

Developing Applications with Flex Transactions section in Oracle Database Database Development Guide.
Transaction Functions

25.2.1 Prerequisites for Using Sessionless Transactions on OCI

User must allocate a transaction handle and set it as the OCI_ATTR_TRANS of the service context handle.

OCISvcCtx *svchp = NULL;
 /* get a service context handle from the session pool. */
OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
               NULL, NULL, NULL, OCI_SESSGET_SPOOL | OCI_SESSGET_PURITY_NEW);
 
/* create a transaction handle */
OCITrans    *txnhp  = (OCITrans *)0;  /* Sessionless transaction */
OCIHandleAlloc(envhp, (void **)&txnhp, OCI_HTYPE_TRANS, 0, 0);
 
/* set txn handle as service context handle's OCI_ATTR_TRANS attribute */
OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, txnhp, 0, OCI_ATTR_TRANS, errhp);

25.2.2 Setting GTRID on OCI

If you want to specify the GTRID, you must call OCIAttrSet to set the XID struct that contains the GTRID as the OCI_ATTR_XID attribute of the transaction handle. Sessionless transactions do not use the data members formatID and bqual_length of struct XID.

The following is the sample code snippet for setting the GTRID:

/* Get current transaction handle */
OCITrans *current_txnhp;
OCIAttrGet(svchp, OCI_HTYPE_SVCCTX, &current_txnhp, NULL,
           errhp);
 
/* set GTRID in XID struct */
XID         res_xid;
strcpy(res_xid.data, "client_user_gtrid_1");
res_xid.gtrid_length = strlen("client_user_gtrid_1");
 
/* set XID as txn handle's attribute */
OCIAttrSet(current_txnhp, OCI_HTYPE_TRANS, &res_xid, sizeof(XID), OCI_ATTR_XID, errhp);

Set XID attribute to NULL to enable the client library to generate GTRID on the start transaction.

OCIAttrSet(current_txnhp, OCI_HTYPE_TRANS, NULL, 0, OCI_ATTR_XID, errhp);

Note:

You cannot set XID in the following conditions:

If there is an active Sessionless transaction that was started or resumed through PL/SQL function, then OCI_ATTR_TRANS attribute of the service context handle is a read-only transaction handle managed by the OCI library. Returns an error 26210.
If the OCI library starts a fresh active Sessionless transaction, then such GTRID has to be retrieved before it can be set. Returns an error 26204. Refer to Retrieving GTRID on OCI.
If the active flex transaction was started (as a new or resumed transaction) on OCI and the GTRID was generated by the OCI library. Since the transaction is active, user has not got the GTRID on OCI. Returns an error 26204.
A piggyback call to start a new Sessionless transaction or resume an existing Sessionless transaction is recorded but is not yet sent to the Database server. Returns an error 26216.
See Also:
- OCIAttrGet()
- OCIAttrSet()

25.2.3 Retrieving GTRID on OCI

To get the GTRID from XID, call OCIAttrGet function to get the OCI_ATTR_XID attribute on the current transaction handle (that is, attribute OCI_ATTR_TRANS of service context handle). When you are working with Sessionless transactions, you must not change the members (including GTRID) in the XID retrieved.

If an active Sessionless transaction was started or resumed on OCI client, the XID retrieved must be the one that was last set as described in the section Setting GTRID on OCI section. If the transaction is started or resumed on the server, then the transaction handle is managed by the OCI client library and the GTRID retrieved is the GTRID of the active Sessionless transaction.

If the active Sessionless transaction was newly started on OCI client and the OCI library generates the GTRID, then the application must retrieve the generated GTRID before such active Sessionless transaction can be suspended. That is, application must call OCIAttrGet function to get the OCI_ATTR_XID attribute.

OCITrans *current_txnhp;
XID      *current_xidp;
char      gtrid_buffer[65];
 
OCIAttrGet(svchp, OCI_HTYPE_SVCCTX, &current_txnhp, NULL, OCI_ATTR_TRANS, errhp);
OCIAttrGet(current_txnhp, OCI_HTYPE_TRANS, &current_xidp, NULL, OCI_ATTR_XID, errhp));
memcpy(gtrid_buffer, current_xidp->data, current_xidp->gtrid_length);

25.2.4 Starting a New Sessionless Transaction

With the OCI_ATTR_TRANS and OCI_ATTR_XID attributes set, the application can start a new Sessionless transaction by executing OCITransStart function with flag OCI_TRANS_SESSIONLESS | OCI_TRANS_NEW along with timeout.

The OCI_TRANS_SESSIONLESS flag distinguishes this request from requests to start an XA branch. Starting a new Sessionless transaction on the client is a pre-call piggyback, meaning that this start request is asynchronous and is sent to and is handled by the server when the next server round-trip occurs.

OCISvcCtx *svchp;
OCIError *errhp;
...
ub4 timeout = 60;

/* start the transaction. this will be piggybacked on the subsequent round trip.
    */
OCITransStart(svchp, errhp, timeout, OCI_TRANS_SESSIONLESS | OCI_TRANS_NEW);

Where, timeout is the time for how long (in seconds) after the suspension of this Sessionless transaction should the server roll back this transaction. This is an attempt to avoid a transaction from holding on to database resources (such as row locks) indefinitely.

If the OCI_ATTR_TRANS attribute of the service context handle is not set, then the call fails and returns OCI_INVALID_HANDLE.OCI client library that first checks whether a transaction handle exists as an attribute of the service context handle (OCI library returns OCI_INVALID_HANDLE).

If the flag contains OCI_TRANS_SESSIONLESS but does not contain either OCI_TRANS_NEW or OCI_TRANS_RESUME, error 26213 is returned. If the Database server does not support Sessionless transaction, error 2027 is returned.

State	No transaction exists	Local transaction	Sessionless transaction started on server	Sessionless transaction started on client, lib-generated GTRID	Sessionless transaction started on client, user-provided GTRID	Global transaction
Checks	None	None	Always error 26211	If the transaction is active and the generated GTRID is not retrieved yet,error 26204 is returned. If any post-call piggyback (exampe: post-call suspend) is pending, returns error 26215. If internal GUID generation function fails, then error 26205 is returned.	If any post-call piggyback (example post-call suspend) is pending, returns error 26215.	None

State

No transaction exists

Local transaction

Sessionless transaction started on server

Sessionless transaction started on client, lib-generated GTRID

Sessionless transaction started on client, user-provided GTRID

Global transaction

Checks

None

Always error 26211

If the transaction is active and the generated GTRID is not retrieved yet,error 26204 is returned.

If any post-call piggyback (exampe: post-call suspend) is pending, returns error 26215.

If internal GUID generation function fails, then error 26205 is returned.

If any post-call piggyback (example post-call suspend) is pending, returns error 26215.

None

The start call is recorded as a pre-call piggyback. OCI_ATTR_XID cannot be altered before the piggyback is sent to the Database server, and the attempt to set it returns error 26216

See Also:

Global Transaction ID

25.2.5 Suspending an Active Sessionless Transaction

You can suspend an active Sessionless transaction on OCI client. If there is no transaction in a session when the suspend happens, the suspend procedure is a no-op. If the session has a transaction other than a Sessionless transaction (a local transaction, or an XA transaction branch), error ORA-26202 is returned. Suspending an active Sessionless transaction can be a pre-call piggyback, a post-call piggyback, or a main call (which means it incurs a round-trip). You can specify the flag bits to indicate your required option.

Note:

If any pre-call piggyback raises an error, the main call is not invoked. If the main call raises an error, the post-call piggyback suspend is not invoked.

Example 25-3 Synchronous OCITransDetach

To suspend the active Sessionless transaction as a main call (synchronously suspend the transaction), call the OCITransDetach function, as shown in the following example:

OCITransDetach(svchp, errhp, OCI_TRANS_SESSIONLESS | OCI_DEFAULT);

Example 25-4 Pre-call OCITransDetach

To issue a pre-call piggyback suspend (suspend on the next server round-trip, before the main call), call the OCITransDetach function with the OCI_TRANS_SESSIONLESS | OCI_TRANS_PRE_CALL flag.

In the following example, the transaction is suspended on the round-trip during the OCIPing() call.

OCITransDetach(svchp, errhp, OCI_TRANS_SESSIONLESS | OCI_TRANS_PRE_CALL);
OCIPing(svchp, errhp, OCI_DEFAULT);

Example 25-5 Post-call OCITransDetach

To issue a post-call piggyback suspend (suspend on the next server round-trip, after the main call), call the OCITransDetach function with the OCI_TRANS_SESSIONLESS | OCI_TRANS_POST_CALL flag as shown in the following example:

OCITransDetach(svchp, errhp, OCI_TRANS_SESSIONLESS | OCI_TRANS_POST_CALL);
OCIPing(svchp, errhp, OCI_DEFAULT);

Note:

This is useful when you know about the last round-trip to the server.

Note:

The post-call suspend can be piggybacked on the same round-trip that start transaction is piggybacked on. As a result, a single statement running in the context of a Sessionless transaction can be done in a single server round-trip.

Example 25-6 Post-call OCITransDetach

In the following example, the transaction identified by the my_Flex_txn3 GTRID is suspended automatically after the successful execution of OCIStmtExecute():

/* obtaining a service context handle from session pool */
OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
               NULL, NULL, NULL, OCI_SESSGET_SPOOL | OCI_SESSGET_PURITY_NEW);
OCIAttrSet (svchp, OCI_HTYPE_SVCCTX, txnhp, 0, OCI_ATTR_TRANS, errhp);
 
memcpy(xidp->data, "my_Flex_txn3", strlen("my_Flex_txn3"));
xidp->gtrid_length = strlen("my_Flex_txn3");
OCIAttrSet(txnhp, OCI_HTYPE_TRANS, &xidp, sizeof(XID), OCI_ATTR_XID, errhp);
OCITransStart(svchp, errhp, 60, OCI_TRANS_SESSIONLESS | OCI_TRANS_NEW);
 
// Ask a post-call suspend
OCITransDetach(svchp, errhp, OCI_TRANS_SESSIONLESS | OCI_POST_CALL);
 
// Do the DML
OCIStmtPrepare(…); /* insert into mytab1(c1, c2) values (1, 1); */
OCIStmtBindByPos(…);
OCIStmtExecute(…);   /* Server suspends the transaction after this DML is executed. */

Before the suspend call is sent to the server (in synchronous case) or recorded (in pre-call piggyback or post-call piggyback case), OCI checks for the following:

If the attribute OCI_ATTR_TRANS of the service context handle is not set to a valid transaction handle, then OCITransDetach function returns OCI_INVALID_HANDLE.
If the flag contains OCI_TRANS_SESSIONLESS but does not contain either one of the following: OCI_DEFAULT, OCI_TRANS_PRE_CALL, or OCI_TRANS_POST_CALL, then it returns error 26214.
If the server supports a Sessionless transaction returns an error 26207.
If a previous post-call suspend is recorded but not yet sent to the server, then it raises an error 26215. This applies only when the pre-call suspend is invoked.

State	No transaction exists	Local transaction	Sessionless transaction started on server	Sessionless transaction started on client, lib-generated GTRID	Sessionless transaction started on client, user-provided GTRID	XA transaction branch
Checks	None	None	Always error 26211	If the transaction is active and the generated GTRID is not retrieved yet, error 26204 is returned. If any post-call piggyback (exampe: post-call suspend) is pending, returns error 26215.	If any post-call piggyback (example post-call suspend) is pending, returns error 26215.	None

25.2.6 Resuming a Suspended Sessionless Transaction

Resuming a suspended Sessionless transaction is similar to starting a Sessionless transaction. The difference between the two is that when resuming a Sessionless transaction on OCI, the OCI_TRANS_RESUME flag in the OCITransStart function replaces the OCI_TRANS_NEW flag.

The OCI_ATTR_TRANS attribute of the service context handle must be set to point to a transaction handle, whose OCI_ATTR_XID attribute must be set to contain the GTRID of the Sessionless transaction to be resumed. Invoking the OCITransStart function with the OCI_TRANS_SESSIONLESS | OCI_TRANS_RESUME resumes the Sessionless transaction identified by the GTRID set in OCI_ATTR_XID. Resuming a Sessionless transaction on the client is a pre-call piggyback, which means that this resume request is asynchronous and is sent to and handled by the server in the next server round trip.

ub4 timeout = 20;
OCITransStart(svchp, errhp, timeout, OCI_TRANS_SESSIONLESS | OCI_TRANS_RESUME);

Where, timeout argument determines for how long (in seconds) the server attempts to resume the transaction. If multiple sessions connect to the same database instance and request to resume the same Sessionless transaction, only one can successfully resume at any given time.

Before recording the resume call, OCI does the following checks:

Similar to starting a new Sessionless transaction, if the OCI_ATTR_TRANS attribute of the service context handle is not set, the call would fail and return OCI_INVALID_HANDLE.
If the flag argument contains OCI_TRANS_SESSIONLESS but does not contain either OCI_TRANS_NEW or OCI_TRANS_RESUME, error 26213 is returned.
If the Database server does not support Sessionless transaction, then an error 26207 is returned.
If OCI_ATTR_XID attribute of the transaction handle is not set, then an error 26212 is returned.
If a Sessionless transaction started, then the Database server is active, an error 26211 is returned.
If a Sessionless transaction started with OCI library, then the generated GTRID is active, and such GTRID has not been retrieved, an error 26204 is returned
If a post-call suspend is recorded but not yet sent to the server, then an error 26215 is returned.

After the checks are passed, OCI library notes down this pre-call piggyback resume request. The OCI_ATTR_XID cannot be changed until this pre-call request has been handled by the server. An attempt to set it returns error 26216.

25.2.7 Commiting a Sessionless Transaction

To commit an active Sessionless transaction in the session COMMIT statement is issued to the server using OCIStmtExecute() function. Similarly, the OCITransCommit() function is executed from the OCI client.

The following example starts a Sessionless transaction identified by the my_Flex_txn5 GTRID and issues a DML and commits the transaction:

Example 25-7 Commit Using OCITransCommit()


OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
               NULL, NULL, NULL, OCI_SESSGET_SPOOL | OCI_SESSGET_PURITY_NEW);
OCIAttrSet (svchp, OCI_HTYPE_SVCCTX, txnhp, 0, OCI_ATTR_TRANS, errhp);
 
memcpy(xidp->data, "my_Flex_txn5", strlen("my_Flex_txn5"));
xidp->gtrid_length = strlen("my_Flex_txn5");
OCIAttrSet(txnhp, OCI_HTYPE_TRANS, &xidp, sizeof(XID), OCI_ATTR_XID, errhp);
OCITransStart(svchp, errhp, 60, OCI_TRANS_SESSIONLESS | OCI_TRANS_NEW);
 /* Executes unit of work in the Sessionless transaction context */ 
OCITransCommit(svchp, errhp, OCI_DEFAULT);

Example 25-8 Commit Using OCISessionRelease()

Following the existing behavior, OCISessionRelease commits the active Sessionless transaction. In the following example, Sessionless transaction identified by my_Flex_txn6 is committed:

/* start Sessionless transaction */
OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0, NULL,
               NULL, NULL, OCI_SESSGET_SPOOL | OCI_SESSGET_PURITY_NEW);
 
OCIAttrSet (svchp, OCI_HTYPE_SVCCTX, txnhp, 0, OCI_ATTR_TRANS, errhp);
 
memcpy(xidp->data, "my_Flex_txn6", strlen("my_Flex_txn6"));
xidp->gtrid_length = strlen("my_Flex_txn6");
OCIAttrSet(txnhp, OCI_HTYPE_TRANS, &xidp, sizeof(XID), OCI_ATTR_XID, errhp);
OCITransStart(svchp, errhp, 60, OCI_TRANS_SESSIONLESS | OCI_TRANS_NEW);
/* done start Sessionless transaction */
/* Executes unit of work in the Sessionless transaction context */
 OCISessionRelease(svchp, errhp, 0, 0, OCI_DEFAULT); 
/* Commits the Sessionless transaction */

25.2.8 Rolling Back a Sessionless Transaction

To roll back an active Sessionless transaction in a session, issue the ROLLBACK statement to the server using the OCIStmtExecute() function. Similarly successful execution of OCITransRollback() function from the client rolls back the active sessionless transaction in the session. The following example starts a Sessionless transaction identified by the my_Flex_txn8 issues a DML statement, to execute a unit of work.

Example 25-9 Rollback Using OCITransRollback()

Run the OCITransRollback() function from OCI client.

/* start Sessionless transaction */
OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0, NULL,
               NULL, NULL, OCI_SESSGET_SPOOL | OCI_SESSGET_PURITY_NEW);
 
OCIAttrSet (svchp, OCI_HTYPE_SVCCTX, txnhp, 0, OCI_ATTR_TRANS, errhp);
 
char gtrid = "my_Flex_txn8";
memcpy(xidp->data, gtrid, strlen(gtrid));
xidp->gtrid_length = strlen(gtrid);
OCIAttrSet(txnhp, OCI_HTYPE_TRANS, &xidp, sizeof(XID), OCI_ATTR_XID, errhp);
OCITransStart(svchp, errhp, 60, OCI_TRANS_SESSIONLESS | OCI_TRANS_NEW);
/* done start Sessionless transaction */
/* Executes unit of work in the Sessionless transaction context */ 
OCITransRollback(svchp, errhp, OCI_DEFAULT);

Example 25-10 Rollback using OCIRequestEnd()

In the following example, the transaction identified by the my_Flex_txn9 GTRID is rolled back after the successful execution of OCIRequestEnd():


OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0, NULL,
               NULL, NULL, OCI_SESSGET_SPOOL | OCI_SESSGET_PURITY_NEW);
 
OCIAttrSet (svchp, OCI_HTYPE_SVCCTX, txnhp, 0, OCI_ATTR_TRANS, errhp);
 
char gtrid = "my_Flex_txn9";
memcpy(xidp->data, gtrid, strlen(gtrid));
xidp->gtrid_length = strlen(gtrid);
OCIAttrSet(txnhp, OCI_HTYPE_TRANS, &xidp, sizeof(XID), OCI_ATTR_XID, errhp);
/* start Sessionless transaction */
OCITransStart(svchp, errhp, 60, OCI_TRANS_SESSIONLESS | OCI_TRANS_NEW);
 
OCIRequestBegin(…);
/* Executes unit of work in the Sessionless transaction context */
OCIRequestEnd(…); /* Rolled back the Sessionless transaction */