2.8 Transaction Processing

There are two ways to control transactions through DB-Library:

setting isolation levels
using transaction control methods

Each of these techniques is described below.

2.8.1 Setting Isolation Levels

You can use the isolation() method of RWDBConnection to set the isolation level of the SQL server. Table 5 shows the mapping between the IsolationType argument you pass and the isolation level set by the SQL server. Take special care when setting isolation levels as this effects all users of the SQL server.

Table 5 -- Setting the isolation level

RWDBConnection::IsolationType	Sybase Isolation Level
Unknown	Level 1 - `DIRTY READ`
ANSILevel1	Level 1 - `DIRTY READ`
ANSILevel2	Level 1 - `DIRTY READ` (Sybase does not support isolation level 2.)
ANSILevel3	Level 3 - `REPEATABLE READ`

To determine the current isolation level call RWDBConnection::isolation() without an argument.

2.8.2 Using Transaction Control Methods

You can explicitly control transactions through the following methods:

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

These methods have straightforward implementations that correspond to the following SQL statements:

"begin transaction"
"rollback transaction"
"rollback transaction savepoint"
"commit transaction"
"save transaction savepoint"

An application can add the DBTools.h++ transaction methods to its code to take explicit control of its transaction blocks. Transactions may be nested and 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(); 

cn2.beginTransaction("outmost");   // Begin transaction. 
                                   // Transaction name is allowed.
                                   // Note: unlike savepoint name,
                                   // transaction name is not used
                                   // by server.

(ins << 1 ).execute(cn2);          // First insertion
cn2.beginTransaction("inner");     // Nested transaction begins
...
cn2.setSavepoint("svp1");          // Save first insertion

(ins << 2 ).execute(cn2);          // Second insertion
(ins << 3 ).execute(cn2);          // Third insertion

cn2.rollbackTransaction("svp1");   // Roll back second and 
                                   // third insertions.
cn2.commitTransaction("inner");    // Pairing inner transaction
cn2.commitTransaction("outmost");  // Commit transaction on
                                   // part that is not rolled back.

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