Three-tier applications such as transaction processing (TP) monitors create and manage global transactions.

They supply a global transaction identifier (XID) that a server associates with a local transaction.

A global transaction has one or more branches. Each branch is identified by an XID. The XID consists of a global transaction identifier (gtrid) and a branch qualifier (bqual). This structure is based on the standard XA specification.

Table 10-1 provides the structure for one possible XID of 1234.

The transaction identifier used by OCI transaction calls is set in the OCI_ATTR_XID attribute of the transaction handle, by using OCIAttrSet(). Alternately, the transaction can be identified by a name set in the OCI_ATTR_TRANS_NAME attribute.

When this attribute is set in a transaction handle, the length of the name can be at most 64 bytes.

The formatid of the XID is 0 and the branch qualifier is 0.

When this attribute is retrieved from a transaction handle, the returned transaction name is the global transaction identifier. The size is the length of the global transaction identifier.

Within a single global transaction, Oracle Database supports both tightly coupled and loosely coupled relationships between a pair of branches.

• Tightly coupled branches share the same local transaction. The gtrid references a unique local transaction, and multiple branches point to that same transaction. The owner of the transaction is the branch that was created first.

• Loosely coupled branches use different local transactions. The gtrid and bqual together map to a unique local transaction. Each branch points to a different transaction.

The flags parameter of OCITransStart() allows applications to pass OCI_TRANS_TIGHT or OCI_TRANS_LOOSE values to specify the type of coupling.

A session corresponds to a user session, created with OCISessionBegin().

Figure 10-1 illustrates tightly coupled branches within an application. The XIDs of the two branches (B1 and B2) share the same gtrid, because they are operating on the same transaction (T), but they have a different bqual, because they are on separate branches.

Figure 10-1 Multiple Tightly Coupled Branches

Description of "Figure 10-1 Multiple Tightly Coupled Branches"

Figure 10-2 illustrates how a single session operates on different branches. The gtrid components of the XIDs are different, because they represent separate global transactions.

Figure 10-2 Session Operating on Multiple Branches

Description of "Figure 10-2 Session Operating on Multiple Branches"

It is possible for a single session to operate on multiple branches that share the same transaction, but this scenario does not have much practical value.

Transaction branches are classified into two states: active branches and inactive branches.

A branch is active if a server process is executing requests on the branch. A branch is inactive if no server processes are executing requests in the branch. In this case, no session is the parent of the branch, and the branch becomes owned by the PMON process in the server.

A branch becomes inactive when an OCI application detaches it, using the OCITransDetach() call.

The branch can be made active again by resuming it with a call to OCITransStart() with the flags parameter set to OCI_TRANS_RESUME.

When an application detaches a branch with OCITransDetach(), it uses the value specified in the timeout parameter of the OCITransStart() call that created the branch. The timeout specifies the number of seconds the transaction can remain dormant as a child of PMON before being deleted.

To resume a branch, the application calls OCITransStart(), specifying the XID of the branch as an attribute of the transaction handle, OCI_TRANS_RESUME for the flags parameter, and a different timeout parameter. This timeout value for this call specifies the length of time that the session waits for the branch to become available if it is currently in use by another process. If no other processes are accessing the branch, it can be resumed immediately. A transaction can be resumed by a different process than the one that detached it, if that process has the same authorization as the one that detached the transaction.

The server handle has OCI_ATTR_EXTERNAL_NAME and OCI_ATTR_INTERNAL_NAME attributes.

These attributes set the client database name recorded when performing global transactions. The name can be used by the database administrator to track transactions that may be pending in a prepared state because of failures.

Global transactions can be committed in one or two phases.

The simplest situation is when a single transaction is operating against a single database. In this case, the application can perform a one-phase commit of the transaction by calling OCITransCommit(), because the default value of the call is for one-phase commit.

The situation is more complicated if the application is processing transactions against multiple Oracle databases. In this case, a two-phase commit is necessary. A two-phase commit operation consists of these steps:

1. Prepare - The application issues an OCITransPrepare() call against each transaction. Each transaction returns a value indicating whether or not it can commit its current work (OCI_SUCCESS) or not (OCI_ERROR).

2. Commit - If each OCITransPrepare() call returns a value of OCI_SUCCESS, the application can issue an OCITransCommit() call to each transaction. The flags parameter of the commit call must be explicitly set to OCI_TRANS_TWOPHASE for the appropriate behavior, because the default for this call is for one-phase commit.

   Note:

   The OCITransPrepare() call can also return OCI_SUCCESS_WITH_INFO if a transaction must indicate that it is read-only. Thus a commit is neither appropriate nor necessary.

An additional call, OCITransForget(), causes a database to "forget" a completed transaction. This call is for situations in which a problem has occurred that requires that a two-phase commit be terminated. When an Oracle database receives an OCITransForget() call, it removes all information about the transaction.

Sometimes when multiple applications use different branches of a global transaction against the same Oracle database.

Before such a transaction can be committed, all branches must be prepared.

Most often, the applications using the branches are responsible for preparing their own branches. However, some architectures turn this responsibility over to an external transaction service. This external transaction service must then prepare each branch of the global transaction. The traditional OCITransPrepare() call is inefficient for this task as each branch must be individually prepared. The OCITransMultiPrepare() call, prepares multiple branches involved in the same global transaction in one round-trip. This call is more efficient and can greatly reduce the number of messages sent from the client to the server.

Lists the steps for a one-phase commit operation.

OCITransStart()

SQL UPDATE

OCITransCommit()

Lists the steps for a two-phase commit operation.

OCITransStart()

SQL UPDATE

OCITransDetach()

OCITransStart()

SQL UPDATE

OCITransPrepare()

OCITransCommit()

Lsts the steps in a failed read-only update operation.

OCITransStart()

SQL UPDATE

OCITransCommit()

Lists the steps for a read-only transaction.

OCITransStart()

SQL SELECT

OCITransCommit()

Use OCI_CRED_PROXY as the value passed in the credt parameter of OCISessionBegin().

When an application server starts a session on behalf of a client, use OCI_CRED_PROXY as the value passed in the credt parameter of OCISessionBegin() rather than OCI_CRED_RDBMS (database user name and password required) or OCI_CRED_EXT (externally provided credentials).

Use the OCI_ATTR_PROXY_CREDENTIALS attribute to specify the credentials of the application server in client authentication.

You can code the following declarations and OCIAttrSet() call, as shown in Example 10-2.

OCISession *session_handle;
OCISvcCtx  *application_server_session_handle;
OCIError   *error_handle;
...
OCIAttrSet((void *)session_handle, (ub4) OCI_HTYPE_SESSION, 
           (void *)application_server_session_handle, (ub4) 0, 
            OCI_ATTR_PROXY_CREDENTIALS, error_handle);

Use OCI_ATTR_DISTINGUISHED_NAME to pass the distinguished name of the client.

Your applications can use the distinguished name contained within a X.509 certificate as the login name of the client, instead of the database user name.

To pass the distinguished name of the client, the middle-tier server calls OCIAttrSet(), passing OCI_ATTR_DISTINGUISHED_NAME, as shown in Example 10-3.

/* Declarations */
...
OCIAttrSet((void *)session_handle, (ub4) OCI_HTYPE_SESSION,
           (void *)distinguished_name, (ub4) 0,
           OCI_ATTR_DISTINGUISHED_NAME, error_handle);

Certificate-based proxy authentication using OCI_ATTR_CERTIFICATE will not be supported in future Oracle Database releases.

Use OCI_ATTR_DISTINGUISHED_NAME or OCI_ATTR_USERNAME attribute instead. This method of authentication is similar to the use of the distinguished name. The entire X.509 certificate is passed by the middle-tier server to the database.

To pass over the entire certificate, the middle tier calls OCIAttrSet(), passing OCI_ATTR_CERTIFICATE, as shown in Example 10-4.

OCIAttrSet((void *)session_handle, (ub4) OCI_HTYPE_SESSION, 
           (void *)certificate, ub4 certificate_length, 
           OCI_ATTR_CERTIFICATE, error_handle);

Use the OCI_ATTR_INITIAL_CLIENT_ROLES attribute to specify the roles the client is to possess when the application server connects to the Oracle database.

To enable a set of roles, the function OCIAttrSet() is called with the attribute, an array of NULL-terminated strings, and the number of strings in the array, as shown in Example 10-5.

OCIAttrSet((void *)session_handle, (ub4) OCI_HTYPE_SESSION, 
           (void *)role_array, (ub4) number_of_strings,
           OCI_ATTR_INITIAL_CLIENT_ROLES, error_handle);

Use OCI_ATTR_CLIENT_IDENTIFIER on the middle tier to track end-user identity.

Many middle-tier applications connect to the database as an application, and rely on the middle tier to track end-user identity. To integrate tracking of the identity of these users in various database components, the database client can set the CLIENT_IDENTIFIER (a predefined attribute from the application context namespace USERENV) in the session handle at any time. Use the OCI attribute OCI_ATTR_CLIENT_IDENTIFIER in the call to OCIAttrSet(), as shown in Example 10-6. On the next request to the server, the information is propagated and stored in the server session.

The CLIENT_IDENTIFIER, which can also be set using the DBMS_SESSION.SET_IDENTIFIER procedure, is automatically truncated if the supplied input is more than 64 bytes because the maximum size for the client id is 64 bytes.

OCI_ATTR_CLIENT_IDENTIFIER can also be used in conjunction with the global application context to restrict availability of the context to the selected identity of these users.

When a client has multiple sessions, execute OCIAttrSet() for each session using the same client identifier. OCIAttrSet() must be executed manually for sessions that are reestablished through transparent application failover (TAF).

The client identifier is found in V$SESSION as a CLIENT_IDENTIFIER column or through the system context with this SQL statement:

SELECT SYS_CONTEXT('userenv', 'client_identifier') FROM DUAL;

OCIAttrSet((void *)session_handle, (ub4) OCI_HTYPE_SESSION, 
           (void *)"janedoe", (ub4)strlen("janedoe"),
           OCI_ATTR_CLIENT_IDENTIFIER, error_handle);

To use the password of the client for validation on the middle-tier, the application server can use the existing attribute OCI_ATTR_PASSWORD with the client’s authentication data.

A middle tier can ask the database server to authenticate a client on its behalf by validating the password of the client rather than doing the authentication itself. Although it appears that this is the same as a client/server connection, the client does not have to have Oracle Database software installed on the client's system to be able to perform database operations. To use the password of the client, the application server supplies OCIAttrSet() with the authentication data, using the existing attribute OCI_ATTR_PASSWORD, as shown in Example 10-7.

Example 10-8 shows OCI attributes that enable you to specify the external name and initial privileges of a client. These credentials are used by OCI applications as alternative means of identifying or authenticating the client.

OCIAttrSet((void *)session_handle, (ub4) OCI_HTYPE_SESSION, (void *)password,
           (ub4)0, OCI_ATTR_PASSWORD, error_handle);

...
*OCIEnv *environment_handle; 
OCIServer *data_server_handle; 
OCIError *error_handle; 
OCISvcCtx *application_server_service_handle; 
OraText *client_roles[2]; 
OCISession *first_client_session_handle, second_client_session_handle; 
...
/*
** General initialization and allocation of contexts. 
*/
 
(void) OCIInitialize((ub4) OCI_DEFAULT, (void *)0,
                     (void * (*)(void *, size_t)) 0, 
                     (void * (*)(void *, void *, size_t))0, 
                     (void (*)(void *, void *)) 0 ); 
(void) OCIEnvInit( (OCIEnv **) &environment_handle, OCI_DEFAULT, (size_t) 0,
                  (void **) 0 ); 
(void) OCIHandleAlloc( (void *) environment_handle, (void **) &error_handle,
     OCI_HTYPE_ERROR, (size_t) 0, (void **) 0); 
/* 
** Allocate and initialize the server and service contexts used by the
** application server. 
*/ 

(void) OCIHandleAlloc( (void *) environment_handle, 
     (void **)&data_server_handle, OCI_HTYPE_SERVER, (size_t) 0, (void **) 0); 
(void) OCIHandleAlloc( (void *) environment_handle, (void **)
     &application_server_service_handle, OCI_HTYPE_SVCCTX, (size_t) 0, 
     (void **) 0);  
(void) OCIAttrSet((void *) application_server_service_handle,
     OCI_HTYPE_SVCCTX, (void *) data_server_handle, (ub4) 0, OCI_ATTR_SERVER,
     error_handle); 
/* 
** Authenticate the application server. In this case, external authentication is
** being used. 
*/

(void) OCIHandleAlloc((void *) environment_handle, 
     (void **)&application_server_session_handle, (ub4) OCI_HTYPE_SESSION,
     (size_t) 0, (void **) 0); 
checkerr(error_handle, OCISessionBegin(application_server_service_handle,
     error_handle, application_server_session_handle, OCI_CRED_EXT,
     OCI_DEFAULT)); 
/* 
** Authenticate the first client. 
** Note that no password is specified by the 
** application server for the client as it is trusted. 
*/ 

(void) OCIHandleAlloc((void *) environment_handle, 
     (void **)&first_client_session_handle, (ub4) OCI_HTYPE_SESSION, 
     (size_t) 0,(void **) 0); 
(void) OCIAttrSet((void *) first_client_session_handle, 
     (ub4) OCI_HTYPE_SESSION, (void *) "jeff", (ub4) strlen("jeff"),
     OCI_ATTR_USERNAME, error_handle); 
/* 
** In place of specifying a password, pass the session handle of the application
** server instead. 
*/ 

(void) OCIAttrSet((void *) first_client_session_handle, 
     (ub4) OCI_HTYPE_SESSION, (void *) application_server_session_handle, 
     (ub4) 0, OCI_ATTR_PROXY_CREDENTIALS, error_handle); 
(void) OCIAttrSet((void *) first_client_session_handle, 
     (ub4) OCI_HTYPE_SESSION, (void *) "jeff@VeryBigBank.com", 
     (ub4) strlen("jeff@VeryBigBank.com"), OCI_ATTR_EXTERNAL_NAME,
     error_handle); 
/* 
** Establish the roles that the application server can use as the client. 
*/
 
client_roles[0] = (OraText *) "TELLER"; 
client_roles[1] = (OraText *) "SUPERVISOR";
(void) OCIAttrSet((void *) first_client_session_handle, 
     OCI_ATTR_INITIAL_CLIENT_ROLES, error_handle); 
checkerr(error_handle, OCISessionBegin(application_server_service_handle,
     error_handle, first_client_session_handle, OCI_CRED_PROXY, OCI_DEFAULT)); 
/* 
** To start a session as another client, the application server does the 
** following.
** This code is unchanged from the current way of doing session switching. 
*/

(void) OCIHandleAlloc((void *) environment_handle, 
     (void **)&second_client_session_handle, (ub4) OCI_HTYPE_SESSION, 
     (size_t) 0, (void **) 0); 
(void) OCIAttrSet((void *) second_client_session_handle, 
     (ub4) OCI_HTYPE_SESSION, (void *) "mutt", (ub4) strlen("mutt"),
     OCI_ATTR_USERNAME, error_handle); 
(void) OCIAttrSet((void *) second_client_session_handle, 
     (ub4) OCI_HTYPE_SESSION, (void *) application_server_session_handle, 
     (ub4) 0, OCI_ATTR_PROXY_CREDENTIALS, error_handle); 
(void) OCIAttrSet((void *) second_client_session_handle, 
     (ub4) OCI_HTYPE_SESSION, (void *) "mutt@VeryBigBank.com", 
     (ub4) strlen("mutt@VeryBigBank.com"), OCI_ATTR_EXTERNAL_NAME,
     error_handle); 
/* 
** Note that the application server has not specified any initial roles to have
** as the second client. 
*/
 
checkerr(error_handle, OCISessionBegin(application_server_service_handle,
     error_handle, second_client_session_handle, OCI_CRED_PROXY, OCI_DEFAULT)); 
/* 
** To switch to the first user, the application server applies the session
** handle obtained by the first 
** OCISessionBegin() call. This is the same as is currently done. 
*/ 

(void) OCIAttrSet((void *)application_server_service_handle, 
     (ub4) OCI_HTYPE_SVCCTX, (void *)first_client_session_handle, 
     (ub4)0, (ub4)OCI_ATTR_SESSION, error_handle); 
/* 
** After doing some operations, the application server can switch to
** the second client. That 
** is be done by the following call: 
*/

(void) OCIAttrSet((void *)application_server_service_handle, 
     (ub4) OCI_HTYPE_SVCCTX, 
     (void *)second_client_session_handle, (ub4)0, (ub4)OCI_ATTR_SESSION,
     error_handle); 
/* 
** and then do operations as that client 
*/
...

Use the OCI_ATTR_APPCTX_SIZE attribute to initialize the context array size with the desired number of context attributes in the OCIAttrSet() call.

This is shown in Example 10-9.

OCIAttrSet(session, (ub4) OCI_HTYPE_SESSION, 
           (void *)&size, (ub4)0, OCI_ATTR_APPCTX_SIZE, error_handle); 

Use the OCI_ATTR_APPCTX_LIST attribute to get a handle on the application context list descriptor for the session in the OCIAttrGet() call.

This is shown in Example 10-10. (The parameter ctxl_desc must be of data type OCIParam *).

Example 10-11 shows how to use the application context list descriptor to obtain an individual descriptor for the i-th application context in a call to OCIParamGet().

OCIAttrGet(session, (ub4) OCI_HTYPE_SESSION, 
           (void *)&ctxl_desc, (ub4)0, OCI_ATTR_APPCTX_LIST, error_handle);

OCIParamGet(ctxl_desc, OCI_DTYPE_PARAM, error_handle,(void **)&ctx_desc, i);

Shows how to set the appropriate session handle attributes values for the application context.

Set the appropriate values of the application context using these attributes:

• OCI_ATTR_APPCTX_NAME to set the namespace of the context, which must be a valid SQL identifier.

• OCI_ATTR_APPCTX_ATTR to set an attribute name in the given context, a non-case-sensitive string of up to 30 bytes.

• OCI_ATTR_APPCTX_VALUE to set the value of an attribute in the given context.

Each namespace can have many attributes, each of which has one value. Example 10-12 shows the calls you can use to set them.

Note that only character type is supported, because application context operations are based on the VARCHAR2 data type.

OCIAttrSet(ctx_desc, OCI_DTYPE_PARAM,
     (void *)ctx_name, sizeof(ctx_name), OCI_ATTR_APPCTX_NAME, error_handle);
  
OCIAttrSet(ctx_desc, OCI_DTYPE_PARAM,
     (void *)attr_name, sizeof(attr_name), OCI_ATTR_APPCTX_ATTR, error_handle);  
  
OCIAttrSet(ctx_desc, OCI_DTYPE_PARAM,
     (void *)value, sizeof(value), OCI_ATTR_APPCTX_VALUE, error_handle);  

Use the attribute OCI_ATTR_COLLECT_CALL_TIME and set the value to TRUE to enable the server to measure each call time.

Set a boolean variable to TRUE or FALSE. After you set the OCI_ATTR_COLLECT_CALL_TIME attribute by calling OCIAttrSet(), the server measures each call time. All server times between setting the variable to TRUE and setting it to FALSE are measured.

Use the attribute OCI_ATTR_CALL_TIME to get the elapsed time of the last server call.

The elapsed time, in microseconds, of the last server call is returned in a ub8 variable by calling OCIAttrGet() with the OCI_ATTR_CALL_TIME attribute. Example 10-13 shows how to do this in a code fragment.

boolean enable_call_time;
ub8 call_time;
...
enable_call_time = TRUE;
OCIAttrSet(session, OCI_HTYPE_SESSION, (void *)&enable_call_time,
           (ub4)0, OCI_ATTR_COLLECT_CALL_TIME,
           (OCIError *)error_handle);
OCIStmtExecute(...);
OCIAttrGet(session, OCI_HTYPE_SESSION, (void *)&call_time,
           (ub4)0, OCI_ATTR_CALL_TIME, 
           (OCIError *)error_handle);
...

Use these attributes for monitoring, tracing, and debugging applications.

Set these attributes for monitoring, tracing, and debugging applications:

• OCI_ATTR_MODULE - Name of the current module in the client application.

• OCI_ATTR_ACTION - Name of the current action within the current module. Set to NULL if you do not want to specify an action.

• OCI_ATTR_DBOP - Name of the database operation set by the client application to be monitored in the database. Set to NULL if you want to end monitoring the current running database operation.

• OCI_ATTR_CLIENT_INFO - Client application additional information.

  See Also:

  User Session Handle Attributes

  Monitoring Database Operations in Oracle Database SQL Tuning Guide

  Monitoring Real-Time Database Performance in Oracle Database 2 Day + Performance Tuning Guide

OCI supports polling mode operations.

When OCI is operating in threaded mode, OCI calls that poll for completion acquire mutexes when the OCI call is actively executing. However, when OCI returns control to the application, OCI releases any acquired mutexes. The caller should ensure that no other OCI call is made on the connection until the polling mode OCI operation in progress completes.

If an application is mixing later release and 7.x OCI calls, and the application has been initialized as thread-safe (with the appropriate calls of the later release), it is not necessary to call opinit() to achieve thread safety.

The application gets 7.x behavior on any subsequent 7.x function calls.

Most calls to OCIThread functions use the OCI environment or user session handle as a parameter.

The OCIThread context is part of the OCI environment or user session handle, and it must be initialized by calling OCIThreadInit(). Termination of the OCIThread context occurs by calling OCIThreadTerm().

The OCIThreadMutex data type is used to represent a mutex.

This mutex is used to ensure that either:

• Only one thread accesses a given set of data at a time

• Only one thread executes a given critical section of code at a time

Mutex pointers can be declared as parts of client structures or as standalone variables. Before they can be used, they must be initialized using OCIThreadMutexInit(). Once they are no longer needed, they must be destroyed using OCIThreadMutexDestroy().

A thread can acquire a mutex by using OCIThreadMutexAcquire(). This ensures that only one thread at a time is allowed to hold a given mutex. A thread that holds a mutex can release it by calling OCIThreadMutexRelease().

The data type OCIThreadKey can be thought of as a process-wide variable with a thread-specific value.

Thus all threads in a process can use a given key, but each thread can examine or modify that key independently of the other threads. The value that a thread sees when it examines the key is always the same as the value that it last set for the key. It does not see any values set for the key by other threads. The data type of the value held by a key is a void * generic pointer.

Keys can be created using OCIThreadKeyInit(). Key value are initialized to NULL for all threads.

A thread can set a key's value using OCIThreadKeySet(). A thread can get a key's value using OCIThreadKeyGet().

The OCIThread key functions save and retrieve data specific to the thread. When clients maintain a pool of threads and assign them to different tasks, it may not be appropriate for a task to use OCIThread key functions to save data associated with it.

Here is a scenario of how things can fail: A thread is assigned to execute the initialization of a task. During initialization, the task stores data in the thread using OCIThread key functions. After initialization, the thread is returned to the threads pool. Later, the threads pool manager assigns another thread to perform some operations on the task, and the task must retrieve the data it stored earlier in initialization. Because the task is running in another thread, it is not able to retrieve the same data. Application developers that use thread pools must be aware of this.

OCIThreadKeyDestFunc is the type of a pointer to a key's destructor routine.

Keys can be associated with a destructor routine when they are created using OCIThreadKeyInit(). A key's destructor routine is called whenever a thread with a non-NULL value for the key terminates. The destructor routine returns nothing and takes one parameter, the value that was set for key when the thread terminated.

The destructor routine is guaranteed to be called on a thread's value in the key after the termination of the thread and before process termination. No more precise guarantee can be made about the timing of the destructor routine call; no code in the process may assume any post-condition of the destructor routine. In particular, the destructor is not guaranteed to execute before a join call on the terminated thread returns.

OCIThreadId data type is used to identify a thread.

At any given time, no two threads can have the same OCIThreadId, but OCIThreadId values can be recycled; after a thread dies, a new thread may be created that has the same OCIThreadId value. In particular, the thread ID must uniquely identify a thread T within a process, and it must be consistent and valid in all threads U of the process for which it can be guaranteed that T is running concurrently with U. The thread ID for a thread T must be retrievable within thread T. This is done using OCIThreadIdGet().

The OCIThreadId type supports the concept of a NULL thread ID: the NULL thread ID can never be the same as the ID of an actual thread.

Data type OCIThreadHandle is used to manipulate a thread in the active primitives, OCIThreadJoin() and OCIThreadClose().

A thread handle opened by OCIThreadCreate() must be closed in a matching call to OCIThreadClose(). A thread handle is invalid after the call to OCIThreadClose().