firebird/doc/README.statement_timeouts

  Timeouts for running SQL statements.
  
Author:
  Vlad Khorsun <[email protected]>


Description:

  The feature allows to set timeout for SQL statement, i.e. it allows to automatically 
stop execution of SQL statement when it running longer than given timeout value. 

  The feature could be useful for:
- database administrators get instrument to limit heavy queries from consuming too 
  much resources
- application developers could use statement timeout when creating\debugging complex 
  queries with unknown in advance execution time
- testers could use statement timeout to detect long running queries and ensure finite 
  run time of the test suites
- and so on

  From the end user point of view feature have following details:
- when statement starts execution (or cursor is opened), engine starts special timer
- fetch doesn't reset timer
- timer is stopped when statement execution finished (or last record is fetched)
- when timer is fired
  - 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 next fetch will actually break execution and returns with error
- timeout value could be set:
  - at database level, by setting value in firebird.conf (or databases.conf) by database 
    administrator
    scope - all statements in all connections
	units - seconds
  - at connection level, using API and\or new SQL statement (see below)
    scope - all statements at given connection
	units - up to milliseconds
  - at statement level, using API 
    scope - given statement
	units - milliseconds
- effective value of timeout is evaluated every time statement starts execution 
  (or cursor is opened) as:
  - if not set at statement level, look at connection level
  - if not set at connection level, look at database level
  - in any case can't be greater than value set at database level 
  i.e. value of statement timeout could be overridden by application developer at lower
  scope but it can't relax limit set by DBA (in config)
- zero timeout means no timeout, i.e. timer will not start
- while statement timeout is set in milliseconds at API level, we can't promise 
  absolute precision. With big load it could be less precise. The only guarantee 
  is that timeout will not fire before specified moment.
- if statement execution is cancelled due to timeout, then API call returns error 
  isc_cancelled with secondary error code specifying exact reason:
  - isc_cfg_stmt_timeout: Config level timeout expired
  - isc_att_stmt_timeout: Attachment level timeout expired
  - isc_req_stmt_timeout: Statement level timeout expired
- statement timeout is ignored for all internal queries issued by engine itself
- statement timeout is ignored for DDL statements
- client application could wait more time than set by timeout value if engine
  need to undo many actions due to statement cancellation
- when engine run EXECUTE STATEMENT statement, it pass rest of currently active timeout
  to the new statement. If external (remote) engine doesn't support statement timeouts, 
  local engine silently ignores corresponding error
- when engine acquires some lock of lock manager, it could lower value of lock timeout 
  using rest of the currently active statement timeout, if possible. Due to lock manager 
  internals rest of statement timeout will be rounded up to the whole seconds.
  
  
  Support at configuration level (firebird.conf and\or databases.conf)
 
  New setting "StatementTimeout": set number of seconds after which statement execution 
will be automatically cancelled by the engine. Zero means no timeout is set.
Per-database configurable. Type: integer. Default value is 0.

  
  Support at API level
  
- get\set statement execution timeout at connection level, milliseconds:
interface Attachment 
	uint getStatementTimeout(Status status);
	void setStatementTimeout(Status status, uint timeOut);
	
- get\set statement execution timeout at statement level, milliseconds:
interface Statement
	uint getTimeout(Status status);
	void setTimeout(Status status, uint timeOut);

- set statement execution timeout at statement level using ISC API, milliseconds:

	ISC_STATUS ISC_EXPORT fb_dsql_set_timeout(ISC_STATUS*, isc_stmt_handle*, ISC_ULONG);

- get statement execution timeout at config and\or connection level is possible
  using isc_database_info() API with new info tags:
  - fb_info_statement_timeout_db
  - fb_info_statement_timeout_att

- get statement execution timeout at statement level is possible using isc_dsql_info() 
  API with new info tags:
  - isc_info_sql_stmt_timeout_user		timeout value of given statement
  - isc_info_sql_stmt_timeout_run		actual timeout value of given statement 
		evaluated considering values set at config, connection and statement levels, see
		"effective value of timeout" above. Valid only when timeout timer is running, i.e. 
		for currently executed statements.

Remote client implementation notes:
  - Attachment::setStatementTimeout() issued "SET STATEMENT TIMEOUT" SQL statement
  - Attachment::getStatementTimeout() calls isc_database_info() with 
    fb_info_statement_timeout_att tag 
  - Statement::setTimeout() save timeout value given and pass it with op_execute
    and op_execute2 packets
  - Statement::getTimeout() returns saved timeout value
  - fb_dsql_set_timeout() is a wrapper over Statement::setTimeout()

If remote server doesn't support statement timeouts (protocol version less than 16):
  - "set" functions will return isc_wish_list error
  - "get" functions will return zero and set isc_wish_list error 
  - "info" functions will return isc_info_error tag in info buffer (as usual).


  Support in SQL

- New SQL statement allows to set set statement execution timeout at connection level:

	SET STATEMENT TIMEOUT <value> [HOUR | MINUTE | SECOND | MILLISECOND]

if timepart is not set, default is SECOND.
This statement could run outside of transaction control and immediately effective.

- Context variables

  Context 'SYSTEM' have new variable: 'STATEMENT_TIMEOUT'. It contains current value of 
statement execution timeout that was set at connection level, or zero, if timeout was 
not set.

- Monitoring tables
  
  MON$ATTACHMENTS
    MON$STATEMENT_TIMEOUT			Connection level statement timeout

  MON$STATEMENTS
    MON$STATEMENT_TIMEOUT			Statement level statement timeout
	MON$STATEMENT_TIMER				Timeout timer expiration time


  MON$STATEMENT_TIMEOUT contains timeout values set at connection\statement level,
	in milliseconds. Zero, if timeout is not set.
  
  MON$STATEMENT_TIMER contains NULL value if timeout was not set or if timer is not 
	running.


  Support in ISQL tool
  
  New ISQL command is introduced:

	SET LOCAL_TIMEOUT <int>
  
  It allows to set statement execution timeout (in milliseconds) for the next statement.
After statement execution it automatically reset to zero.