Specifying Isolation Levels

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 isolation levels are in increasing order of isolation:

• Read Uncommitted

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

  Table 1. Read Uncommitted database types and equivalent isolation levels

  Database type	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

  A transaction can access only rows committed by other transactions.

  Table 2. Read committed database types and equivalent isolation levels

  Database type	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

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

  Table 3. Cursor stability database types and equivalent isolation levels

  Database type	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

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

  Table 4. Reproducible read database types and equivalent isolation levels

  Database type	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

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

  Table 5. Phantom protection database types and equivalent isolation levels

  Database type	Equivalent isolation level
  Oracle	Not applicable
  DB2	Not applicable
  Microsoft SQL Server	Not applicable
  Sybase Adaptive Server Enterprise	Not applicable
  Informix	Not applicable

• Serializable

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

  Table 6. Serializable database types and equivalent isolation levels

  Database Type	Equivalent isolation level
  Oracle	Serializable
  DB2	Repeated read
  Microsoft SQL Server	Serializable
  Sybase Adaptive Server Enterprise	Serializable
  Informix	Not applicable