Pessimistic Locking - Firebird

In a pessimistic locking DBMS, rows that have been requested by one user or transaction for an operation that could potentially change data state become immediately inaccessible for reading or writing by other users or transactions. In some systems, the entire table becomes unavailable. Many developers moving databases and applications to Firebird from such systems are disconcerted by optimistic locking and search desperately for ways to mimic the old.

In Firebird, all updates are at row level—there is no mechanism to lock an individual column. At almost all levels of transaction isolation, the engine employs an optimistic locking principle: All transactions not constrained by some form of pessimistic locking begin with a view of the currently committed state of all rows in all tables—subject to privileges, of course. When a transaction submits a request to update a row, the old version of that row remains visible to all transactions. Writers do not block readers.

Internally, upon a successful update request, the engine creates a new version of the row that implicitly locks the original version. Depending on the settings of that user’s transaction and others, some level of lock conflict will occur if another transaction attempts to update or delete the locked row. For more information about Firebird’s locking conditions, refer to the previous chapter.

Firebird is designed for interactive use by many concurrent users and there are seldom any genuine reasons to use a pessimistic lock. It is not a magic bullet to be used to emulate the locking behavior of a desktop DBMS. Pessimistic locks by one transaction will cause conflicts for other transactions. There is no escape from the responsibility to work in sympathy with the multi-user transaction model and write handlers to anticipate locking conflicts.

Table-Level Locking

The transaction isolation level TABLE STABILITY (aka consistency isolation) provides full, table write-locking that flows on to dependent tables. It is too aggressive for interactive applications.

It is preferable to use a RESERVING clause with READ COMMITTED or SNAPSHOT isolation because it offers more flexibility and control for targeting tables that you want to lock for the duration of a transaction. It has parameters that determine how much protection is requested for each table:

RESERVING <reserving_clause>; <reserving_clause> = table [, table ...] [FOR [SHARED | PROTECTED] {READ | WRITE}] [, <reserving_clause>]

The <reserving-clause> can comprise multiple sets of reservation specifications, enabling different tables or groups of tables to be reserved with different rights. A specific table should appear only once in the entire reserving clause. Refer to the previous chapter for information about table reservation.

Statement-Level Locking

Pessimistic locking at statement level—affecting individual rows or sets—is not directly an issue of transaction configuration. However, the effects of these lock types are directly governed by the transaction settings of both the transaction in which the locking is defined and other transactions that try to access the locked row or set. It is essential to consider these techniques in terms of transaction settings.

Pessimistic locking of a row or a set is antithetical to the way Firebird was designed to work. In short, if you have lived with and depended on pessimistic locking until you discovered Firebird, then the time has come for you to get into the MGA groove and enjoy the benefits of optimistic row locking.

However, it cannot be denied that design requirements do occasionally call for a pessimistic lock, though the need in Firebird is much rarer than your experiences with other DBMSs may have taught you. The likely scenario involves an absolute requirement for exclusive access to rows that, not withstanding, does not justify a table-level lock. Typically, a row, once read, must be secured by that reader exclusively for an update or deletion. This requirement is sometimes referred to as strict task serialization.

For conditions where a row-level pessimistic lock is necessary, a pessimistic locking mechanism and supporting SQL syntax were introduced in version 1.5. Before then, the Firebird engine did not support it, per se. In this section, we look first at the standard “hack” that client applications do—when language support for it is absent—to achieve a pessimistic lock. Next we examine the syntax and conditions for the explicit SELECT ... WITH LOCK that brought pessimistic locking support to Firebird SQL at release 1.5.

The “Dummy Update” Hack

“Editing” is not a server-side activity, hence, when the user clicks the Edit button (or whatever device your program uses to give her an editing interface) it changes nothing on the server. As far as the server is concerned, the transaction is merely reading. No new record version is created. There is no lock. Nothing changes until the user is finished editing the row and the application actually posts the user’s work.

Developers who regard this behavior as a problem get around it by having their applications post a “dummy update” to a row as soon as the user asks to edit it. The “hack” is to post an update statement that sets a stable column to its current value. Usually, theprimary key column is used, for example:


Thus, the server creates a new record version, in which nothing is actually different from the latest committed version, and applies a lock to the row. Once the user signals that she has finished editing the row, the application posts the real update, for example:

UPDATE ATABLE SET COLUMN2 = 'Some new value', COLUMN3 = 99, ...

WHERE PKEY = <the value of the primary key>;

On the server, a second new record version is posted, overwriting the first.

If you really need a pessimistic lock and there is no combination of transaction attributes that suits your special need, this technique is effective. It works on a single row only and lasts until the transaction is committed, even if the user decides not to perform any actual change to the row.

Explicit Locking in v.1.5 and Later

The syntax pattern for explicit locks is

SELECT output-specification FROM table-name [WHERE search-condition] [FOR UPDATE [OF col1 [, col2 [,...]]]] WITH LOCK;

How It Works

As the engine considers, in turn, each record falling under an explicit lock statement, it returns either the record version that is the most currently committed, regardless of database state when the statement was submitted, or an exception.

Wait behavior and conflict reporting depend on the transaction parameters specified in the TPB block.

The engine guarantees that all records returned by an explicit lock statement are actually locked and meet the search conditions specified in WHERE clause, as long as no search condition depends on any other tables, for example, through a join or sub-query. It also guarantees to lock only rows that meet the search conditions. However, the potential set is subject to the settings of its transaction: isolation level, lock resolution, and record version. A row does not get its lock until it is actually output to the server’s row buffer. There is no guarantee that the potential set will or will not be affected by parallel transactions that commit during the course of the locking statement’s execution, making further rows eligible for selection by your transaction.

Refer to Table for a summary of interactions between transaction settings and explicit locks. ThisTransaction is the transaction that has, or is trying to get, an explicit lock on a row or set.

Interaction of Transaction Settings and Explicit Locks

Interaction of Transaction Settings and Explicit Locks

Interaction of Transaction Settings and Explicit Locks

If a SELECT ... WITH LOCK clause succeeds and the optional FOR UPDATE sub-clause is omitted, all of the rows in the set are pre-emptively locked, whether you actually update them or not. With careful transaction configuration and client-side control of buffering, the lock will prevent any other transaction from obtaining write access to any of those rows, or their dependants, until your transaction ends. Pre-emptively locking a multi-row set will cause deadlocks to escalate and your application code must be ready to manage them.

The number of rows in the specified output set has important consequences if you use an access method that requests “datasets” or “recordsets” in packets of a few hundred rows at a time (“buffered fetches”) and buffers them in the client, typically to implement a scrolling interface. If the lock fails upon fetching a certain row and causes an exception, none of the rows currently in waiting in the server’s buffer will be sent and those already passed to the client buffer become invalid. Your application will have to roll back the transaction.

With this style of access, it is essential to provide your applications with a way to handle exceptions as they occur. Use a very strict WHERE clause to limit the range of the lock to one or a very small set of rows and avoid having partly fetched sets made invalid. If your data access interface supports it, set the data access component’s fetch buffer to one row, for example:

SELECT * FROM DOCUMENT WHERE ID = ? WITH LOCK /* ID is the primary key */

The optional FOR UPDATE clause provides a way to define a multi-row set and fetch and process rows one at a time.


If the FOR UPDATE clause is included, buffered fetching will be disabled and the lock will be applied to each row, one by one, as it is fetched into the server-side row cache. If a named cursor is controlling the position of the update, the clause can take an optional OF <column-list> to target updates at specific cursor columns.

Because the normal isolation rules apply to the transaction, it is possible for a lock that was available at the start of the request to fail subsequently. Unfetched rows remain “clear” and available for other transactions to obtain for update, leaving a “moving window” in which any unfetched row may become locked by another transaction, even if the lock appeared to succeed when the set was requested.

Example Using WITH LOCK

This statement defines an unrestricted multi-row output set and causes each row to be fetched into the server-side buffer individually. It will not fetch the next row until he For reasons of scope, the subject of named cursors is barely touched in this guide. The API provides a group of isc_dsql_* functions for operating them. The DECLARE CURSOR statement syntax, fully implemented in ESQL, is available in some DSQL programming environments. WITH LOCK causes the pessimistic lock to be attempted upon each row request. It will return either the next row in the set or an exception.


Restrictions on Explicit Locking

The SELECT ... WITH LOCK construct is available in DSQL and PSQL. It can succeed only in a top-level, single-table SELECT statement.

  • It is not available in a subquery or a joined set.
  • It cannot be specified with set quantifier (the DISTINCT operator, FIRST, or SKIP), a GROUP BY clause, or any other aggregating operation.
  • It cannot be used in or with a view, an external table, or the output set of a selectable stored procedure.

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

Firebird Topics