Specifying Isolation Levels

You can specify 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