Isolation levels

You can specify isolation levels for data sources.

The isolation level specifies how transactions that modify the database are handled. By default, the default object gateway is used. Not all types of databases support each isolation level. Some database vendors use different names for the isolation levels.

Queries that are executed by reports and analysis are intended to be read-only operations. The queries execute with a unit of work at the data source known as a transaction with either a default or administrator-defined isolation level. Report authors should not assume that queries that execute stored procedures commit any data written by the procedure. In some environments, changes made by a procedure may be committed due to features of the database. A stored procedure that is marked for-write in Framework Manager commits changes but can only be used by Event Studio.

If you need specific queries to run with different isolation levels, you must define different database connections.

For OLAP data sources, including SAP BW, the transaction unit of work is read-only.

The following sections list the isolation levels in increasing order of isolation. Each section contains a description of the isolation level and information about equivalent isolation levels in different databases.

Read Uncommitted

Description

Changes made by other transactions are immediately available to a transaction.

Equivalent isolation level for different databases

The following table lists, for example databases, isolation levels that are equivalent to Read Uncommitted.

Tip: To find out isolation levels equivalent to Read Uncommitted for databases not listed in the table, see the database vendor's JDBC driver and server documentation.

Table 1. Read Uncommitted databases and equivalent isolation levels
Database	Equivalent isolation level
Oracle	Not applicable
Db2®	Uncommitted read
Microsoft SQL Server	Read uncommitted
Sybase Adaptive Server Enterprise	Read uncommitted
Informix®	Dirty read

Read Committed

Description

A transaction can access only rows committed by other transactions.

Equivalent isolation level for different databases

The following table lists, for example databases, isolation levels that are equivalent to Read Committed.

Tip: To find out isolation levels equivalent to Read Committed for databases not listed in the table, see the database vendor's JDBC driver and server documentation.

Table 2. Read committed databases and equivalent isolation levels
Database	Equivalent isolation level
Oracle	Read committed
Db2	Cursor stability
Microsoft SQL Server	Read committed
Sybase Adaptive Server Enterprise	Read committed
Informix	Committed read

Cursor Stability

Description

Other transactions cannot update the row in which a transaction is positioned.

Equivalent isolation level for different databases

The following table lists, for example databases, isolation levels that are equivalent to Cursor Stability.

Tip: To find out isolation levels equivalent to Cursor Stability for databases not listed in the table, see the database vendor's JDBC driver and server documentation.

Table 3. Cursor stability databases and equivalent isolation levels
Database	Equivalent isolation level
Oracle	Not applicable
Db2	Not applicable
Microsoft SQL Server	Not applicable
Sybase Adaptive Server Enterprise	Not applicable
Informix	Cursor stability

Reproducible Read

Description

Rows selected or updated by a transaction cannot be changed by another transaction until the transaction is complete.

Equivalent isolation level for different databases

The following table lists, for example databases, isolation levels that are equivalent to Reproducible Read.

Tip: To find out isolation levels equivalent to Reproducible Read for databases not listed in the table, see the database vendor's JDBC driver and server documentation.

Table 4. Reproducible read databases and equivalent isolation levels
Database	Equivalent isolation level
Oracle	Not applicable
Db2	Read stability
Microsoft SQL Server	Repeatable read
Sybase Adaptive Server Enterprise	Repeatable read
Informix	Repeatable read

Phantom Protection

Description

A transaction cannot access rows inserted or deleted since the start of the transaction.

Equivalent isolation level for different databases

The following table lists, for example databases, isolation levels that are equivalent to Phantom Protection.

Tip: To find out isolation levels equivalent to Phantom Protection for databases not listed in the table, see the database vendor's JDBC driver and server documentation.

Table 5. Phantom protection databases and equivalent isolation levels
Database	Equivalent isolation level
Oracle	Not applicable
Db2	Not applicable
Microsoft SQL Server	Not applicable
Sybase Adaptive Server Enterprise	Not applicable
Informix	Not applicable

Serializable

Description

A set of transactions executed concurrently produces the same result as if they were performed sequentially.

Equivalent isolation level for different databases

The following table lists, for example databases, isolation levels that are equivalent to Serializable.

Tip: To find out isolation levels equivalent to Serializable for databases not listed in the table, see the database vendor's JDBC driver and server documentation.

Table 6. Serializable databases and equivalent isolation levels
Database	Equivalent isolation level
Oracle	Serializable
Db2	Repeated read
Microsoft SQL Server	Serializable
Sybase Adaptive Server Enterprise	Serializable
Informix	Not applicable