Oracle Call Interface Programmer's Guide
Release 8.1.5






Prev Next

Managing Scalable Platforms

The following topics are covered in this chapter:


Chapter 2, "OCI Programming Basics" introduced the basic concepts of OCI programming, including how a simple transactions are processed and how the OCISessionBegin() call is used as part of OCI initialization. This chapter is designed to introduce more advanced concepts, including the following:


Release 8i of the Oracle Call Interface provides a set of API calls to support operations on both local and global transactions. These calls include object support, so that if an OCI application is running in object mode, the commit and rollback calls will synchronize the object cache with the state of the transaction.

The functions listed below perform transaction operations. Each call takes a service context handle that should be initialized with the proper server context and user session handle. The transaction handle is the third element of the service context; it stores specific information related to a transaction. When a SQL statement is prepared, it is associated with a particular service context. When the statement is executed, its effects (query, fetch, insert) become part of the transaction that is currently associated with the service context.

Depending on the level of transactional complexity in your application, you may need all or only a few of these calls. The following section discusses this in more detail.

See Also: For more specific information about these calls, refer to the function descriptions in Chapter 15, "OCI Relational Functions".

Levels of Transactional Complexity

The OCI supports several levels of transaction complexity. Each level is described in one of the following sections.

Simple Local Transactions

Many applications work with only simple local transactions. In these applications, an implicit transaction is created when the application makes database changes. The only transaction-specific calls needed by such applications are:

As soon as one transaction has been committed or rolled back, the next modification to the database creates a new implicit transaction for the application.

Only one implicit transaction can be active at any time on a service context. Attributes of the implicit transaction are opaque to the user.

If an application creates multiple sessions, each one can have an implicit transaction associated with it.

For sample code showing the use of simple local transactions, refer to the example for OCITransCommit().

Serializable or Read-Only Local Transactions

Applications requiring serializable or read-only transactions require an additional OCI call beyond those needed by applications operating on simple local transactions. To initiate a serializable or read-only transactions, the application must create the transaction by calling OCITransStart() to start the transaction.

The call to OCITransStart() should specify OCI_TRANS_SERIALIZABLE or OCI_TRANS_READONLY, as appropriate, for the flags parameter. If no flag is specified, the default value is OCI_TRANS_READWRITE for a standard read-write transaction.

Specifying the read-only option in the OCITransStart() call saves the application from performing a server round-trip to execute a SET TRANSACTION READ ONLY statement.

Global Transactions

Global transactions are necessary only in more sophisticated transaction-processing applications.

Note: Users not operating in distributed or global transaction environments may skip this section.

This section provides some background about global transactions, and then gives specific information about using OCI calls to process global transactions.

Transaction Identifiers

Three-tiered applications such as transaction processing (TP) monitors create and manage global transactions. They supply a global transaction identifier (XID), which a server then 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.

For example, the following is the structure for one possible XID of 1234:

Component   Value  







See Also: For more information about transaction identifiers, refer to the Oracle8i Distributed Database Systems manual.

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

Transaction Branches

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

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

In Oracle OCI release 8i, a session corresponds to a user session, created with OCISessionBegin().

The following figure illustrates tightly coupled branches within an application. In the figure, S1 and S2, are sessions, B1 and B2 are branches, and T is a transaction. In this first example, the XIDs of the two branches would share the same gtrid, because they are operating on the same transaction, but they would have a different bqual, because they are separate branches

Figure 8-1 Multiple Tightly Coupled Branches

It is also possible for a single session to operate on different branches. In this case, illustrated in the next figure, gtrid component of the XIDs would be different, because they are separate global transactions

Figure 8-2 Session Operating on Multiple Branches

For sample code demonstrating this scenario, refer to the examples for OCITransStart(). 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. Sample code demonstrating this scenario can be found in the examples for OCITransStart().

The following figure illustrates loosely coupled branches:

Figure 8-3 Loosely Coupled Branches

Branch States

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.

Detaching and Resuming Branches

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

When an application wants to resume a branch, it 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 will wait 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.

Note: A transaction can be resumed by a different process than the one that detached it, as long as that process has the same authorization as the one that detached the transaction.

Setting Client Database Name

The server handle has OCI_ATTR_EXTERNAL_NAME and OCI_ATTR_INTERNAL_NAME attributes associated with it. These attributes set the client database name that will be recorded when performing global transactions. The name can be used by the DBA to track transactions that may be pending in a prepared state due to failures.

Warning: An OCI application should set these attributes, using OCIAttrSet(), before logging on and using global transactions.

One-Phase Versus Two-Phase Commit

Global transactions may 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 databases or multiple Oracle servers. In this case, a two-phase commit is necessary. A two-phase commit consists of these steps:

  1. Prepare - The application issues a prepare call, OCITransPrepare() against each transaction. The transaction returns a value indicating whether it is able to commit its current work (OCI_SUCCESS) or not (OCI_ERROR).

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

    Note: The prepare call can also return OCI_SUCCESS_WITH_INFO if a transaction needs to indicate that it is read-only, so that a commit is neither appropriate nor necessary.

An additional call, OCITransForget() indicates that a database should forget a heuristically completed transaction. This call is for situations in which a problem has occurred that requires that a two-phase commit be aborted. When a server receives a OCITransForget() call, it forgets all information about the transaction.

See Also: For more information about two-phase commit, refer to the Oracle8i Distributed Database Systems manual.

Transaction Examples

This section provides examples of how to use the transaction OCI calls. The following tables provide series of OCI calls and other actions, along with their resulting behavior. For the sake of simplicity, not all parameters to these calls are listed; rather, the flow of calls which is being demonstrated.

The OCI Action column indicates what the OCI application is doing, or what call it is making. The XID column lists the transaction identifier, when necessary. The Flags column lists the value(s) passed in the flags parameter. The Result column describes the result of the call.

Update Successfully, One-Phase Commit

Step   OCI Action   XID   Flags   Result  





Starts new read-write transaction  





Update rows  





Commit succeeds  

Start a Transaction, Detach, Resume, Prepare, Two-Phase Commit

Step   OCI Action   XID   Flags   Result  





Starts new read-only transaction  





Update rows  





Transaction is detached  





Transaction is resumed  










Transaction prepared for two-phase commit  





Transaction is committed.  

Note: In step 4, above, the transaction could have been resumed by a different process, as long as it had the same authorization.  

Read-Only Update Fails

Step   OCI Action   XID   Flags   Result  






Starts new read-only transaction  





Update fails, because transaction is read-only  





Commit has no effect  

Start a Read-Only Transaction, Select and Commit

Step   OCI Action   XID   Flags   Result  






Starts new read-only transaction  





Query database  





No effect -- transaction is read-only, no changes made  

Related Initialization Parameters

Two initialization parameters relate to the use of global transaction branches and migratable open connections:

Password and Session Management

Beginning with release 8, the OCI provides the ability to authenticate and maintain multiple users in an OCI application. There is also a new OCI call which allows the application to update a user's password. This is particularly helpful if an expired password message is returned by an authentication attempt.

Authentication Management

The OCISessionBegin() call is used to authenticate a user against the server set in the service context handle. For Oracle8i, OCISessionBegin() must be called for any given server handle before requests can be made against it. Also, OCISessionBegin() only supports authenticating the user for access to the Oracle server specified by the server handle in the service context that is used for the OCISessionBegin() call. In other words, after OCIServerAttach() is called to initialize a server handle, OCISessionBegin() must be called to authenticate the user for that given server identified by the server handle.

When OCISessionBegin() is called for the first time for a given server handle, the user session may not be created in migratable mode (OCI_MIGRATE). After OCISessionBegin() has been called for a server handle, the application may call OCISessionBegin() again to initialize another user session handle with different or the same credentials and different or the same operation modes. If an application wants to authenticate a user in OCI_MIGRATE mode, the service handle must already be associated with a non-migratable user handle. The user ID of that user handle becomes the ownership ID of the migratable user session. Every migratable session must have a non-migratable parent session.

If the OCI_MIGRATE mode is not specified, then the user session context can only be used with the same server handle that was used with the OCISessionBegin(). If OCI_MIGRATE mode is specified, then the user authentication may be set with different server handles. However, the user session context may only be used with server handles which resolve to the same database instance. Security checking is done during session switching.

A migratable session is allowed to switch to a different server handle only if the ownership ID of the session matches the user Id of a non-migratable session currently connected to that same server.

OCI_SYSDBA, OCI_SYSOPER, and OCI_PRELIM_AUTH may only be used with a primary user session context.

A migratable session can be switched, or migrated, to a server handle within a given environment represented by a environment handle. It can also be migrated, or cloned, to a server handle in another environment in the same process or in a different process in a different mode. To perform this migration, or cloning, you need to do the following:

  1. Extract the session Id from the session handle using OCI_ATTR_MIGSESSION. This is an array of bytes. It should not be modified by the caller. See OCI_ATTR_MIGSESSION.

  2. Transport this session Id to any other process by any means.

  3. In the new environment, create a session handle and set the session Id using OCI_ATTR_MIGSESSION.

  4. Execute OCISessionBegin(). The resulting session handle is a fully-authenticated session handle.

To provide credentials for a call to OCISessionBegin(), one of two methods are supported. The first is to provide a valid username and password pair for database authentication in the user session handle passed to OCISessionBegin(). This involves using OCIAttrSet() to set the OCI_ATTR_USERNAME and OCI_ATTR_PASSWORD attributes on the user session handle. Then OCISessionBegin() is called with OCI_CRED_RDBMS.

Note: When the user session handle is terminated using OCISessionEnd(), the username and password attributes remain unchanged and thus can be re-used in a future call to OCISessionBegin(). Otherwise, they must be reset to new values before the next OCISessionBegin() call.

The second type of credentials supported are external credentials. No attributes need to be set on the user session handle before calling OCISessionBegin(). The credential type is OCI_CRED_EXT. This is equivalent to the Oracle7 CONNECT syntax. If values have been set for OCI_ATTR_USERNAME and OCI_ATTR_PASSWORD, then these are ignored if OCI_CRED_EXT is used.

Password Management

OCI provides the OCIPasswordChange() call to allow an OCI application to modify a user's database password as necessary. This is particularly useful if a call to OCISessionBegin() returns an error message or warning indicating that a user's password has expired.

Following is the OCI call for changing a user's password:

OCIPasswordChange(service_handle, error_handle, user_name, user_name_len,
     old_password, old_password_len, new_password, new_password_len, mode);

Like OCISessionBegin(), OCIPasswordChange() can be called only after a server is attached, and the service handle has been set with the server handle and the user session handle. The effect of OCIPasswordChange() on a user session depends on whether or not the session is established before the call:

Session Management

Applications, such as transaction servers, that perform active user load balancing by multiplexing user sessions over a few server connections must group these connections into a server group. Oracle uses the server groups to identify these connections so that sessions can be managed effectively and securely.

The attribute OCI_ATTR_SERVER_GROUP must be defined for a server context to specify the server group name. For example:

OCIAttrSet ((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER, (dvoid *) group_name, 
            (ub4) strlen ((CONST char *)  group_name, 
            (ub4) OCI_ATTR_SERVER_GROUP, errhp);

The server group name is an alpha-numeric string not exceeding 30 characters. OCI_ATTR_SERVER_GROUP attribute must be set in the server context prior to creating the first non-migratable session using that context. After the session is created successfully and the connection is established to the server, the server group name cannot be changed. See OCI_ATTR_SERVER_GROUP.

All migratable sessions created on servers within a server group can only migrate to other servers in the same server group. Servers that terminate will get removed from the server group. New servers may be created within an existing server group at any time.

Server groups are optional. If no server group is specified, the server will get created in a server group called DEFAULT.

The owner of the first non-migratable session created in the first server in a server group other than DEFAULT establishes ownership of the server group. All subsequent non-migratable sessions for any server in this server group must be created by the same user as the owner of the server group.

The server group feature is useful when dedicated servers are used. It has no effect for MTS servers. In case of MTS, all shared servers will effectively belong to the server group DEFAULT.

Thread Safety

The thread safety feature of the Oracle database server and OCI libraries allows developers to use the OCI in a multi-threaded environment. With thread safety, OCI code can be reentrant, with multiple threads of a user program making OCI calls without side effects from one thread to another.

Note: Thread safety is not available on every platform. Check your Oracle system-specific documentation for more information.

The following sections describe how you can use the OCI to develop multi-threaded applications.

Advantages of OCI Thread Safety

The implementation of thread safety in the Oracle Call Interface provides the following benefits and advantages:

Thread Safety and Three-Tier Architectures

In addition to client-server applications, where the client can be a multithreaded program, a typical use of multithreaded applications is in three-tier (also called client-agent-server) architectures. In this architecture the client is concerned only with presentation services. The agent (or application server) processes the application logic for the client application. Typically, this relationship is a many-to-one relationship, with multiple clients sharing the same application server.

The server tier in this scenario is an Oracle database. The applications server (agent) is very well suited to being a multithreaded application server, with each thread serving a client application. In an Oracle environment this application server is an OCI or precompiler program.

Basic Concepts of Multi-threaded Development

Threads are lightweight processes that exist within a larger process. Threads share the same code and data segments but have their own program counters, machine registers, and stack. Global and static variables are common to all threads, and a mutual exclusivity mechanism may be required to manage access to these variables from multiple threads within an application.

Once spawned, threads run asynchronously to one another. They can access common data elements and make OCI calls in any order. Because of this shared access to data elements, a mechanism is required to maintain the integrity of data being accessed by multiple threads.

The mechanism to manage data access takes the form of mutexes (mutual exclusivity locks), which ensure that no conflicts arise between multiple threads that are accessing shared resources within an application. In Oracle OCI release 8, mutexes are granted on a per-environment-handle basis.

Implementing Thread Safety

In order to take advantage of thread safety in the Oracle OCI release 8, an application must be running on a thread-safe platform. Then the application must tell the OCI layer that the application is running in multithreaded mode, by specifying OCI_THREADED for the mode parameter of the opening call to OCIInitialize(), which must be the first OCI function called in the application.

Note: Applications running on non-thread-safe platforms should not pass a value of OCI_THREADED to OCIInitialize().

If an application is single-threaded, whether or not the platform is thread safe, the application should pass a value of OCI_DEFAULT to OCIInitialize(). Single-threaded applications which run in OCI_THREADED mode may incur performance hits.

If a multi-threaded application is running on a thread-safe platform, the OCI library will manage mutexing for the application on a per-environment-handle basis. If the application programmer desires, this application can override this feature and maintain its own mutexing scheme. This is done by specifying a value of OCI_NO_MUTEX to the OCIEnvInit() call.

The following three scenarios are possible, depending on how many connections exist per environment handle, and how many threads will be spawned per connection.

  1. If an application has multiple environment handles, but each only has one thread (one session exists per environment handle), no mutexing is required.

  2. If an application (running in OCI_THREADED mode) maintains multiple environment handles, each of which has one connection which can spawn multiple threads, the programmer has the following options:

    • Pass a value of OCI_NO_MUTEX for the mode of OCIEnvInit(). In this case the application must mutex OCI calls made on the same environment handle by itself. This has the advantage that the mutexing scheme can be optimized based on the application design. The programmer must also insure that only one OCI call is in process on the environment handle connection at any given time.

    • Pass a value of OCI_DEFAULT to OCIEnvInit(). In this case, the OCI library automatically gets a mutex on every OCI call on the environment handle.

  3. If an application running in OCI_THREADED mode maintains one or more environment handles, each of which has multiple connections, it also has the following options:

    • Pass a value of OCI_NO_MUTEX for the mode of OCIEnvInit(). In this case the application must mutex OCI calls by made on the same environment handle itself. This has the advantage that the mutexing scheme can be optimized based on the application design. The programmer must also insure that only one OCI call is in process on the environment handle connection at any given time.

    • Pass a value of OCI_DEFAULT to OCIEnvInit(). In this case, the OCI library automatically gets a mutex on every OCI call on the same environment handle.

    In this case, however, the programmer should be aware that if the application has two calls on the same environment handle, and one call operating on the server is mutexed, application performance can degrade if the mutexed call is long-running, thus tying up the server connection.

Mixing 7.x and 8.0 OCI calls

If an application is mixing 8.0 and 7.x OCI calls, and the application has been initialized as thread safe (with the appropriate 8.0 calls), it is not necessary to call opinit() to achieve thread safety. The application will get 7.x behavior on any subsequent 7.x function calls.


Copyright © 1999 Oracle Corporation.

All Rights Reserved.