Transaction Processing
The DB Access Module for PostgreSQL implements the model of transaction processing of the DB Interface Module with the SQL statement "SET SESSION CHARACTERSTICS AS TRANSACTION ISOLATION LEVEL" and the PostgreSQL language extensions "BEGIN", "COMMIT", and "ROLLBACK."
There are two ways to control transactions through PostgreSQL:
• setting isolation levels
• using transaction control methods
These methods are described below.
Setting Isolation Levels
PostgreSQL supports two levels of settings for transaction isolations levels, one at the session level via the SQL statement "set session characteristics as transaction isolation level " and the other at the transaction level via the SQL statement "set transaction".
The method
isolation()of class
RWDBConnection sets the session isolation level. Table 5 shows the mapping between the argument you pass for
IsolationType, and the isolation level set by the PostgreSQL Access Module.
Table 5 – Setting the isolation level
RWDBConnection:: IsolationType | PostgreSQL Isolation Level |
Unknown | (not supported by PostgreSQL) |
ANSILevel1 | read committed |
ANSILevel2 | (not supported by PostgreSQL) |
ANSILevel3 | serializable |
To determine the current isolation level, call RWDBConnection::isolation() without an argument.
Using Transaction Control Methods
You can explicitly control transactions through the following methods:
RWDBConnection::beginTransaction()
RWDBConnection::rollbackTransaction()
RWDBConnection::commitTransaction()
An application can add the DB Interface Module transaction methods to its code to take explicit control of its transaction blocks. The following code demonstrates how these methods can be used to commit or to rollback transactions.
Example 6 – Using transaction control methods
// 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.commitTransaction(); // Commit transaction
cn1.beginTransaction(); // Begin another transaction
(ins << 2 ).execute(cn1); // Second insertion
(ins << 3 ).execute(cn1); // Third insertion
cn1.rollbackTransaction(); // Rollback transaction erasing
// second and third insertions..
// The above program results in myTable holding one row of data.
// Its value is 1.
The DB Access Module for PostgreSQL does not currently support savepoint. Attempting to use the savepoint features of the DB Interface Module API generates the error RWDBStatus::notSupported for the connection that attempted to create it.