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;

OPTIMIZE FOR

Syntax
SELECT ...
  [WITH LOCK [SKIP LOCKED]]
  OPTIMIZE FOR {FIRST | ALL} ROWS

The OPTIMIZE FOR clause can only occur on a top-level SELECT.

This feature allows the optimizer to consider another (hopefully better) plan if only a subset or rows is fetched initially by the user application (with the remaining rows being fetched on demand), thus improving the response time.

It can also be specified at the session level using the SET OPTIMIZE management statement.

The default behaviour can be specified globally using the OptimizeForFirstRows setting in firebird.conf or databases.conf.

INTO

Passes SELECT output into variables

Available in

PSQL

Syntax

In PSQL the INTO clause is placed at the end of the SELECT statement.

SELECT [...] <column-list>
FROM ...
[...]
[INTO <variable-list>]

<variable-list> ::= <variable> [, <variable> ...]

<variable> ::= varname | ':' varname
Note

The colon prefix before local variable names in PSQL is optional in the INTO clause.

In PSQL code (triggers, stored procedures and executable blocks), the results of a SELECT statement can be loaded row-by-row into local variables.It is often the only way to do anything with the returned values at all, unless an explicit or implicit cursor name is specified.The number, order and types of the variables must match the columns in the output row.

A “plain” SELECT statement can only be used in PSQL if it returns at most one row, i.e. if it is a singleton select.For multi-row selects, PSQL provides the FOR SELECT loop construct, discussed later in the PSQL chapter.PSQL also supports the DECLARE CURSOR statement, which binds a named cursor to a SELECT statement.The cursor can then be used to walk the result set.

Examples
  1. Selecting aggregated values and passing them into previously declared variables min_amt, avg_amt and max_amt:

    select min(amount), avg(cast(amount as float)), max(amount)
      from orders
      where artno = 372218
      into min_amt, avg_amt, max_amt;
    Note

    The CAST serves to make the average a real number;otherwise, since amount is presumably an integer field, SQL rules would truncate it to the nearest lower integer.

  2. A PSQL trigger that retrieves two values as a BLOB field (using the LIST() function) and assigns it INTO a third field:

    select list(name, ', ')
      from persons p
      where p.id in (new.father, new.mother)
      into new.parentnames;