db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Pavel Bortnovskiy <PBortnovs...@Jefferies.com>
Subject Re: Can it be that Derby (in-memory) is deadlocking on this query? (UPDATE)
Date Thu, 20 Jan 2011 22:43:11 GMT
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 <lilywei@yahoo.com>
To:
PBortnovskiy@Jefferies.com
Cc:
lilywei@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 <PBortnovskiy@Jefferies.com>
To: Derby Discussion <derby-user@db.apache.org>
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 <field> from TABLE T2 
     ) as M 
where 
    M.<field> = T1.<field> 

----- Forwarded by Pavel Bortnovskiy/JEFCO on 01/20/2011 03:55 PM ----- 
From: 
Pavel Bortnovskiy/JEFCO 
To: 
Derby Discussion <derby-user@db.apache.org> 
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.






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.
Mime
View raw message