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

• by setting isolation levels

• by using transaction control methods

2.11.1 Setting Isolation Levels

When connecting to Oracle 10g server, RWDBConnection::isolation() returns the isolation level of the server. When connecting to Oracle11g server, RWDBConnection::isolation() returns the isolation level of the connection to the server rather than the server's default isolation level.

Note: To determine the isolation level of an Oracle 10g 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 entry for V$PARAMETER in the Oracle 10g Server Administrator's Guide.

Note: To determine the isolation level of a connection to an Oracle11g server, the system table V$SES_OPTIMIZER_ENV is read.

Table 5 shows the mapping between the Oracle 10g server configuration and the isolation levels returned:

Table 5: Isolation levels of Oracle 10g server

Please note that the Oracle server initialization parameter row_locking is obsolete in Oracle 10g. Hence, Oracle has marked it as hidden, adding an underscore in front of its name, and reporting a warning in the log when the server is started. Please see Oracle10g documentation for more information.

Table 6 shows the mapping between the Oracle11g session parameter and the isolation levels returned:

Table 6: Isolation levels of Oracle11g

Beginning with Oracle11g, a new column, transaction_isolation_level, in the dynamic performance view V$SES_OPTIMIZER_ENV is available to determine the transaction level of a connection to the Oracle server.

2.11.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 OCITransCommit(), OCITransRollback(), and SQL statements.

An application can add the DB Interface Module 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.

---

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 following example demonstrates the use of the savepoint feature and the other transaction processing methods of the DB Interface Module.

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