Fw: Can it be that Derby (in-memory) is deadlocking on this query? (UPDATE)
the more I am working through this issue, the more I get convinced that
it's a Derby issue.
When the select statement is modified slightly (not using joining of
tables on themselves, but rather using IN), then everything works without
a hiccup:
SELECT
P1.ID ,
R1.description
P1.BOOK
P1.NOMINAL
P1.NOMINAL * R1.Factor
FROM
P_TABLE P1,
R_TABLE R1
WHERE
P1.IN_ID = R1.IN_ID AND
R1.IN_ID in (
select
R2.IN_ID
from
P_TABLE P2,
R_TABLE R2
where
P2.IN_ID = R2.IN_ID AND
P2.NOMINAL <> 0 AND
R2.IType='X'
GROUP BY R2.IN_ID
HAVING COUNT(*) >1
)
So, this leads me to believe that SELECT statements such as this causes a
problem within Derby:
select
*
from
TABLE T1,
(
select from TABLE T2
) as M
where
M. = T1.
- Forwarded by Pavel Bortnovskiy/JEFCO on 01/20/2011 03:55 PM -
From:
Pavel Bortnovskiy/JEFCO
To:
Derby Discussion
Date:
01/20/2011 01:28 PM
Subject:
Can it be that Derby (in-memory) is deadlocking on this query?
Hello:
while running my application, I noticed that when the following query
(which uses a subquery with tables joining on themselves) is executed, the
application processes 185 records and then sits indefinitely in
ResultSet.next() method:
SELECT
P1.ID ,
R1.description
P1.BOOK
P1.NOMINAL
P1.NOMINAL * R1.Factor
FROM
P_TABLE P1,
R_TABLE R1,
(
select
R2.IN_ID
from
P_TABLE P2,
R_TABLE R2
where
P2.IN_ID = R2.IN_ID AND
P2.NOMINAL <> 0 AND
R2.IType='X'
GROUP BY R2.IN_ID
HAVING COUNT(*) >1
) as MULTI
WHERE
P1.IN_ID = R1.IN_ID AND
MULTI.IN_ID = R1.IN_ID
Then I tried running AquaDataStudio with this query and it's been over 16
minutes without any results back:
However, when I run the subquery itself, it executes practically
instanteneously:
And if I replace the subquery with where R1.IN in ('P32764', 'P32765', ...
[all results from subquery]), it executes in a few ms:
I have a suspicion that Derby (which is running in in-memory only mode) is
deadlocking.
What can I do on my end (without exposing our data) to help you diagnose
this.
Please respond as soon as you can, since this is quite important and
urgent.
Thank you,
Pavel.
Jefferies archives and monitors outgoing and incoming e-mail. The contents of
this email, including any attachments, are confidential to the ordinary user of
the email address to which it was addressed. If you are not the addressee of
this email you may not copy, forward, disclose or otherwise use it or any part
of it in any form whatsoever. This email may be produced at the request of
regulators or in connection with civil litigation. Jefferies accepts no
liability for any errors or omissions arising as a result of transmission. Use
by other than intended recipients is prohibited. In the United Kingdom,
Jefferies operates as Jefferies International Limited; registered in England:
no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London
EC4V 3BJ. Jefferies International Limited is authorised and regulated by the
Financial Services Authority.<><><>
Re: Can it be that Derby (in-memory) is deadlocking on this query? (UPDATE)
Hello, Lily:
thank you for your response.
while I am still trying to create a test for Derby users, your experiment
is not the same. Notice that in our query we are joining on the same
table. So, T2 is not a different table. It's still the same T1 table, but
aliased as T2...
try something like this:
select
*
from
TABLE T1,
(
select
T2.col
from
TABLE T2
) as M
where
M.col = T1.col
Once I succeed reproducing the error, I will show my test.
What makes me suspect that there is a problem with Derby is two-fold:
- our code seems to hang in ResultSet.next(), which is a call to Derby
JDBC API implementation
- accessing those in-memory tables with AquaDataStudio and running the
same query never returns/succeeds (I stopped after 20 minutes of having it
running, where as the subqueries themselves run in a few milliseconds).
AquaDataStudio is a commercial product and I doubt that it breaks
precisely at the same point and query as our code...
P.
From:
Lily Wei
To:
pbortnovs...@jefferies.com
Cc:
lily...@yahoo.com
Date:
01/20/2011 05:25 PM
Subject:
Re: Can it be that Derby (in-memory) is deadlocking on this query?
(UPDATE)
Hi Pavel:
How are you? This is Lily Wei. I am one of the Derby committers. I am
also doing projects for clients.
I am curious in turn of how do you draw to conclusion that this is
one of Derby's problem.
What error message you get from Derby? What message is print to derby.log.
I did a simple experiment and the query seem to work. However, it is
totally possible that your query is different than my query.
For example:
=
create table T1 (col1 int, col2 char(20));
insert into T1 values (1, 'row 1';);
insert into T1 values (2, 'row 2');
create table T2 (col1 int, col2 char(20));
insert into T2 values (1, 'row 1 for T2');
insert into T2 values (2, 'row 2 for T2');
ij> select T.col1, T.col2 from T1 T, (select col1, col2 from T2) as M
where M.co
l1 = T.col1;
COL1 |COL2
1 |row 1
2 |row 2
Would you mind forward me your table definition, your data and the
query you were running that Derby can not handle. It will also be good to
have derby.log information as well?
Sorry for so many questions. I sincerely hope I can help you.
Thanks,
Lily
From: Pavel Bortnovskiy
To: Derby Discussion
Sent: Thu, January 20, 2011 1:03:11 PM
Subject: Fw: Can it be that Derby (in-memory) is deadlocking on this
query? (UPDATE)
the more I am working through this issue, the more I get convinced that
it's a Derby issue.
When the select statement is modified slightly (not using joining of
tables on themselves, but rather using IN), then everything works without
a hiccup:
SELECT
P1.ID ,
R1.description
P1.BOOK
P1.NOMINAL
P1.NOMINAL * R1.Factor
FROM
P_TABLE P1,
R_TABLE R1
WHERE
P1.IN_ID = R1.IN_ID AND
R1.IN_ID in (
select
R2.IN_ID
from
P_TABLE P2,
R_TABLE R2
where
P2.IN_ID = R2.IN_ID AND
P2.NOMINAL <> 0 AND
R2.IType='X'
GROUP BY R2.IN_ID
HAVING COUNT(*) >1
)
So, this leads me to believe that SELECT statements such as this causes a
problem within Derby:
select
*
from
TABLE T1,
(
select from TABLE T2
) as M
where
M. = T1.
- Forwarded by Pavel Bortnovskiy/JEFCO on 01/20/2011 03:55 PM -
From:
Pavel Bortnovskiy/JEFCO
To:
Derby Discussion
Date:
01/20/2011 01:28 PM
Subject:
Can it be that Derby (in-memory) is deadlocking on this query?
Hello:
while running my application, I noticed that when the following query
(which uses a subquery with tables joining on themselves) is executed, the
application processes 185 records and then sits indefinitely in
ResultSet.next() method:
SELECT
P1.ID ,
R1.description
P1.BOOK
P1.NOMINAL
P1.NOMINAL * R1.Factor
FROM
P_TABLE P1,
R_TABLE R1,
(
select
R2.IN_ID
from
P_TABLE P2,
R_TABLE R2
where
P2.IN_ID = R2.IN_ID AND
P2.NOMINAL <> 0 AND
R2.IType='X'
GROUP BY R2.IN_ID
HAVING COUNT(*) >1
) as MULTI
WHERE
P1.IN_ID = R1.IN_ID AND
MULTI.IN_ID = R1.IN_ID
Then I tried running AquaDataStudio with this query and it's been over 16
minutes without any results back:
However, when I run the subquery itself, it executes practically
instanteneously:
And if I replace the subquery with where R1.IN in ('P32764', 'P32765', ...
[all results from subquery]), it executes in a few ms:
I have a suspicion that
Re: Can it be that Derby (in-memory) is deadlocking on this query? (UPDATE)
Hi Pavel: Will you be able to find the exact query executing from AquaDataStudio to Derby? Will the query be in derby.log? If you have the query, do you mind executing the query in ij? Also, when the query hang, would you mind getting the thread dump? You can edit out the proprietary parts I am just thinking out loud here. I did another query for the same table as T1. It still work with my simple table. i.e. == ij> select T.col1, T.col2 from T1 T, (select col1, col2 from T1 as T2) as M wher e M.col1 = T.col1; COL1 |COL2 1 |row 1 2 |row 2 Hope this help, Lily From: Pavel Bortnovskiy To: Lily Wei Cc: Derby Discussion Sent: Thu, January 20, 2011 2:43:11 PM Subject: Re: Can it be that Derby (in-memory) is deadlocking on this query? (UPDATE) Hello, Lily: thank you for your response. while I am still trying to create a test for Derby users, your experiment is not the same. Notice that in our query we are joining on the same table. So, T2 is not a different table. It's still the same T1 table, but aliased as T2... try something like this: select * from TABLE T1, ( select T2.col from TABLE T2 ) as M where M.col = T1.col Once I succeed reproducing the error, I will show my test. What makes me suspect that there is a problem with Derby is two-fold: - our code seems to hang in ResultSet.next(), which is a call to Derby JDBCAPI implementation - accessing those in-memory tables with AquaDataStudio and running the same query never returns/succeeds (I stopped after 20 minutes of having it running, where as the subqueries themselves run in a few milliseconds). AquaDataStudio is a commercial product and I doubt that it breaks precisely at the same point and query as our code... P.
Re: Fw: Can it be that Derby (in-memory) is deadlocking on this query? (UPDATE)
On 1/20/2011 1:03 PM, Pavel Bortnovskiy wrote: the more I am working through this issue, the more I get convinced that it's a Derby issue. When the select statement is modified slightly (not using joining of tables on themselves, but rather using IN), then everything works without a hiccup: SELECT P1.ID , R1.description P1.BOOK P1.NOMINAL P1.NOMINAL * R1.Factor FROM P_TABLE P1, R_TABLE R1 WHERE P1.IN_ID = R1.IN_ID AND R1.IN_ID in ( select R2.IN_ID from P_TABLE P2, R_TABLE R2 where P2.IN_ID = R2.IN_ID AND P2.NOMINAL <> 0 AND R2.IType='X' GROUP BY R2.IN_ID HAVING COUNT(*) >1 ) So, this leads me to believe that SELECT statements such as this causes a problem within Derby: select * from *TABLE* T1, ( select from *TABLE* T2 ) as M where M. = T1. I think it would be worthwile to file an issue in Jira while you work to get a reproduction that the community can work with. Put as much information as you can in the Jira issue especially useful would be a thread dump even if the proprietary parts are edited out. You might also want to set lock timeout and deadlock timeout low [1] so that you don't have to wait for those errors if they are occurring. Generally the more information you can provide the better the community will be able to help. As for it being urgent, once you get a reproduction and confirm it as a Derby issue, I think that you will find the development community very supportive if you want to try to fix it yourself. Thanks Kathey [1] http://db.apache.org/derby/docs/10.7/devguide/cdevconcepts16400.html
