You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Mikael Sundberg <Mi...@artificial-solutions.com> on 2008/10/09 19:01:32 UTC
cancel a running query?
Hi
is there anyway to cancel a running query?
i know you can se the current running querys with for example:
select XID,STATUS,SQL_TEXT from SYSCS_DIAG.TRANSACTION_TABLE
where SQL_TEXT is not NULL;
but if you accidently enter a query that takes forever how do i cancel it?
im using the latest derby.
/Micke
Re: cancel a running query?
Posted by Rick Hillegas <Ri...@Sun.COM>.
I have logged an enhancement request to help track this issue:
https://issues.apache.org/jira/browse/DERBY-3908
Regards,
-Rick
Knut Anders Hatlen wrote:
> Kristian Waagan <Kr...@Sun.COM> writes:
>
>
>> Rick Hillegas wrote:
>>
>>> Hi Dirk,
>>>
>>> Yes, Derby does support Statement.setQueryTimeout(), at least as
>>> verified by the Derby regression test
>>> org.apache.derbyTesting.functionTests.tests.lang.SetQueryTimeoutTest. That's
>>> a good defense if you suspect in advance that your queries may
>>> ramble on. It leaves the lingering issue of how to cancel a runaway
>>> query that you (or someone else) didn't limit before the query
>>> started.
>>>
>> Correct.
>> It should be noted though, that your mileage may vary with
>> setQueryTimeout. I assume that mechanism requires the engine to check
>> a flag to see if it should abort, and if the code is "stuck" in an
>> area where there are no such checks you might have to wait longer than
>> the specified timeout value.
>> If anyone has been using it, it would be nice to get some feedback on
>> how well it is working/behaving.
>>
>> Is it possible to use the same machinery to add support for timing out
>> a running transaction?
>> Is this something that would be needed by many users?
>> I suppose the client (dba, user etc) would normally have an idea
>> whether the transaction/query will finish in a few seconds or a few
>> days before issuing it.
>>
>
> There is an undocumented feature that you may use in embedded mode:
>
> Connection conn = ....;
> org.apache.derby.impl.jdbc.EmbedConnection embConn =
> ((org.apache.derby.impl.jdbc.EmbedConnection) conn;
> embConn.cancelRunningStatement();
>
> It uses the same mechanism as the statement timeout. That is, it sets a
> flag that is checked now and then during execution.
>
> Note that this method is not part of Derby's public API and could change
> or be removed any time.
>
>
Re: cancel a running query?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Kristian Waagan <Kr...@Sun.COM> writes:
> Rick Hillegas wrote:
>> Hi Dirk,
>>
>> Yes, Derby does support Statement.setQueryTimeout(), at least as
>> verified by the Derby regression test
>> org.apache.derbyTesting.functionTests.tests.lang.SetQueryTimeoutTest. That's
>> a good defense if you suspect in advance that your queries may
>> ramble on. It leaves the lingering issue of how to cancel a runaway
>> query that you (or someone else) didn't limit before the query
>> started.
>
> Correct.
> It should be noted though, that your mileage may vary with
> setQueryTimeout. I assume that mechanism requires the engine to check
> a flag to see if it should abort, and if the code is "stuck" in an
> area where there are no such checks you might have to wait longer than
> the specified timeout value.
> If anyone has been using it, it would be nice to get some feedback on
> how well it is working/behaving.
>
> Is it possible to use the same machinery to add support for timing out
> a running transaction?
> Is this something that would be needed by many users?
> I suppose the client (dba, user etc) would normally have an idea
> whether the transaction/query will finish in a few seconds or a few
> days before issuing it.
There is an undocumented feature that you may use in embedded mode:
Connection conn = ....;
org.apache.derby.impl.jdbc.EmbedConnection embConn =
((org.apache.derby.impl.jdbc.EmbedConnection) conn;
embConn.cancelRunningStatement();
It uses the same mechanism as the statement timeout. That is, it sets a
flag that is checked now and then during execution.
Note that this method is not part of Derby's public API and could change
or be removed any time.
--
Knut Anders
Re: cancel a running query?
Posted by Kristian Waagan <Kr...@Sun.COM>.
On 12.10.08 04:35, Stephan van Loendersloot (LIST) wrote:
>
> Kristian Waagan wrote:
>> Rick Hillegas wrote:
>>> Hi Dirk,
>>>
>>> Yes, Derby does support Statement.setQueryTimeout(), at least as
>>> verified by the Derby regression test
>>> org.apache.derbyTesting.functionTests.tests.lang.SetQueryTimeoutTest.
>>> That's a good defense if you suspect in advance that your queries may
>>> ramble on. It leaves the lingering issue of how to cancel a runaway
>>> query that you (or someone else) didn't limit before the query started.
>>
>> Correct.
>> It should be noted though, that your mileage may vary with
>> setQueryTimeout. I assume that mechanism requires the engine to check
>> a flag to see if it should abort, and if the code is "stuck" in an
>> area where there are no such checks you might have to wait longer than
>> the specified timeout value.
>> If anyone has been using it, it would be nice to get some feedback on
>> how well it is working/behaving.
>>
>> Is it possible to use the same machinery to add support for timing out
>> a running transaction?
>> Is this something that would be needed by many users?
>> I suppose the client (dba, user etc) would normally have an idea
>> whether the transaction/query will finish in a few seconds or a few
>> days before issuing it.
>>
>>
>> regards,
> Hi Kristian,
>
> I had the intention to reply to this earlier, but work and time limits
> didn't help very much...
>
> Anyway, here's your feedback. The setQueryTimeOut() method works like a
> charm... we really optimize our queries for Derby. It's easy, since in
> our systems every dynamic 'new' query runs through
> SYSCS_GET_RUNTIMESTATISTICS, which has a 24 hour 'cache'. This enables
> us to to log any anomaly that might occur.
Hello, Stephan,
It's great to hear that setQueryTimeOut is working well in Derby.
>
> Sometimes the anomaly rears it's ugly head when the index statistics get
> stale, which is why I'm really looking forward to the 10.5 release.
> However, various other unpredictable results can happen when working
> with millions of tuples and *a lot* of concurrency, no matter how much
> unit-testing was done.
>
> We hardly need it anymore since we switched from another open source
> RDBMS to Derby, but hey, it doesn't hurt performance, so we use
> setQueryTimeOut on every query, and our software does exactly what it
> needs to do: quit->log->inform instantly.
>
> We're able to solve problems of unexpected long running queries in a
> short timeframe and setQueryTimeOut really helps.
>
> I haven't had the time yet (again, sigh) to look at the codebase to see
> how it's implemented, but we tend not to use simple time differences
> like System.currentMilliseconds/nanoSeconds to measure the quality of a
> query. Instead we use ThreadMXBean or hook into JNI to get the actual
> processor-time on our (Linux) production servers, even regarding the
> sloppy delays on some (Windows) development systems.
>
>
> Conclusion: setQueryTimeout works... our logs show messages within
> nanoseconds.
Sounds like you have a good system going. I'm sure other people would be
happy to learn from your experiences.
>
>
> Now, to be able to actually identify queries that do not use intended
> indices, and as such may be slow, I've written some simple classes that
> parse the output of SYSCS_GET_RUNTIME_STATISTICS to show per table/join
> in a MySQL-EXPLAIN like format how Derby does things (excuse the format,
> you may need a wide screen):
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Table |RowResultSetType |ResultSetType|IndexType
> |IndexName
> |IsolationLevel|LockingType |ScanType|RowsVisited|RowsQualified|
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> PRODUCT_SUB_GROUPS|Index Row to Base Row|Index Scan |index
> |IX_PRODUCT_SUB_GROUPS_ENCODED_TITLE |read
> committed|instantaneous share row|btree |1 |1 |
> PRODUCTS_UNITS | |Table Scan |
> | |read committed|share
> row |heap |391 |308 |
> PRODUCTS |Index Row to Base Row|Index Scan
> |constraint|PK_PRODUCTS_PRODUCT_ID |read
> committed|share row |btree |308 |308 |
> PRODUCT_SUB_GROUPS|Index Row to Base Row|Index Scan
> |constraint|PK_PRODUCT_SUB_GROUPS_PRODUCT_SUB_GROUP_ID |read
> committed|share row |btree |262 |262 |
> PRODUCT_GROUPS |Index Row to Base Row|Index Scan
> |constraint|PK_PRODUCT_GROUPS_PRODUCT_GROUP_ID |read
> committed|share row |btree |248 |248 |
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
>
> Though this may seem unrelated to the raised issue, it is related to us
> and using setQueryTimeout, as well as the issue raised about a week ago
> on the dev-list, (Question regarding runtimestatistics and join order),
> which is why I brought it here...
>
> It's late in the Netherlands, about 4/5 o'clock and I really need to get
> some sleep now... but I hope to have cleared that setQueryTimeout
> actually DOES work like it's supposed to.
>
> The last part of my post is to remind myself to contribute the code that
> parses the statistics, if and when anyonye finds that it can be useful
> (I'll get back on that on the dev-list).
There have been talks about presenting the runtime statistics in a more
parser-friendly format, but I don't think anyone has started working on
it yet. I appreciate that you took the time to share your information
with us. If you want to contribute some of your code, creating a Jira
issue would be an option [1].
Thanks a lot for your feedback!
--
Kristian
[1] https://issues.apache.org/jira/browse/DERBY
>
>
>
> Regards,
>
> Stephan van Loendersloot.
Re: cancel a running query?
Posted by "Stephan van Loendersloot (LIST)" <st...@republika.nl>.
Kristian Waagan wrote:
> Rick Hillegas wrote:
>> Hi Dirk,
>>
>> Yes, Derby does support Statement.setQueryTimeout(), at least as
>> verified by the Derby regression test
>> org.apache.derbyTesting.functionTests.tests.lang.SetQueryTimeoutTest.
>> That's a good defense if you suspect in advance that your queries may
>> ramble on. It leaves the lingering issue of how to cancel a runaway
>> query that you (or someone else) didn't limit before the query started.
>
> Correct.
> It should be noted though, that your mileage may vary with
> setQueryTimeout. I assume that mechanism requires the engine to check
> a flag to see if it should abort, and if the code is "stuck" in an
> area where there are no such checks you might have to wait longer than
> the specified timeout value.
> If anyone has been using it, it would be nice to get some feedback on
> how well it is working/behaving.
>
> Is it possible to use the same machinery to add support for timing out
> a running transaction?
> Is this something that would be needed by many users?
> I suppose the client (dba, user etc) would normally have an idea
> whether the transaction/query will finish in a few seconds or a few
> days before issuing it.
>
>
> regards,
Hi Kristian,
I had the intention to reply to this earlier, but work and time limits
didn't help very much...
Anyway, here's your feedback. The setQueryTimeOut() method works like a
charm... we really optimize our queries for Derby. It's easy, since in
our systems every dynamic 'new' query runs through
SYSCS_GET_RUNTIMESTATISTICS, which has a 24 hour 'cache'. This enables
us to to log any anomaly that might occur.
Sometimes the anomaly rears it's ugly head when the index statistics get
stale, which is why I'm really looking forward to the 10.5 release.
However, various other unpredictable results can happen when working
with millions of tuples and *a lot* of concurrency, no matter how much
unit-testing was done.
We hardly need it anymore since we switched from another open source
RDBMS to Derby, but hey, it doesn't hurt performance, so we use
setQueryTimeOut on every query, and our software does exactly what it
needs to do: quit->log->inform instantly.
We're able to solve problems of unexpected long running queries in a
short timeframe and setQueryTimeOut really helps.
I haven't had the time yet (again, sigh) to look at the codebase to see
how it's implemented, but we tend not to use simple time differences
like System.currentMilliseconds/nanoSeconds to measure the quality of a
query. Instead we use ThreadMXBean or hook into JNI to get the actual
processor-time on our (Linux) production servers, even regarding the
sloppy delays on some (Windows) development systems.
Conclusion: setQueryTimeout works... our logs show messages within
nanoseconds.
Now, to be able to actually identify queries that do not use intended
indices, and as such may be slow, I've written some simple classes that
parse the output of SYSCS_GET_RUNTIME_STATISTICS to show per table/join
in a MySQL-EXPLAIN like format how Derby does things (excuse the format,
you may need a wide screen):
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table |RowResultSetType |ResultSetType|IndexType
|IndexName
|IsolationLevel|LockingType |ScanType|RowsVisited|RowsQualified|
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRODUCT_SUB_GROUPS|Index Row to Base Row|Index Scan |index
|IX_PRODUCT_SUB_GROUPS_ENCODED_TITLE |read
committed|instantaneous share row|btree |1 |1 |
PRODUCTS_UNITS | |Table Scan |
| |read committed|share
row |heap |391 |308 |
PRODUCTS |Index Row to Base Row|Index Scan
|constraint|PK_PRODUCTS_PRODUCT_ID |read
committed|share row |btree |308 |308 |
PRODUCT_SUB_GROUPS|Index Row to Base Row|Index Scan
|constraint|PK_PRODUCT_SUB_GROUPS_PRODUCT_SUB_GROUP_ID |read
committed|share row |btree |262 |262 |
PRODUCT_GROUPS |Index Row to Base Row|Index Scan
|constraint|PK_PRODUCT_GROUPS_PRODUCT_GROUP_ID |read
committed|share row |btree |248 |248 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Though this may seem unrelated to the raised issue, it is related to us
and using setQueryTimeout, as well as the issue raised about a week ago
on the dev-list, (Question regarding runtimestatistics and join order),
which is why I brought it here...
It's late in the Netherlands, about 4/5 o'clock and I really need to get
some sleep now... but I hope to have cleared that setQueryTimeout
actually DOES work like it's supposed to.
The last part of my post is to remind myself to contribute the code that
parses the statistics, if and when anyonye finds that it can be useful
(I'll get back on that on the dev-list).
Regards,
Stephan van Loendersloot.
Re: cancel a running query?
Posted by Kristian Waagan <Kr...@Sun.COM>.
Rick Hillegas wrote:
> Hi Dirk,
>
> Yes, Derby does support Statement.setQueryTimeout(), at least as
> verified by the Derby regression test
> org.apache.derbyTesting.functionTests.tests.lang.SetQueryTimeoutTest.
> That's a good defense if you suspect in advance that your queries may
> ramble on. It leaves the lingering issue of how to cancel a runaway
> query that you (or someone else) didn't limit before the query started.
Correct.
It should be noted though, that your mileage may vary with
setQueryTimeout. I assume that mechanism requires the engine to check a
flag to see if it should abort, and if the code is "stuck" in an area
where there are no such checks you might have to wait longer than the
specified timeout value.
If anyone has been using it, it would be nice to get some feedback on
how well it is working/behaving.
Is it possible to use the same machinery to add support for timing out a
running transaction?
Is this something that would be needed by many users?
I suppose the client (dba, user etc) would normally have an idea whether
the transaction/query will finish in a few seconds or a few days before
issuing it.
regards,
--
Kristian
>
> Hope this helps,
> -Rick
>
> Dirk Flachbart wrote:
>>
>> Doesn't Derby support Statement.setQueryTimeout() ?
>>
>>
>> Dirk
>>
>>
>> derby@segel.com <ma...@segel.com> wrote:
>>> A rollback doesn't "cancel" a query. Meaning that you can't stop the
>>> query
>>> until it completes.
>>>
>>> The rollback will reverse the results of a transaction after the SQL
>>> statement completes. A query is unaffected because its not a SQL
>>> statement
>>> that changes the database. (query == SELECT, other statements
>>> UPDATE,DELETE,
>>> INSERT, etc ... do change the data.)
>>>
>>> So if you start a query, there isn't a way to have some sort of query
>>> timeout or call to prematurely end the query. (Perhaps you need to
>>> consider
>>> threading your app...)
>>>
>>> HTH
>>>
>>> -Mike
>>>
>>>
>>>
>>>> -----Original Message-----
>>>> From: Dag.Wanvik@Sun.COM <ma...@Sun.COM>
>>>> [mailto:Dag.Wanvik@Sun.COM]
>>>> Sent: Thursday, October 09, 2008 9:09 PM
>>>> To: Derby Discussion
>>>> Subject: Re: cancel a running query?
>>>>
>>>> Rick Hillegas <Ri...@Sun.COM>
>>>> <ma...@Sun.COM> writes:
>>>>
>>>>
>>>>> conn.rollback();
>>>>>
>>>> Doesn't the rollback block on synchronizion on the connection?
>>>>
>>>> Dag
>>>>
>>>
>>>
>>
>
Re: cancel a running query?
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Dirk,
Yes, Derby does support Statement.setQueryTimeout(), at least as
verified by the Derby regression test
org.apache.derbyTesting.functionTests.tests.lang.SetQueryTimeoutTest.
That's a good defense if you suspect in advance that your queries may
ramble on. It leaves the lingering issue of how to cancel a runaway
query that you (or someone else) didn't limit before the query started.
Hope this helps,
-Rick
Dirk Flachbart wrote:
>
> Doesn't Derby support Statement.setQueryTimeout() ?
>
>
> Dirk
>
>
> derby@segel.com <ma...@segel.com> wrote:
>> A rollback doesn't "cancel" a query. Meaning that you can't stop the query
>> until it completes.
>>
>> The rollback will reverse the results of a transaction after the SQL
>> statement completes. A query is unaffected because its not a SQL statement
>> that changes the database. (query == SELECT, other statements UPDATE,DELETE,
>> INSERT, etc ... do change the data.)
>>
>> So if you start a query, there isn't a way to have some sort of query
>> timeout or call to prematurely end the query. (Perhaps you need to consider
>> threading your app...)
>>
>> HTH
>>
>> -Mike
>>
>>
>>
>>> -----Original Message-----
>>> From: Dag.Wanvik@Sun.COM <ma...@Sun.COM> [mailto:Dag.Wanvik@Sun.COM]
>>> Sent: Thursday, October 09, 2008 9:09 PM
>>> To: Derby Discussion
>>> Subject: Re: cancel a running query?
>>>
>>> Rick Hillegas <Ri...@Sun.COM> <ma...@Sun.COM> writes:
>>>
>>>
>>>> conn.rollback();
>>>>
>>> Doesn't the rollback block on synchronizion on the connection?
>>>
>>> Dag
>>>
>>
>>
>
RE: cancel a running query?
Posted by de...@segel.com.
A rollback doesn't "cancel" a query. Meaning that you can't stop the query
until it completes.
The rollback will reverse the results of a transaction after the SQL
statement completes. A query is unaffected because its not a SQL statement
that changes the database. (query == SELECT, other statements UPDATE,DELETE,
INSERT, etc ... do change the data.)
So if you start a query, there isn't a way to have some sort of query
timeout or call to prematurely end the query. (Perhaps you need to consider
threading your app...)
HTH
-Mike
> -----Original Message-----
> From: Dag.Wanvik@Sun.COM [mailto:Dag.Wanvik@Sun.COM]
> Sent: Thursday, October 09, 2008 9:09 PM
> To: Derby Discussion
> Subject: Re: cancel a running query?
>
> Rick Hillegas <Ri...@Sun.COM> writes:
>
> > conn.rollback();
>
> Doesn't the rollback block on synchronizion on the connection?
>
> Dag
Re: cancel a running query?
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Rick Hillegas <Ri...@Sun.COM> writes:
> conn.rollback();
Doesn't the rollback block on synchronizion on the connection?
Dag
Re: cancel a running query?
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Mikael,
If you can get your hands on the connection which ran the query, then I
think that you can kill the query by rolling back the transaction and
closing the connection:
conn.rollback();
conn.close();
Hope this helps,
-Rick
Mikael Sundberg wrote:
>
> Hi
> is there anyway to cancel a running query?
> i know you can se the current running querys with for example:
> select XID,STATUS,SQL_TEXT from SYSCS_DIAG.TRANSACTION_TABLE
> where SQL_TEXT is not NULL;
>
> but if you accidently enter a query that takes forever how do i cancel it?
> im using the latest derby.
>
> /Micke
>