FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

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.