FirebirdSQL logo
NO WAIT Mode

In the NO WAIT mode, a transaction will immediately throw a database exception if a conflict occurs.

Note

LOCK TIMEOUT is a separate transaction option, but can only be used for WAIT transactions.Specifying LOCK TIMEOUT with a NO WAIT transaction will raise an error “invalid parameter in transaction parameter block -Option isc_tpb_lock_timeout is not valid if isc_tpb_nowait was used previously in TPB

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

  • SNAPSHOT TABLE STABILITY

  • READ COMMITTED with three specifications (READ CONSISTENCY, NO RECORD_VERSION and RECORD_VERSION)

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