14.2. Connections Pool Management

Management statements to manage the external connections pool.

14.2.1. ALTER EXTERNAL CONNECTIONS POOL

Used forManaging the external connections pool

Available inDSQL

Syntax 

  |ALTER EXTERNAL CONNECTIONS POOL
  |  { CLEAR ALL
  |  | CLEAR OLDEST
  |  | SET LIFETIME lifetime <time-unit>
  |  | SET SIZE size }
  | 
  |<time-unit> ::= SECOND | MINUTE | HOUR

Table 14.2. ALTER EXTERNAL CONNECTIONS POOL Statement Parameters
ParameterDescription

lifetime

Maximum lifetime of a connection in the pool. Minimum values is 1 SECOND, maximum is 24 HOUR.

size

Maximum size of the connection pool. Range 0 - 1000. Setting to 0 disables the external connections pool.

When prepared it is described like a DDL statement but its effect is immediate — it is executed immediately and completely, without waiting for transaction commit.

The statements can be issued from any connection, and changes are applied to the in-memory instance of the pool in the current Firebird process. If the process is a Classic one, a change submitted there does not affect other Classic processes.

Changes made with ALTER EXTERNAL CONNECTIONS POOL are not persistent: after a restart, Firebird will use the pool settings configured in firebird.conf by ExtConnPoolSize and ExtConnPoolLifeTime.

14.2.1.1. Clauses of ALTER EXTERNAL CONNECTIONS POOL

CLEAR ALL

Closes all idle connections and disassociates currently active connections so they are immediately closed when unused.

CLEAR OLDEST

Closes expired connections

SET LIFETIME

Configures the maximum lifetime of an idle connection in the pool. The default value (in seconds) is set using the parameter ExtConnPoolLifetime in firebird.conf.

SET SIZE

Configures the maximum number of idle connections in the pool. The default value is set using the parameter ExtConnPoolSize in firebird.conf.

14.2.1.2. How the Connection Pool Works

Every successful connection is associated with a pool, which maintains two lists — one for idle connections and one for active connections. When a connection in the active list has no active requests and no active transactions, it is assumed to be unused. A reset of the unused connection is attempted using an ALTER SESSION RESET statement and,

  • if the reset succeeds (no errors occur) the connection is moved into the idle list;

  • if the reset fails, the connection is closed;

  • if the pool has reached its maximum size, the oldest idle connection is closed.

  • When the lifetime of an idle connection expires, it is deleted from the pool and closed.

14.2.1.2.1. New Connections

When the engine is asked to create a new external connection, the pool first looks for a candidate in the idle list. The search, which is case-sensitive, involves four parameters:

  1. connection string

  2. username

  3. password

  4. role

If suitable connection is found, it is tested to check that it is still alive.

  • If it fails the check, it is deleted, and the search is repeated, without reporting any error to the client

  • Otherwise, the live connection is moved from the idle list to the active list and returned to the caller

  • If there are multiple suitable connections, the most recently used one is chosen

  • If there is no suitable connection, a new one is created and added to the active list.

14.2.1.3. Who Can Alter the External Connections Pool

The ALTER EXTERNAL CONNECTIONS POOL statement can be executed by:

See alsoRDB$GET_CONTEXT