Release 10.1A: OpenEdge Data Management:
SQL Development

> ## Setting isolation levels

> The degree to which one transaction can interfere with other transactions by accessing the same rows concurrently is determined by setting the transaction isolation level in the program.

> This is the syntax for the `SET TRANSACTION ISOLATION LEVEL` statement:

> Syntax
>     SET TRANSACTION ISOLATION LEVEL isolation_level_name ;

> `isolation_level_name`

> Syntax
>     READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

> SQL defines isolation levels in terms of the inconsistencies they allow:

> `READ UNCOMMITTED`

> Also called a “dirty read.” When this isolation level is used, a transaction can read uncommitted data that later can be rolled back. A transaction that uses this isolation level can only fetch data but cannot update, delete, or insert data.

> `READ COMMITTED`

> With this isolation level dirty reads are not possible, but if the same row is read repeatedly during the same transaction, its contents can be changed or the entire row can be deleted by other transactions.

> `REPEATABLE READ`

> This isolation level guarantees that a transaction can read the same row many times, and it will remain intact. However, if a query with the same search criteria (the same `WHERE` clause) is executed more than once, each execution can return different sets of rows. This can happen because other transactions are allowed to insert new rows that satisfy the search criteria or update some rows in such way that they now satisfy the search criteria.

> `SERIALIZABLE`

> This isolation level guarantees that none of the above happens. In addition, it guarantees that transactions that use this level will be completely isolated from other transactions.

> Table 8–1 identifies which phenomena are either permitted or prevented by each isolation level.

> Table 8–1: Transaction isolation levels
> Isolation | Dirty read | Nonrepeatable read | Phantom read
> --- | --- | --- | ---
>     READ UNCOMMITTED
> | Permitted | Permitted | Permitted
>     READ COMMITTED
> | Prevented | Permitted | Permitted
>     REPEATABLE READ
> | Prevented | Prevented | Permitted
>     SERIALIZABLE
> | Prevented | Prevented | Prevented

> Notes: The isolation levels are ordered according to the phenomena they either permit or prevent.
> The first one, `READ UNCOMMITTED,` is the isolation level providing the highest level of concurrency but with the lowest level of consistency.
> Each subsequent level provides at least as much data consistency as the one before but will result in less concurrency.
> As a general rule, the more data consistency that is provided by the isolation level used from an application, the less concurrency is allowed between this application and other applications connected to the same database.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095