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