Isolation Level

Keeping the work of one database task separated from others is what isolation is about.Changes made by one statement become visible to all remaining statements executing within the same transaction, regardless of its isolation level.Changes that are in progress within other transactions remain invisible to the current transaction as long as they remain uncommitted.The isolation level and, sometimes, other attributes, determine how transactions will interact when another transaction wants to commit work.

The ISOLATION LEVEL attribute defines the isolation level for the transaction being started.It is the most significant transaction parameter for determining its behavior towards other concurrently running transactions.

The three isolation levels supported in Firebird are:

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”.

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.