[
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
