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:
Provides a stable, committed view of the database at the time the transaction starts; this is the default isolation level. Other simultaneous transactions can
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.
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
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 COMMITTEDtransactions 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 STABILITYlocks entire tables for write access, although concurrent reads by other
READ COMMITTEDtransactions are permitted.
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.
- 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