Progress
Embedded SQL-92
Guide and Reference
Transaction 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 ESQL program.
Progress Software recommends that you specify the transaction isolation level number by isolation_level_name.
This is the syntax for the SET TRANSACTION ISOLATION LEVEL statement:
isolation_level_name
The ANSI/ISO SQL standard defines isolation levels in terms of the inconsistencies they allow:
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 remains the same.
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 talbe lock is held until the end of the current transaction.
NOTES
For more information on setting isolation levels in transactions, see SET TRANSACTION ISOLATION LEVEL in Chapter 3, “SQL-92 Statements,” of the Progress SQL-92 Guide and Reference .
EXAMPLEThe following code fragment illustrates how to execute a query at the READ COMMITTED isolation level:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |