db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-5073) Derby deadlocks without recourse on simultaneous correlated subqueries
Date Thu, 17 Mar 2011 09:24:29 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5073?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13007854#comment-13007854
] 

Knut Anders Hatlen commented on DERBY-5073:
-------------------------------------------

Thanks for verifying that the infinite waiting has been fixed, Karl. I think it would be better
to file a separate issue for investigating the bad plan. I'm not sure we have any good way
of using indexes for the above query currently, though. Except doing a range scan of the index
using the smallest and largest value of (jobid,linktype,parentidhash) in the supplied list
of predicates, but that may very well degenerate to a full scan in many cases, depending on
the actual values. We do have something called multi-probe scans that might be useful for
such queries, but it is my understanding that they are only used for single-column keys.

One quick check that might indicate whether the problem is the optimizer or we're just lacking
the proper access methods to use the indexes efficiently, would be to add an optimizer override
to force the use of the index - http://db.apache.org/derby/docs/10.7/tuning/ctundepthoptover.html.

You may also want to make sure that the optimizer has up to date statistics for the indexes
by calling SYSCS_UTIL.SYSCS_UPDATE_STATISTICS on the tables involved in the query - http://db.apache.org/derby/docs/10.7/ref/rrefupdatestatsproc.html.

> Derby deadlocks without recourse on simultaneous correlated subqueries
> ----------------------------------------------------------------------
>
>                 Key: DERBY-5073
>                 URL: https://issues.apache.org/jira/browse/DERBY-5073
>             Project: Derby
>          Issue Type: Bug
>          Components: Services
>    Affects Versions: 10.0.2.1, 10.1.2.1, 10.2.2.0, 10.3.3.0, 10.4.2.0, 10.5.3.0, 10.6.2.1,
10.7.1.1, 10.8.0.0
>            Reporter: Karl Wright
>         Attachments: Derby5073.java, derby-5073-1a.diff, derby-5073-1b.diff
>
>
> When the following two queries are run against tables that contain the necessary fields,
using multiple threads, Derby deadlocks and none of the queries ever returns.  Derby apparently
detects no deadlock condition, either.
> SELECT t0.* FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM carrydown t1 WHERE t1.parentidhash
IN (?) AND t1.childidhash=t0.dochash AND t0.jobid=t1.jobid) AND t0.jobid=?
> SELECT t0.* FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM carrydown t1 WHERE t1.parentidhash
IN (?) AND t1.childidhash=t0.dochash AND t0.jobid=t1.jobid AND t1.newField=?) AND t0.jobid=?
> This code comes from Apache ManifoldCF, and has occurred when there are five or more
threads trying to execute these two queries at the same time.  Originally we found this on
10.5.3.0.  It was hoped that 10.7.1.1 would fix the problem, but it hasn't.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message