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:

SYNTAX
SET TRANSACTION ISOLATION LEVEL isolation_level_name ; 

isolation_level_name

SYNTAX
READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE 

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 .

EXAMPLE

The following code fragment illustrates how to execute a query at the READ COMMITTED isolation level:

EXEC SQL CONNECT TO DEFAULT ;
 
EXEC SQL WHENEVER SQLERROR GOTO do_rollback ;
 
EXEC SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
 
EXEC SQL
     SELECT last_name, city
     INTO :cust_no_v, :name_v
     FROM customer
     WHERE cust_no = 1024 ;
   .
   .
   .
 
EXEC SQL COMMIT WORK ;
EXEC SQL DISCONNECT DEFAULT ;
exit (0) ;
 
do_rollback:
     if (sqlca.sqlcode < 0)
     {
          strncpy (errmesg, sqlca.sqlerrm, sqlca.sqlerrml);
          errmesg [sqlca.sqlerrml] = ’\0’ ;
          printf ("Error : %s\n", errmesg);
     }
 
EXEC SQL WHENEVER SQLERROR CONTINUE ;
EXEC SQL ROLLBACK WORK ;
EXEC SQL DISCONNECT DEFAULT ;
exit (1) ; 


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