RFC: Timeouts

classic Classic list List threaded Threaded
88 messages Options
12345
Reply | Threaded
Open this post in threaded view
|

RFC: Timeouts

Vlad Khorsun

   Hi All,

   There are user requests to implement statement, transaction and attachment timeouts:
http://tracker.firebirdsql.org/browse/CORE-658
http://tracker.firebirdsql.org/browse/CORE-985
http://tracker.firebirdsql.org/browse/CORE-4238

   More - it was decided by TTG to implement this feature as mandatory on in fb4, see:
http://www.firebirdsql.org/en/planning-board/

   I am responcible for it, so i going to start discussion about it.

   Looking at tickets above we could see three kinds of timeouts:
- statement timeout - to limit maximum statement execution time,
- transaction timeout - to limit maximum lifetime of transaction,
- session (attachment) timeout - to kill idle (inactive) session after some timeout.

   I see the main purpose of this feature as ability for database admins (DBA) to
control "bad written" or "poorly\no supported"  applications. I.e. DBA should be able
to not allow wrong written queries to consume all resources, avoid idle transactions \
connections to run too long and so on. Also, statement timeouts could be useful
for developers for stress-testing.

   I suppose that feature should be configurable at per-database\per-connection and
per-statement levels. Per-database configuration should have highest priority to
give power to DBA. I.e. application developers could make stronger limits than DBA
but couldn't get around DBA. By default feature should be disabled, of course.

   Other mainstream DBMS already have support for active statement and idle session
timeouts. I know no example of transaction timeout, though. I think, transactions
timeouts could bring more troubles than goods and in many cases could be replaced
by idle session timeouts. Therefore i offer to omit transaction timeouts from
further discussion (but i not insist).


   I don't want to discuss implementation details until we reach agreement on
feature as a whole, except of one important detail - where timeouts should be
implemented: at the client side (y-valve\fbclient) or at the server side
(engine). Client-side implementation could be based on already implemented
ability to cancel statement execution asyncronously. Also, it potentially
could take off some work from the engine (to track timeouts) and could be
more precise from user point of view (as it will include network delays into
time measurements).

   But client-side have also weak points: we still have no support for
fb_cancel_execution at XNET protocol, asyncronous work always more complex
and error prone, Java and .Net providers will be forced to create own
implementation of this feature, it is hard detect if some statement should
not be affected by timeout (DDL statements, for example).

   Therefore i offer to create server-side implementation of timeouts.


   So, below is high level overview of what i propose to implement:

a) Statement execution timeout
- timeout is set in milliseconds, there is no guarantee of exact precision
   (especially under high load). The only promise is that timeout will not
   happen earlier than specified
- timeout value could be set
   - at databases.conf (or firebird.conf) by DBA
   - at runtime at session level
   - at runtime at statement level
   - value of zero means timeout is not set
- effective value of timeout is evaluated every time statement execution is
   started using rules below:
   - if not set at statement level then look at session level
   - if not set at session level then look at config
   - can't be greater than (non-zero) value at config
- when statement execution is started (exec()\open() methods), timeout timer is
   started
- timeout tracked since the moment of the execution start, i.e. timer is not
   reset by the fetch() calls
- timeout timer is stopped when statement execution is finished - at the end of
   exec() method or when the fetch() returns last record
- if timeout happens at moment with no client activity (for example between two
   fetch() calls) it will not take immediate effect, i.e. cursor remains open and
   resources is not freed
- when timeout happens the client call (open\exec\fetch) returns with primary
   error code isc_cancelled (same as asyncronous statement cancellation) and
   secondary error code pointed to exact reason of cancellation
- timeout have no effect on DDL statements
- timeout have no effect on internal statements executed by the engine itself
- client could wait longer than expected if statement backout (undo) takes time

b) idle session timeout
- timeout is set in seconds, there is no guarantee of exact precision. The only
   promise is that timeout will not happen earlier than specified
- timeout value could be set
   - at databases.conf (or firebird.conf) by DBA
   - at runtime at session level
   - value of zero means timeout is not set
- effective value of timeout is evaluated every time timeout timer is started
   using rules below:
   - if not set at session level then look at config
   - can't be greater than (non-zero) value at config
- timeout timer is started when user call of API function leaves the engine
- timeout timer is stopped when user call of API function enter the engine
- when timeout happens the engine immediately closes the attachment in the same
   way as with asyncronous attachment cancellation:
   - all active statements and cursors are closed
   - all active transactions are rolled back
   - next API call by client returns with primary error code isc_att_shutdown and
     secondary error code pointed to exact reason of cancellation
- timeout have no effect on system attachments (garbage collector, encryption etc)

   Once we get agreement on said above i go to the more details (API changes, SQL
support and so on)

Thanks for opinions,
Vlad

------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

Dimitry Sibiryakov-3
17.08.2016 20:44, Vlad Khorsun wrote:
>    - can't be greater than (non-zero) value at config

   I.e there is no way for DBA to make exceptions for some queries that are known to be
good, but long, right?

> - timeout tracked since the moment of the execution start, i.e. timer is not
>    reset by the fetch() calls
> - timeout timer is stopped when statement execution is finished - at the end of
>    exec() method or when the fetch() returns last record

   I.e interactive Delphi application that fetch only really shown records will get error
when user press "Down" key, or DBA won't be able to use this feature on database which
they are working with, at all, right?

--
   WBR, SD.

------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

Leyne, Sean
In reply to this post by Vlad Khorsun

>    Other mainstream DBMS already have support for active statement and
> idle session timeouts. I know no example of transaction timeout, though. I
> think, transactions timeouts could bring more troubles than goods and in
> many cases could be replaced by idle session timeouts. Therefore i offer to
> omit transaction timeouts from further discussion (but i not insist).

I agree that transaction timeouts could be problematic and should be omitted from the initial timeout implementation.


Sean


------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

Adriano dos Santos Fernandes-3
In reply to this post by Vlad Khorsun
Em 17/08/2016 15:44, Vlad Khorsun escreveu:

>
> a) Statement execution timeout
> - timeout is set in milliseconds, there is no guarantee of exact precision
>    (especially under high load). The only promise is that timeout will not
>    happen earlier than specified

Why milliseconds? If you're going to use seconds for session, seconds
here would be less confusing.

Considering API calls, network times, etc, I doubt < 1s would be good
for anything here.

> - if timeout happens at moment with no client activity (for example between two
>    fetch() calls) it will not take immediate effect, i.e. cursor remains open and
>    resources is not freed

What do you mean with "will not take immediate effect"?

If it waits for the next fetch, it will not be effective for its purpose
- track bad written applications.


Adriano

------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

Vlad Khorsun
18.08.2016 0:58, Adriano dos Santos Fernandes wrote:

> Em 17/08/2016 15:44, Vlad Khorsun escreveu:
>
>>
>> a) Statement execution timeout
>> - timeout is set in milliseconds, there is no guarantee of exact precision
>>    (especially under high load). The only promise is that timeout will not
>>    happen earlier than specified
>
> Why milliseconds? If you're going to use seconds for session, seconds
> here would be less confusing.

   Initially i also thought about seconds. And, yes, i don't like to have different
measures for similar things. But, technically, it is possible and it could be
useful for testing purposes. Note, it have no sense for session idle timeout to
be specified in ms. So i decided to discuss this point and it is good that you
raise this question :)

> Considering API calls, network times, etc, I doubt < 1s would be good
> for anything here.

   What about 2500 ms ? :)

   I don't insist on milliseconds for statement timeouts. I want to hear more
opinions. Btw, PG uses milliseconds :)

>> - if timeout happens at moment with no client activity (for example between two
>>    fetch() calls) it will not take immediate effect, i.e. cursor remains open and
>>    resources is not freed
>
> What do you mean with "will not take immediate effect"?
>
> If it waits for the next fetch, it will not be effective for its purpose
> - track bad written applications.

   Probably you right. I just described how statement cancellation works currently.

Regards,
Vlad


------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

[SPAM] Re: RFC: Timeouts

Vlad Khorsun
In reply to this post by Dimitry Sibiryakov-3
17.08.2016 22:07, Dimitry Sibiryakov wrote:
> 17.08.2016 20:44, Vlad Khorsun wrote:
>>    - can't be greater than (non-zero) value at config
>
>    I.e there is no way for DBA to make exceptions for some queries that are known to be
> good, but long, right?

   Right. Smart DBA should set timeout value big enough to allow such "known good" queries.

>> - timeout tracked since the moment of the execution start, i.e. timer is not
>>    reset by the fetch() calls
>> - timeout timer is stopped when statement execution is finished - at the end of
>>    exec() method or when the fetch() returns last record
>
>    I.e interactive Delphi application that fetch only really shown records will get error
> when user press "Down" key,

    If user fetch one record per hour - yes, such application should be better rewritten

> or DBA won't be able to use this feature on database which
> they are working with, at all, right?

   DBA should decide what is more important for DB. Currently, DBA have no way to control.

Vlad


------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM] Re: RFC: Timeouts

liviuslivius@poczta.onet.pl
Hi Vlad,

>> I.e interactive Delphi application that fetch only really shown records will get error
> > when user press "Down" key,
>
>     If user fetch one record per hour - yes, such application should be better rewritten

Is this query in different state that can be distinguished from "running" queries? I see that yes.
And should be possibility to exclude it from this feature or make 2 different settings for that.


regards,
Karol Bieniaszewski

------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

Dimitry Sibiryakov-3
In reply to this post by Vlad Khorsun
18.08.2016 8:47, Vlad Khorsun wrote:
>    What about 2500 ms ? :)
>     If user fetch one record per hour - yes,

   2500 ms is much less that an hour, you know...

> such application should be better rewritten

   It will require to implement background fetch. Much out of skills for most Delphi
programmers.

>    DBA should decide what is more important for DB. Currently, DBA have no way to control.

   IMHO, in proposed form it won't have more control, because there will be no sane range
of timeouts values that could be useful to kill wrong DMLs but let selects to live.

   I would suggest to apply the timeout to each single execute() and fetch() call. This
way selects will have the same quotes as DML.

--
   WBR, SD.

------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

Vlad Khorsun
In reply to this post by liviuslivius@poczta.onet.pl
18.08.2016 10:08, liviuslivius пишет:
> Hi Vlad,
>
>>> I.e interactive Delphi application that fetch only really shown records will get error
>>> when user press "Down" key,
>>
>>     If user fetch one record per hour - yes, such application should be better rewritten
>
> Is this query in different state that can be distinguished from "running" queries? I see that yes.
> And should be possibility to exclude it from this feature or make 2 different settings for that.

   Could you show good reasons to do it ? Real use case also welcome.

Regards,
Vlad

------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

liviuslivius@poczta.onet.pl
In reply to this post by liviuslivius@poczta.onet.pl


W dniu 2016-08-18 09:26:22 użytkownik Vlad Khorsun <[hidden email]> napisał:

> 18.08.2016 10:08, liviuslivius пишет:
> > Hi Vlad,
> >
> >>> I.e interactive Delphi application that fetch only really shown records will get error
> >>> when user press "Down" key,
> >>
> >>     If user fetch one record per hour - yes, such application should be better rewritten
> >
> > Is this query in different state that can be distinguished from "running" queries? I see that yes.
> > And should be possibility to exclude it from this feature or make 2 different settings for that.
>
>    Could you show good reasons to do it ? Real use case also welcome.
>
> Regards,
> Vlad
>
> ------------------------------------------------------------------------------
> Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
>

Query timeout is good for queries that consume CPU resources and not finished in limited time.
But queries that feth e.g. 100 records for user and wait to fetch rest do not consume much resources
and user can fetch (rest or next portion) e.g. after 10 minutes.
And that queries are not problem for DBA. Yes, such queries consume some resources but not extensivly.
But feature like timeout is for queries that can utilize all server resources.

regards,
Karol Bieniaszewski



------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

Molnár Attila
Hi!

I think timeout should depend on these independent factors :
- transaction parameters : RORC = false else true
- first fetch : not possible at the timeout moment = true else false
- average fetch time (start to measure after the first fetch) : very
high (config) = true else fales

and optionally (config)
- plan : has NATURAL on some table (config) = true else false


If at least one of the factor is true then cancel the statement, else
let the statement live.


On 2016.08.18. 9:37, liviuslivius wrote:

>
> W dniu 2016-08-18 09:26:22 użytkownik Vlad Khorsun <[hidden email]> napisał:
>> 18.08.2016 10:08, liviuslivius пишет:
>>> Hi Vlad,
>>>
>>>>> I.e interactive Delphi application that fetch only really shown records will get error
>>>>> when user press "Down" key,
>>>>      If user fetch one record per hour - yes, such application should be better rewritten
>>> Is this query in different state that can be distinguished from "running" queries? I see that yes.
>>> And should be possibility to exclude it from this feature or make 2 different settings for that.
>>     Could you show good reasons to do it ? Real use case also welcome.
>>
>> Regards,
>> Vlad
>>
>> ------------------------------------------------------------------------------
>> Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
>>
> Query timeout is good for queries that consume CPU resources and not finished in limited time.
> But queries that feth e.g. 100 records for user and wait to fetch rest do not consume much resources
> and user can fetch (rest or next portion) e.g. after 10 minutes.
> And that queries are not problem for DBA. Yes, such queries consume some resources but not extensivly.
> But feature like timeout is for queries that can utilize all server resources.
>
> regards,
> Karol Bieniaszewski
>
>
>
> ------------------------------------------------------------------------------
> Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel


------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

Vlad Khorsun
In reply to this post by liviuslivius@poczta.onet.pl
18.08.2016 10:37, liviuslivius wrote:

>
>
> W dniu 2016-08-18 09:26:22 użytkownik Vlad Khorsun <[hidden email]> napisał:
>> 18.08.2016 10:08, liviuslivius пишет:
>>> Hi Vlad,
>>>
>>>>> I.e interactive Delphi application that fetch only really shown records will get error
>>>>> when user press "Down" key,
>>>>
>>>>     If user fetch one record per hour - yes, such application should be better rewritten
>>>
>>> Is this query in different state that can be distinguished from "running" queries? I see that yes.
>>> And should be possibility to exclude it from this feature or make 2 different settings for that.
>>
>>    Could you show good reasons to do it ? Real use case also welcome.
...
> Query timeout is good for queries that consume CPU resources and not finished in limited time.

   CPU is important but not the only resource.

> But queries that feth e.g. 100 records for user and wait to fetch rest do not consume much resources
> and user can fetch (rest or next portion) e.g. after 10 minutes.

   It is too generic. Query could use 1GB for sorting and fetch very slow

> And that queries are not problem for DBA. Yes, such queries consume some resources but not extensivly.

   See above, it is not as easy. Also, such queries not allows to commit corresponding transaction
and it could lead to blocked garbage collection, growing active part of TIP and force other attachments
to consume more resources.

> But feature like timeout is for queries that can utilize all server resources.


Regards,
Vlad


------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

liviuslivius@poczta.onet.pl
In reply to this post by liviuslivius@poczta.onet.pl
W dniu 2016-08-18 10:01:17 użytkownik Molnár Attila <[hidden email]> napisał:
> Hi!
>
> I think timeout should depend on these independent factors :
> - transaction parameters : RORC = false else true

why do you need to take different action for readonly transaction?
If statement consume to much resources during time set in timeout why not stop it like in other transactions?

> - first fetch : not possible at the timeout moment = true else false

agree

> - average fetch time (start to measure after the first fetch) : very

i do not know if average fetch time is good here
any fetch taking too much time should be treated as timeout


> high (config) = true else fales
>
> and optionally (config)
> - plan : has NATURAL on some table (config) = true else false

disagree - plan natural can be better in many cases than index retrivial
and have not correlation with timeouts feature

regards,
Karol Bieniaszewski

------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

liviuslivius@poczta.onet.pl
In reply to this post by liviuslivius@poczta.onet.pl
> > Query timeout is good for queries that consume CPU resources and not finished in limited time.
>
>    CPU is important but not the only resource.
Yes, i know - time cost to take action as a whole like cpu + I/O + sync  should cause timeout -
statement as a whole

>
> > But queries that feth e.g. 100 records for user and wait to fetch rest do not consume much resources
> > and user can fetch (rest or next portion) e.g. after 10 minutes.
>
>    It is too generic. Query could use 1GB for sorting and fetch very slow

yes and fetch time of "portion" should be considered by timeout
or maybe sumarize of fetches time -
but memory consumption is different feature not releated to timeouts
query can eat 1GB but run very fast and other can eat 10MB but work very very long

>
> > And that queries are not problem for DBA. Yes, such queries consume some resources but not extensivly.
>
>    See above, it is not as easy. Also, such queries not allows to commit corresponding transaction
> and it could lead to blocked garbage collection, growing active part of TIP and force other attachments
> to consume more resources.

yes but this is different feature like transaction timeouts - which is not so important from my POV
i only talking about statement timeouts.
And most systems i have seen operate without without transaction timeout feature
because they check if some long running transaction is pending (query to MON$Transaction) and inform admins about details


regards,
Karol Bieniaszewski

------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

liviuslivius@poczta.onet.pl
In reply to this post by liviuslivius@poczta.onet.pl
If i can start general discussion..

do you really use such feature in real systems?
I saw this in MSSQL environment and what was advice of DBA when someone reach timeout?
Increase timeout settings...

Kiling statement or transaction is not good as a general solution
It must be customized for situations.

I suppose better feature will be "timeout messaging" - something like
TRIGGER ON STATEMENT_TIMEOUT
TRIGGER ON TRANSACTION_TIMEOUT

and inside it we have access to MON$ tables and we can cancel statement, transaction if we need.
Inside we can check e.g. individual context variables which eovercome some default settings.
We can post event and some admin client application can take some action
i suppose you can run into more samples


P.S.
What about statements executing query to external database by EXECUTE STATEMENT?

regards,
Karol Bieniaszewski





------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

Molnár Attila
This is a GREAT idea! +1

And you might define the timeout in the CREATE/ALTER command (no need
for config).

On 2016.08.18. 12:04, liviuslivius wrote:
> Kiling statement or transaction is not good as a general solution
> It must be customized for situations.
>
> I suppose better feature will be "timeout messaging" - something like
> TRIGGER ON STATEMENT_TIMEOUT
> TRIGGER ON TRANSACTION_TIMEOUT
>
> and inside it we have access to MON$ tables and we can cancel statement, transaction if we need.


------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

Vlad Khorsun
In reply to this post by Dimitry Sibiryakov-3
18.08.2016 10:14, Dimitry Sibiryakov wrote:
> 18.08.2016 8:47, Vlad Khorsun wrote:
>>    What about 2500 ms ? :)
>>     If user fetch one record per hour - yes,
>
>    2500 ms is much less that an hour, you know...

   Do you intentionally mixed timeouts set by DBA and by app developer or you really not
understand what is for what ?

>> such application should be better rewritten
>
>    It will require to implement background fetch.

   Bad joke.

>>    DBA should decide what is more important for DB. Currently, DBA have no way to control.
>
>    IMHO, in proposed form it won't have more control, because there will be no sane range
> of timeouts values that could be useful to kill wrong DMLs but let selects to live.

   Global timeout set by DBA usually measured in tens of minutes or in hours. Are you advocate
applications which could hold open cursor for a hours ?

>    I would suggest to apply the timeout to each single execute() and fetch() call. This
> way selects will have the same quotes as DML.

   It is wrong assumption. If cursor can't be fetched during global timeout then timeout is
set wrong or application should be fixed. From the other side, it is impossible to guess
correct timeout value in the case of never\randomly\rare fetched cursors so i don't care
about it.

   If you going to say that statement timeouts is useless for such apps - i not object.
Note, nobody forced to use it.

Vlad


------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

Dimitry Sibiryakov-3
18.08.2016 15:33, Vlad Khorsun wrote:
>    Do you intentionally mixed timeouts set by DBA and by app developer or you really not
> understand what is for what ?

   Yes, I don't understand.

>    Global timeout set by DBA usually measured in tens of minutes or in hours.

   Could you provide an example what such enormous timeouts can be good for?
   I'm sure that almost everyone who hear about this feature first time will imagine
statement timeouts in a couple of seconds, not more.

>  Are you advocate applications which could hold open cursor for a hours ?

   I know at least one such application.

--
   WBR, SD.

------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

Pavel Cisar
In reply to this post by liviuslivius@poczta.onet.pl
Hi,

that's very neat idea. +1

best regards
Pavel Cisar
IBPhoenix

Dne 18.8.2016 v 12:04 liviuslivius napsal(a):

> If i can start general discussion..
>
> do you really use such feature in real systems?
> I saw this in MSSQL environment and what was advice of DBA when someone reach timeout?
> Increase timeout settings...
>
> Kiling statement or transaction is not good as a general solution
> It must be customized for situations.
>
> I suppose better feature will be "timeout messaging" - something like
> TRIGGER ON STATEMENT_TIMEOUT
> TRIGGER ON TRANSACTION_TIMEOUT
>
> and inside it we have access to MON$ tables and we can cancel statement, transaction if we need.
> Inside we can check e.g. individual context variables which eovercome some default settings.
> We can post event and some admin client application can take some action
> i suppose you can run into more samples
>
>
> P.S.
> What about statements executing query to external database by EXECUTE STATEMENT?
>
> regards,
> Karol Bieniaszewski
>
>
>
>
>
> ------------------------------------------------------------------------------
> Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
>

------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|

Re: RFC: Timeouts

Vlad Khorsun
In reply to this post by Dimitry Sibiryakov-3
18.08.2016 16:41, Dimitry Sibiryakov пишет:

> 18.08.2016 15:33, Vlad Khorsun wrote:
>>    Do you intentionally mixed timeouts set by DBA and by app developer or you really not
>> understand what is for what ?
>
>    Yes, I don't understand.
>
>>    Global timeout set by DBA usually measured in tens of minutes or in hours.
>
>    Could you provide an example what such enormous timeouts can be good for?
>    I'm sure that almost everyone who hear about this feature first time will imagine
> statement timeouts in a couple of seconds, not more.

   Global timeout is a last line of defense for DBA against bad apps, wrong queries,
developer mistakes, unlucky days (dropped some indices last week but now some queries
got crazy) and so on. It is *last* line, therefore it should be used with maximum care.

   Application-level timeouts is for developers, not for DBA's, and reasons to use it
completely different - starting from stress-testing and up to the enterprise policy
such as "no OLTP statements should run more than 2 sec". Therefore such timeouts should
be set in more individual way - at session and\or query level.

   For example: as developer you may like to set session level timeout in your dev tool
to avoid need to kill manually query you writting and forget to specify join condition
for 25th and 26th tables in it.

>>  Are you advocate applications which could hold open cursor for a hours ?
>
>    I know at least one such application.

   It is allowed for you to not use timeouts with this app :)

Vlad

------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
12345