Commented by: PizzaProgram Ltd. (szakilaci)

This is THE feature we are waiting for since FB1.0 :)
but not only for long running tasks.

The Timeout parameters should be in milliseconds (int64)* please.
-1 = infinite (<0)
0 = no timeout
<50 = there can be an auto-minimum inside the engine : if (t<50) then t:=50; // maybe 20ms is enough too, needs testing
<1000ms = it is sometimes necessary to control short-running transactions. ex.: should be finished within 100ms ... otherwise something is wrong with server/connection/etc ... and need to deal with it differently on the app. side.

* or we could use DOUBLE. (0.5 = half sec.)

Would be nice, if

AUTONOMOUS TRANSACTION would have a TIMEOUT parameter too.
or EXECUTE STATEMENT ON EXTERNAL DATA SOURCE would have a CommandTimeout.

Commented by: PizzaProgram Ltd. (szakilaci)

A possible implementation for this:

1.) There would be a LinkedList* resident in the memory of the FB engine:
<event_type : byte> -- {1=connection | 2=transaction | 3=command ... ? }
<event_id : bigint> -- {the conn or trans ... ID}
<event_ends : timestamp> -- when it should be timed out.
* see more about: http://stackoverflow.com/a/270094

2.) This List would be sorted/indexed by <event_ends> value. (Ascending)
- every time a new event is started, AND IT HAS a TIMEOUT parameter too >>
it's ID would be inserted into this list. (Keeping the time-order.)

3.) It's enough always to check the beginning of this list, if the first item in the list is past current time.
- delete that Item from the beginning of that List.
- check if that event still exists .
- Signal the event it should stop executing and should drop a timeout exeption.
"stop_because_of_timeout = True" (Booleans are multi-thread-safe and fast. )
- the event itself (for example a running query) checks that boolean periodically.
- check next ListItem.

4.) This check would run every 50ms once.
- It is not a problem, if the engine does not immediately recognize: the event timed out.
- Basically extra 49ms delay is still fine. If the event finished by itself by than, even better :)

5.) Commands would look like this:
SELECT TIMEOUT(5000) * FROM invoice_details WHERE recorded > '2000.01.01' ;

... Maybe the format "WITH TIMEOUT" would be more elegant... also WITH LOCK WITH TIMEOUT()

6.) By a connection-timeout method even a Backup could be time-maximized!

7.) Hierarchy:
- if the is no "timeout" in [firebird.conf] nor [TPB] nor at the [SQL] >>> nothing happens = standard behaviour
- overwrite order:
[firebird.conf] <<< [DB connection TPB] <<< [TRansaction TPB] <<< [Command SQL]

8.) I'm not sure if [Connection Idle Timeout] is necessary. I'm afraid it would cause more harm than good.
(For example a simple Delphi program with 1 DB component expects: a local DB should be open forever until closed ... even if I put a laptop to sleep for a month.)

Commented by: PizzaProgram Ltd. (szakilaci)

There should be 3x3 different error codes for each of these timeout-kinds:

timeout_db_def <Database connection timeout set in config file @1 ms>
timeout_db_tpb <Database connection timeout set in API TPB @1 ms>
timeout_db_cmd <External database connection timeout set in SQL @1 ms>

timeout_tr_def <A rollback performed by transaction timeout, set in config file @1 ms>
timeout_tr_tpb <A rollback performed by transaction timeout, set in API TPB @1 ms>
timeout_tr_cmd <Autonomous transaction timeout, set in SQL @1 ms>

timeout_sql_def <SQL command timeout, set in config file @1 ms>
timeout_sql_tpb <SQL command timeout, set in API TPB @1 ms>
timeout_sql_cmd <SQL command timeout @1 ms>

... so a developers can see the difference. (Much easier to test and debug.)

Commented by: @hvlad

The feature is almost finished and already committed into separate branch (for review and testing).

You may found it there
https://github.com/FirebirdSQL/firebird/tree/timeouts

It lacks documentation so far, the more-or-less close description could be found there
https://sourceforge.net/p/firebird/mailman/message/35285574/