FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

How the engine deals with WITH LOCK

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 error codes are returned from conflicts involving pessimistic locks.

The engine guarantees that all records returned by an explicit lock statement are 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.

Note

This situation can arise if other, parallel transactions commit their changes during the course of the locking statement’s execution.

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, when an error occurred.

Caveats using WITH LOCK

  • Rolling back of an implicit or explicit savepoint releases record locks that were taken under that savepoint, but it doesn’t notify waiting transactions.Applications should not depend on this behaviour as it may get changed in the future.

  • 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 probably 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 important for websites 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.