Progress
SQL-92
Guide and Reference


SET TRANSACTION ISOLATION LEVEL Statement

Explicitly sets the isolation level for a transaction. Isolation levels specify the degree to which one transaction can modify data or database objects in use by another concurrent transaction.

SYNTAX

SET TRANSACTION ISOLATION LEVEL isolation_level_name ; 

isolation_level_name:

SYNTAX
READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE 

READ UNCOMMITTED

Allows dirty reads, nonrepeatable reads, and phantoms. When a record is read, no record locks are aquired. This forces read-only use. Allows a user application to read records that were modified by other applications but have not yet been committed.

READ COMMITTED

Prohibits dirty reads; allows nonrepeatable reads and phantoms. Whenever a record is read, a share lock is acquired on that record. The duration of the lock varies. Disallows the reading of uncommitted modified records. However, if a record is read multiple times in the same transaction, the state of the record can be different each time.

REPEATABLE READ

Prohibits dirty reads and nonrepeatable reads; allows phantoms. Whenever a record is read, a share lock is acquired on that record and held until the end of the current transaction. Disallows the reading of uncommitted modified records. If a record is read multiple times in the same transaction, the state of the record remains the same.

REPEATABLE READ is the default isolation level.

SERIALIZABLE

Prohibits dirty reads, nonrepeatable reads, and phantoms. If an application executes the same SELECT statement more than once within the same transaction, the same set of rows is retrieved every time. Guarantees that concurrent transactions will not affect each other, and that they will behave as if they were executing serially, not concurrently.

Whenever a table is accessed, the entire table is locked with an appropriate lock. The table lock is held until the end of the current transaction.

NOTES

AUTHORIZATION

None

SQL COMPLIANCE

SQL-92. The semantics to which it corresponds are standard.

The isolation level SERIALIZABLE guarantees the highest consistency. The isolation level READ UNCOMMITTED guarantees the least consistency. The default isolation level is REPEATABLE READ, which prohibits non-repeatable read operations. The ANSI/ISO SQL standard defines isolation levels in terms of the inconsistencies they allow:

Dirty read

Allows the transaction to read a row that has been inserted or modified by another transaction, but not committed. If the other transaction rolls back its changes, the transaction will read a row that never existed because it never committed.

Nonrepeatable read

Allows the transaction to read a row that another transaction modifies or deletes before the next read operation. If the other transaction commits the change, the transaction receives modified values or discovers the row is deleted on subsequent read operations.

Phantom

Allows the transaction to read a range of rows that satisfies a given search condition, but to which another transaction adds rows before another read operation using the same search condition. The transaction receives a different collection of rows with the same search condition.

ENVIRONMENT

Embedded SQL only, interactive SQL

RELATED STATEMENTS

COMMIT Statement, LOCK TABLE Statement, ROLLBACK Statement


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