FirebirdSQL logo

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.

Examples using explicit locking

  1. Simple:

    SELECT * FROM DOCUMENT WHERE ID=? WITH LOCK;
  2. Multiple rows, one-by-one processing with DSQL cursor:

    SELECT * FROM DOCUMENT WHERE PARENT_ID=?
      FOR UPDATE WITH LOCK;