2.10 Transaction Processing

Transactions can be controlled through Oracle OCI in two different ways:

by setting isolation levels
by using transaction control methods

Each of these techniques is described in the sections below.

2.10.1 Setting Isolation Levels

RWDBConnection::isolation() can be used to determine the isolation level of the connected server. However an application cannot change the isolation level of an Oracle server directly, as isolation levels are set when the Oracle server is first initialized. In order to change the isolation level, the initialization parameters of the Oracle server must be changed and the server restarted.

Note: To determine the isolation level of the Oracle server, the database user must have permissions to read V$PARAMETER system table. Typically, this is only available to the system account, SYS; however, access can be safely granted to other users by running UTLMONITOR.SQL, which is provided by Oracle. For more information see the Oracle Server Administrator's Guide on V$PARAMETER. Table 5 shows the mapping between the Oracle server configuration and the isolation levels returned:

Table 5 -- Isolation levels of Oracle servers

Oracle Configuration Parameters	RWDBConnection::IsolationType
`serializable = FALSE`	ANSILevel1
`serializable = TRUE row_locking != INTENT`	ANSILevel2
`serializable = TRUE row_locking = INTENT`	ANSILevel3

2.10.2 Using Transaction Control Methods

You can explicitly control transactions through the following methods:

RWDBConnection::beginTransaction()
RWDBConnection::rollbackTransaction()
RWDBConnection::rollbackTransaction(const RWCString& savepoint)
RWDBConnection::commitTransaction()
RWDBConnection::setSavepoint(const RWCString& savepoint)

These methods have straightforward implementations that correspond to OCI calls like ocom() or orol(), and SQL statements.

An application can add the DBTools.h++ transaction methods to its code to take explicit control of its transaction blocks. However, transactions may not be nested as this feature is not supported by Oracle.

NOTE: Oracle does not support nested transactions.

The savepoint feature is supported. The savepoint feature allows a current transaction to be partially rolled back to a marked point. The example below demonstrates the use of the savepoint feature and the other transaction processing methods of DBTools.h++.

// Assume we have a table myTable(c int) with no rows in it.
RWDBInserter ins= myTable.inserter(); 
cn1.beginTransaction ();           // Begin transaction..

(ins << 1 ).execute(cn1);          // First insertion
...
cn1.setSavepoint("svp1");          // Save first insertion

(ins << 2 ).execute(cn1);          // Second insertion
(ins << 3 ).execute(cn1);          // Third insertion
cn1.rollbackTransaction("svp1");   // Roll back second and 
                                   // third insertions
cn1.commitTransaction();           // Commit transaction on 
                                   // part that is not rolled back

// The previous program results in myTable holding one row of data.
// Its value is 1.