DSQL, PSQL

    Used for

    Limited pessimistic locking

    Description:

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

    1. extremely small (ideally, a singleton), and

    2. precisely controlled by the application code.

    Syntax

    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.

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

    • for joined sets

    • with the DISTINCT operator, a GROUP BY clause or any other aggregating operation

    • with a view

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

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

    See also

    When an UPDATE statement tries to access a record that is locked by another transaction, it either raises an update conflict exception or waits for the locking transaction to finish, depending on TPB mode. Engine behaviour here is the same as if this record had already been modified by the locking transaction.

    No special gdscodes are returned from conflicts involving pessimistic locks.

    The engine guarantees that all records returned by an explicit lock statement are actually locked and do meet the search conditions specified in WHERE clause, as long as the search conditions do not depend on any other tables, via joins, subqueries, etc. It also guarantees that rows not meeting the search conditions will not be locked by the statement. It can not guarantee that there are no rows which, though meeting the search conditions, are not locked.

    The engine locks rows at fetch time. This has important consequences if you lock several rows at once. Many access methods for Firebird databases default to fetching output in packets of a few hundred rows (“buffered fetches”). Most data access components cannot bring you the rows contained in the last-fetched packet, where an error occurred.

    • While explicit locks can be used to prevent and/or handle unusual update conflict errors, the volume of deadlock errors will grow unless you design your locking strategy carefully and control it rigorously.

    • Most applications do not need explicit locks at all. The main purposes of explicit locks are:

      1. to prevent expensive handling of update conflict errors in heavily loaded applications, and

      2. to maintain integrity of objects mapped to a relational database in a clustered environment.

      If your use of explicit locking doesn’t fall in one of these two categories, then it’s the wrong way to do the task in Firebird.

    • Explicit locking is an advanced feature; do not misuse it! While solutions for these kinds of problems may be very important for web sites handling thousands of concurrent writers, or for ERP/CRM systems operating in large corporations, most application programs do not need to work in such conditions.