FirebirdSQL logo
 Context VariablesSecurity 
SNAPSHOT Isolation Level

SNAPSHOT isolation level — the default level — allows the transaction to see only those changes that were committed before it was started.Any committed changes made by concurrent transactions will not be seen in a SNAPSHOT transaction while it is active.The changes will become visible to a new transaction once the current transaction is either committed or rolled back, but not if it is only a roll back to a savepoint.

The SNAPSHOT isolation level is also known as “concurrency”.

Note
Autonomous Transactions

Changes made by autonomous transactions are not seen in the context of the SNAPSHOT transaction that launched it.
Sharing Snapshot Transactions

Using SNAPSHOT AT NUMBER snaphot_number, a SNAPSHOT transaction can be started sharing the snapshot of another transaction.With this feature it’s possible to create parallel processes (using different attachments) reading consistent data from a database.For example, a backup process may create multiple threads reading data from the database in parallel, or a web service may dispatch distributed sub-services doing processing in parallel.

Alternatively, this feature can also be used via the API, using Transaction Parameter Buffer item isc_tpb_at_snapshot_number.

The snapshot_number from an active transaction can be obtained with RDB$GET_CONTEXT('SYSTEM', 'SNAPSHOT_NUMBER') in SQL or using the transaction information API call with fb_info_tra_snapshot_number information tag.The snapshot_number passed to the new transaction must be a snapshot of a currently active transaction.

Note

To share a stable view between transactions, the other transaction also needs to have isolation level SNAPSHOT.With READ COMMITTED, the snapshot number will move forward.
Example
SET TRANSACTION SNAPSHOT AT NUMBER 12345;
SNAPSHOT TABLE STABILITY Isolation Level

The SNAPSHOT TABLE STABILITY — or SNAPSHOT TABLE — isolation level is the most restrictive.As in SNAPSHOT, a transaction in SNAPSHOT TABLE STABILITY isolation sees only those changes that were committed before the current transaction was started.After a SNAPSHOT TABLE STABILITY is started, no other transactions can make any changes to any table in the database that has changes pending for this transaction.Other transactions can read other data, but any attempt at inserting, updating or deleting by a parallel process will cause conflict exceptions.

The RESERVING clause can be used to allow other transactions to change data in some tables.

If any other transaction has an uncommitted change pending in any (non-SHARED) table listed in the RESERVING clause, trying to start a SNAPSHOT TABLE STABILITY transaction will result in an indefinite wait (default or explicit WAIT), or an exception (NO WAIT or after expiration of the LOCK TIMEOUT).

The SNAPSHOT TABLE STABILITY isolation level is also known as “consistency”.