Setting A Statement Timeout

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

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
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.
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.