2.10 Transaction Processing

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

by setting isolation levels
by using transaction control methods

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 Oracle8 server directly, as isolation levels are set when the Oracle8 server is first initialized. In order to change the isolation level, the initialization parameters of the Oracle8 server must be changed and the server restarted.

Note: To determine the isolation level of the Oracle8 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 Oracle8. For more information, see the Oracle8 Server Administrator's Guide on V$PARAMETER. Table 5 shows the mapping between the Oracle8 server configuration and the isolation levels returned:

Table 5 -- Isolation levels of Oracle8 servers

Oracle8 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 above program results in myTable holding one row of data.
// Its value is 1.