FOR UPDATE [OF]

CommentsProcedural SQL (PSQL) Statements

`FOR UPDATE [OF]`

Syntax

SELECT ... FROM single_table
  [WHERE ...]
  FOR UPDATE [OF <column-name-list>]

FOR UPDATE does not do what its name suggests.Its only effect currently is to disable the pre-fetch buffer.

Tip	It is likely to change in a future Firebird version: the plan is to validate cursors marked with `FOR UPDATE` if they are truly updatable and reject positioned updates and deletes for cursors evaluated as non-updatable.

The OF sub-clause does not do anything at all, and is only provided for syntax compatibility with other database systems.

`WITH LOCK`

Applies limited pessimistic locking

Syntax

SELECT ... FROM single_table
  [WHERE ...]
  [FOR UPDATE [OF <column_list>]]
  WITH LOCK [SKIP LOCKED]

WITH LOCK provides a limited explicit pessimistic locking capability for cautious use in conditions where the affected row set is:

extremely small (ideally singleton), and
precisely controlled by the application code.

Caution

This is for experts only!

The need for a pessimistic lock in Firebird is very rare indeed and should be well understood before use of this extension is considered.

It is essential to understand the effects of transaction isolation and other transaction attributes before attempting to implement explicit locking in your application.

If the WITH LOCK clause succeeds, it will secure a lock on the selected rows and prevent any other transaction from obtaining write access to any of those rows, or their dependants, until your transaction ends.

WITH LOCK can only be used with a top-level, single-table SELECT statement.It is not available:

in a subquery specification
for joined sets
with the DISTINCT operator, a GROUP BY clause or any other aggregating operation
with a view
with the output of a selectable stored procedure
with an external table
with a UNION query

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.

When the optional SKIP LOCKED clause is specified, records locked by a different transaction are skipped.

Note	If a statement has both `SKIP LOCKED` and `OFFSET`/`SKIP`/`ROWS` subclauses, locked rows may be skipped before `OFFSET`/`SKIP`/`ROWS` subclause can account for them, thus skipping more rows than specified in `OFFSET`/`SKIP`/`ROWS`.

Wait behaviour and conflict reporting depend on the transaction parameters specified in the TPB block:

Table 1. How TPB settings affect explicit locking
TPB mode	Behaviour
isc_tpb_consistency	Explicit locks are overridden by implicit or explicit table-level locks and are ignored.
isc_tpb_concurrency + isc_tpb_nowait	If a record is modified by any transaction that was committed since the transaction attempting to get explicit lock started, or an active transaction has performed a modification of this record, an update conflict exception is raised immediately.
isc_tpb_concurrency + isc_tpb_wait	If the record is modified by any transaction that has committed since the transaction attempting to get explicit lock started, an update conflict exception is raised immediately. If an active transaction is holding ownership on this record (via explicit locking or by a normal optimistic write-lock) the transaction attempting the explicit lock waits for the outcome of the blocking transaction and, when it finishes, attempts to get the lock on the record again.This means that, if the blocking transaction committed a modified version of this record, an update conflict exception will be raised.
isc_tpb_read_committed + isc_tpb_nowait	If there is an active transaction holding ownership on this record (via explicit locking or normal update), an update conflict exception is raised immediately.
isc_tpb_read_committed + isc_tpb_wait	If there is an active transaction holding ownership on this record (via explicit locking or by a normal optimistic write-lock), the transaction attempting the explicit lock waits for the outcome of blocking transaction and when it finishes, attempts to get the lock on the record again. Update conflict exceptions can never be raised by an explicit lock statement in this TPB mode.