Isolation Level

Go Up to Specifying SET TRANSACTION Behavior

The isolation level parameter specifies the control a transaction exercises over table access. It determines the:

• View of a database the transaction can see.
• Table access allowed to this and other simultaneous transactions.

The following table describes the three isolation levels supported by InterBase:

ISOLATION LEVEL options

Isolation level	Purpose
SNAPSHOT	Provides a stable, committed view of the database at the time the transaction starts; this is the default isolation level. Other simultaneous transactions can UPDATE and INSERT rows, but this transaction cannot see those changes. For updated rows, this transaction sees versions of those rows as they existed at the start of the transaction. If this transaction attempts to update or delete rows changed by another transaction, an update conflict is reported.
SNAPSHOT TABLE STABILITY	Provides a transaction sole insert, update, and delete access to the tables it uses. Other simultaneous transactions may still be able to select rows from those tables.
READ COMMITTED	Enables the transaction to see all committed data in the database, and to update rows updated and committed by other simultaneous transactions without causing lost update problems.

The isolation level for most transactions should be either SNAPSHOT or READ COMMITTED. These levels enable simultaneous transactions to select, insert, update, and delete data in shared databases, and they minimize the chance for lock conflicts. Lock conflicts occur in two situations:

• When a transaction attempts to update a row already updated or deleted by another transaction. A row updated by a transaction is effectively locked for update to all other transactions until the controlling transaction commits or rolls back. READ COMMITTED transactions can read and update rows updated by simultaneous transactions after they commit.
• When a transaction attempts to insert, update, or delete a row in a table locked by another transaction with an isolation level of SNAPSHOT TABLE STABILITY. SNAPSHOT TABLE STABILITY locks entire tables for write access, although concurrent reads by other SNAPSHOT and READ COMMITTED transactions are permitted.

Using SNAPSHOT TABLE STABILITY guarantees that only a single transaction can make changes to tables, but increases the chance of lock conflicts where there are simultaneous transactions attempting to access the same tables. For more information about the likelihood of lock conflicts, see Isolation level interactions.

Topics

• Comparing SNAPSHOT, READ COMMITTED, and SNAPSHOT TABLE STABILITY
• Choosing Between SNAPSHOT and READ COMMITTED
• Starting a Transaction with SNAPSHOT Isolation Level
• Starting a transaction with READ COMMITTED isolation level
• Starting a transaction with SNAPSHOT TABLE STABILITY isolation level
• Isolation level interactions