WITH LOCK

CommentsProcedural SQL (PSQL) Statements

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

Usage with a `FOR UPDATE` Clause

If the FOR UPDATE sub-clause precedes the WITH LOCK sub-clause, buffered fetches are suppressed.Thus, the lock will be applied to each row, one by one, at the moment it is fetched.It becomes possible, then, that a lock which appeared to succeed when requested will nevertheless fail subsequently, when an attempt is made to fetch a row which has become locked by another transaction in the meantime.This can be avoided by also using SKIP LOCKED.

Tip	As an alternative, it may be possible in your access components to set the size of the fetch buffer to 1.This would enable you to process the currently-locked row before the next is fetched and locked, or to handle errors without rolling back your transaction.

WITH LOCK

WITH LOCK

Usage with a FOR UPDATE Clause

`WITH LOCK`

Usage with a `FOR UPDATE` Clause