Maintain data integrity with isolation levels
The isolation level defines the degree of isolation for an application process from other application processes that are running concurrently.
You can maintain data integrity for a data source table by requesting that the table rows be locked at a specific isolation level.
Locking occurs at the base table row at the data source. The database manager, however, can replace multiple row locks with a single table lock. This action is called lock escalation. An application process is guaranteed at least the minimum requested lock level.
- RR
- Repeatable read
- RS
- Read stability
- CS
- Cursor stability (default)
- UR
- Uncommitted read
The types of isolation are the statement level isolation and connection level isolation.
You can set the isolation when you perform the following actions:
- Precompile or bind an application. You can specify isolation levels when you prepare or bind an application. The isolation level specified in the BIND and PREP command is the default isolation level when the federated server connects to the remote data source.
- Use the WITH clause in an SQL statement. This action is called statement level isolation. You can use the WITH clause in the SELECT, UPDATE, INSERT, and DELETE statements.
If the federated server does not find an isolation level for a statement, the federated server uses the isolation level that was established when the federated server connected to the data source.
The following table lists the data sources that use connection level isolation, the isolation levels that they use, and the equivalent isolation levels on the federated server.
| Data sources | Most restrictive isolation level | More restrictive isolation level | Less restrictive isolation level | Least restrictive isolation level |
|---|---|---|---|---|
| Federated database | Repeatable read | Read stability | Cursor stability | Uncommitted read |
| Db2® family of products | Repeatable read | Read stability* | Cursor stability | Uncommitted read |
| Informix® | Repeatable read | Repeatable read | Cursor stability | Dirty read |
| JDBC | Serializable | Repeatable read | Read committed | Read Uncommitted |
| Microsoft SQL Server | Serializable | Repeatable read | Read committed | Read Uncommitted |
| ODBC | Serializable | Repeatable read | Read committed | Read Uncommitted |
| Oracle | Serializable | Serializable | Read committed | Read committed |
| Sybase | Level 3 | Level 3 | Level 1 | Level 0 |
| *For Db2 for VM and VSE Server data sources, the isolation level is repeatable read. | ||||
The CURRENT ISOLATION special register is not used by the federated server when it connects to a data source.
The nonrelational data sources do not have a concept like isolation levels. Teradata does have the concept of isolation levels but they are not supported by the federated server. There is no isolation-level mapping between the federated database isolation levels and the Teradata and nonrelational data sources.