FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 SQL SecuritySupplementary Information 

Statement Timeouts

The statement timeout feature allows execution of a statement to be stopped automatically when it has been running longer than a given timeout period.It gives the database administrator an instrument for limiting excessive resource consumption from heavy queries.

Statement timeouts can also be useful to application developers when creating and debugging complex queries without advance knowledge of execution time.Testers and others could find them handy for detecting long-running queries and establishing finite run times for test suites.

How the Statement Timeout Works

When the statement starts execution, or a cursor is opened, the engine starts a special timer.It is stopped when the statement completes execution, or the last record has been fetched by the cursor.

Note

A fetch does not reset this timer.

When the timeout point is reached:

  • if statement execution is active, it stops at closest possible moment

  • if statement is not active currently (between fetches, for example), it is marked as cancelled, and the next fetch will break execution and return an error

Note
Statement types excluded from timeouts

Statement timeouts are not applicable to some types of statement and will be ignored:

  • All DDL statements

  • All internal queries issued by the engine itself

docnext count = 12

Setting a Statement Timeout
Note

The timer will not start if the timeout period is set to zero.

A statement timeout can be set:

  • at database level, by the database administrator, by setting the configuration parameter StatementTimeout in firebird.conf or databases.conf.StatementTimeout is an integer representing the number of seconds after which statement execution will be cancelled automatically by the engine.Zero means no timeout is set.A non-zero setting will affect all statements in all connections.

  • at connection level, using SET STATEMENT TIMEOUT or the API for setting a statement timeout (setStatementTimeout).A connection-level setting (via SQL or the API) affects all statements for the given connection;units for the timeout period at this level can be specified to any granularity from hours to milliseconds.

  • at statement level, using the API, in milliseconds

Determining the Statement Timeout that is In Effect

The statement timeout value that is in effect is determined whenever a statement starts executing, or a cursor is opened.In searching out the timeout in effect, the engine goes up through the levels, from statement through to database and/or global levels until it finds a non-zero value.If the value in effect turns out to be zero then no statement timer is running and no timeout applies.

A statement-level or connection-level timeout can override the value of a database-level setting, as long as the period of time for the lower-level setting is no longer than any non-zero timeout that is applicable at database level.

Important

Take note of the difference between the time units at each level.At database level, in the conf file, the unit for StatementTimeout is seconds.In SQL, the default unit is seconds but can be expressed in hours, minutes or milliseconds explicitly.At the API level, the unit is milliseconds.

Absolute precision is not guaranteed in any case, especially when the system load is high, but timeouts are guaranteed not to expire earlier than the moment specified.

Whenever a statement times out and is cancelled, the next user API call returns the error isc_cancelled with a secondary error specifying the exact reason, viz.,

isc_cfg_stmt_timeout

Config level timeout expired

isc_att_stmt_timeout

Attachment level timeout expired

isc_req_stmt_timeout

Statement level timeout expired

Note
Notes about Statement Timeouts

  1. A client application could wait longer than the time set by the timeout value if the engine needs to undo a large number of actions as a result of the statement cancellation

  2. When the engine runs an EXECUTE STATEMENT statement, it passes the remainder of the currently active timeout to the new statement.If the external (remote) engine does not support statement timeouts, the local engine silently ignores any corresponding error.

  3. When the engine acquires a lock from the lock manager, it tries to lower the value of the lock timeout using the remainder of the currently active statement timeout, if possible.Due to lock manager internals, any statement timeout remainder will be rounded up to whole seconds.

Time Zone Management

Statements for management of time zone features of the current connections.

SET TIME ZONE

Sets the session time zone

Syntax
SET TIME ZONE { time_zone_string | LOCAL }

Changes the session time zone to the specified time zone.Specifying LOCAL will revert to initial session time zone of the session (either the default or as specified through connection property isc_dpb_session_time_zone).

Executing [fblangref50-management-session-reset-alter] has the same effect on the session time zone as SET TIME ZONE LOCAL, but will also reset other session properties.

SET TIME ZONE Examples

set time zone '-02:00';
set time zone 'America/Sao_Paulo';
set time zone local;

Optimizer Configuration

SET OPTIMIZE

Configures whether the optimizer should optimize for fetching first or all rows.

Syntax
SET OPTIMIZE <optimize-mode>

<optimize-mode> ::=
    FOR {FIRST | ALL} ROWS
  | TO DEFAULT

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 statement level using the OPTIMIZE FOR clause.

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

Reset Session State

ALTER SESSION RESET

Resets the session state of the current connection to its initial values

Syntax
ALTER SESSION RESET

Resetting the session can be useful for reusing the connection by a client application (for example, by a client-side connection pool).When this statement is executed, all user context variables are cleared, contents of global temporary tables are cleared, and all session-level settings are reset to their initial values.

It is possible to execute ALTER SESSION RESET without a transaction.

Execution of ALTER SESSION RESET performs the following steps:

  • Error isc_ses_reset_err (335545206) is raised if any transaction is active in the current session other than the current transaction (the one executing ALTER SESSION RESET) and two-phase transactions in the prepared state.

  • System variable RESETTING is set to TRUE.

  • ON DISCONNECT database triggers are fired, if present and if database triggers are not disabled for the current connection.

  • The current transaction (the one executing ALTER SESSION RESET), if present, is rolled back.A warning is reported if this transaction modified data before resetting the session.

  • Session configuration is reset to their initial values.This includes, but is not limited to:

    • DECFLOAT parameters (TRAP and ROUND) are reset to the initial values defined using the DPB at connect time, or otherwise the system default.

    • Session and statement timeouts are reset to zero.

    • The current role is restored to the initial value defined using DPB at connect time, and — if the role changed — the security classes cache is cleared.

    • The session time zone is reset to the initial value defined using the DPB at connect time, or otherwise the system default.

    • The bind configuration is reset to the initial value defined using the DPB at connect time, or otherwise the database or system default.

    • In general, configuration values should revert to the values configured using the DPB at connect time, or otherwise the database or system default.

  • Context variables defined for the USER_SESSION namespace are removed (USER_TRANSACTION was cleared earlier by the transaction roll back).

  • Global temporary tables defined as ON COMMIT PRESERVE ROWS are truncated (their contents is cleared).

  • ON CONNECT database triggers are fired, if present and if database triggers are not disabled for the current connection.

  • A new transaction is implicitly started with the same parameters as the transaction that was rolled back (if there was a transaction)

  • System variable RESETTING is set to FALSE.

Note

  • The context variables CURRENT_USER and CURRENT_CONNECTION will not be changed.

  • As isql starts multiple transactions for a single connection, ALTER SESSION RESET cannot be executed in isql.

Error Handling

Any error raised by ON DISCONNECT triggers aborts the session reset and leaves the session state unchanged.Such errors are reported using primary error code isc_session_reset_err (335545206) and error text "Cannot reset user session".

Any error raised after ON DISCONNECT triggers (including the ones raised by ON CONNECT triggers) aborts both the session reset and the connection itself.Such errors are reported using primary error code isc_ses_reset_failed (335545272) and error text "Reset of user session failed. Connection is shut down.".Subsequent operations on the connection (except detach) will fail with error isc_att_shutdown (335544856).

Debugging

SET DEBUG OPTION

Sets debug options

Syntax
SET DEBUG OPTION option-name = value
Table 1. Supported options
Option name Value Type Description

DSQL_KEEP_BLR

BOOLEAN

Stores statement BLR for retrieval with isc_info_sql_exec_path_blr_bytes and isc_info_sql_exec_path_blr_text.

SET DEBUG OPTION configures debug information for the current connection.

Warning

Debug options are closely tied to engine internals and their usage is discouraged if you do not understand how these internals are subject to change between versions.