FirebirdSQL logo
Lock Resolution Mode

When several client processes work with the same database, locks may occur when one process makes uncommitted changes in a table row, or deletes a row, and another process tries to update or delete the same row.Such locks are called update conflicts.

Locks may occur in other situations when multiple transaction isolation levels are used.

The two lock resolution modes are WAIT and NO WAIT.

WAIT Mode

In the WAIT mode (the default mode), if a conflict occurs between two parallel processes executing concurrent data updates in the same database, a WAIT transaction will wait till the other transaction has finished — by committing (COMMIT) or rolling back (ROLLBACK).The client application with the WAIT transaction will be put on hold until the conflict is resolved.

If a LOCK TIMEOUT is specified for the WAIT transaction, waiting will continue only for the number of seconds specified in this clause.If the lock is unresolved at the end of the specified interval, the error message “Lock time-out on wait transaction” is returned to the client.

Lock resolution behaviour can vary a little, depending on the transaction isolation level.

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)