Locking and Lock Conflicts - Firebird

With Firebird, locking is governed by the relative ages of transactions and the records managed by Firebird’s versioning engine. All locking applies at the row level, except when a transaction is operating in SNAPSHOT TABLE STABILITY isolation or with a table reservation restriction that blocks write access.


The timing of the lock on the row in normal read-write activity is optimistic—no locking is in force on any row until the moment it is actually required. Until an update of the row is posted to the server, the row is free to be “won” by any read-write transaction.

Pessimistic Locking

Pessimistic, or pre-emptive, locking can be applied to sets of rows or to entire tables. The table-locking options have already been introduced (see the sections “Table Reservation” and “SNAPSHOT TABLE STABILITY (Consistency)”).

Row-level and set-level pessimistic locking are options where there is an application requirement to reserve a row or a small set in advance of actually posting an update or deletion.

Explicit Locking

The capability to do explicit pessimistic row locking was added to Firebird’s SQL SELECT statement syntax at version 1.5. It is restricted to “outer-level” SELECT statements that return output sets or define cursors. In cannot be applied to subqueries.

The abbreviated syntax for acquiring explicit pessimistic row locks is

FOR UPDATE, which is not a locking instruction, requests that the output set be delivered to the client one row at a time, rather than as a batch. The optional phrase WITH LOCK is the element that forces the pre-emptive lock on a row as soon as the server outputs it from the server. Rows waiting to be output are not locked.

Dummy Updates

The traditional way of achieving a pessimistic row lock with Firebird is the dummyupdate. It is a hack that takes advantage of record versioning. Simply put, the client posts an update statement for the row that does not update anything —it just sets a column to its current value, causing the server to create a new record version, and thus blocks other transactions from acquiring the row for updating or deletion.

Lock Conflicts

A lock conflict is triggered when concurrent transactions try to update or delete the same row during a time when their views of database state overlap. Lock conflicts are the planned outcome of Firebird’s transaction isolation and record versioning strategy, protecting volatile data from uncontrolled overwriting by parallel operations on the same data.

The strategy works so well that there are really only two conditions that can cause lock conflicts:

  • Condition 1: One transaction (ThisTransaction) has posted an update or deletion for a row that another transaction, which started before ThisTransaction locked that row, attempts to update or delete. The other transaction encounters a lock conflict and has two choices:
    1. It can roll back its attempt and try again later against the newest committed version.
    2. It can wait until ThisTransaction either commits or rolls back.
  • Condition 2: ThisTransaction is blocking the whole table against writes, because it has the table isolated in SNAPSHOT TABLE STABILITY or by a PROTECTED table reservation, and another transaction tries to update or delete a row or to insert a new row.

Suppose ThisTransaction posts a change to a row. Another transaction comes along and requests to change or delete the same row. In SNAPSHOT isolation with WAIT, the other transaction will keep waiting until ThisTransaction completes with either commit or rollback.

If ThisTransaction commits, then the other transaction will fail with an update conflict. The client that started the other transaction should have an exception handler that either rolls the transaction back and starts a new one to resubmit the request, or simply commits the transaction and exits.

Calling COMMIT in a lock conflict exception handler is not recommended, since it breaks the atomicity of the transaction —some work will complete, some will not, and it will be impossible to predict database state afterward. Rollback is almost always the right response to a lock conflict.

Unfortunately, Firebird tends to generalize all locking exceptions and report them as “deadlocks.” The normal case just described is not a deadlock.

What Is a Deadlock?

Deadlock is just a nickname, borrowed from the sport of wrestling, for the condition where two transactions are contending to update rows in overlapping sets and one transaction does not take any precedence over the other.

For example, ThisTransaction has an update pending on Row X and wants to update Row Y, while the other transaction has an update pending on Row Y and wants to update Row X, and both transactions are in WAIT mode. As in wrestling, the deadlock can be resolved only if one contender withdraws its hold. One transaction must roll back and let the other commit its changes.

Firebird provides for the application to resolve the deadlock, by scanning for deadlocks every few seconds. It will arbitrarily pick one transaction from the deadlock and deliver a deadlock exception.

Developers should not dread deadlock messages. On the contrary, they are the essence of isolating the work of multiple users in transaction contexts. You should anticipate them and handle them effectively in your client application.

When ThisTransaction is selected to resolve the deadlock, the application’s exception handler should roll it back to allow the other transaction to resume and complete its work. The alternative —committing ThisTransaction in the exception handler—is not recommended, since ThisTransaction becomes non-atomic and the other transaction will fail with a lock conflict.

Deadly Embrace

In rare cases, more than two transactions could be deadlocked in contention for the same overlapping sets. It is sometimes called the deadly embrace. The deadlock scan will fail one transaction (ThisTransaction), handing it over to the client for exception resolution, as before. However, this time, even if the client rolls back This Transaction, those other transactions are still deadlocked out there.


The client might start a new transaction and retry, but the other contenders are still deadlocked, waiting for the next deadlock scan to extricate another of the contenders with a deadlock exception. As long as the retrying application keeps retrying with a WAIT transaction, it is just going to wait for some indefinite time for the other transactions to resolve the deadly embrace. For those futile retries, the transaction is said to be in a livelock.

In short, it is important in the first place to avoid transaction contexts that make it possible for a deadly embrace to occur. As added protection, exception handlers should be made capable of quickly dealing with deadlocks and ensuring that problem transactions finish cleanly and without delay.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics