[
https://issues.apache.org/jira/browse/DERBY-5073?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13007843#comment-13007843
]
Karl Wright commented on DERBY-5073:
------------------------------------
Turning on query plan output had some interesting results. The following
queries are doing full table scans. In all cases, I cannot see why the planner
does not use an existing index that was created specifically to support the
query. The other interesting thing to note is that NONE of these queries
appeared in my list of "long running queries" above, so they were not involved
in the 30-minute stall recorded there. But maybe the underlying cause is
similar?
SELECT parentidhash FROM intrinsiclink WHERE (jobid=? AND linktype=? AND
parentidhash=? AND childidhash=?) OR(jobid=? AND linktype=? AND parentidhash=?
AND childidhash=?) OR(jobid=? AND linktype=? AND parentidhash=? AND
childidhash=?) OR(jobid=? AND linktype=? AND parentidhash=? AND childidhash=?)
OR(jobid=? AND linktype=? AND parentidhash=? AND childidhash=?) OR(jobid=? AND
linktype=? AND parentidhash=? AND childidhash=?) OR(jobid=? AND linktype=? AND
parentidhash=? AND childidhash=?) OR(jobid=? AND linktype=? AND parentidhash=?
AND childidhash=?) OR(jobid=? AND linktype=? AND parentidhash=? AND
childidhash=?) OR(jobid=? AND linktype=? AND parentidhash=? AND childidhash=?)
OR(jobid=? AND linktype=? AND parentidhash=? AND childidhash=?) OR(jobid=? AND
linktype=? AND parentidhash=? AND childidhash=?) OR(jobid=? AND linktype=? AND
parentidhash=? AND childidhash=?) OR(jobid=? AND linktype=? AND parentidhash=?
AND childidhash=?) OR(jobid=? AND linktype=? AND parentidhash=? AND
childidhash=?) OR(jobid=? AND linktype=? AND parentidhash=? AND childidhash=?)
OR(jobid=? AND linktype=? AND parentidhash=? AND childidhash=?) OR(jobid=? AND
linktype=? AND parentidhash=? AND childidhash=?) OR(jobid=? AND linktype=? AND
parentidhash=? AND childidhash=?) OR(jobid=? AND linktype=? AND parentidhash=?
AND childidhash=?)
(identical index exists: intrinsiclink:
(jobid,linktype,parentidhash,childidhash) )
SELECT id,distance,linktype FROM hopcount WHERE (jobid=? AND linktype=? AND
parentidhash=?) OR (jobid=? AND linktype=? AND parentidhash=?)
(identical index exists: hopcount: (jobid,linktype,parentidhash) )
SELECT id FROM jobqueue WHERE (jobid=? AND status=?) OR (jobid=? AND status=?)
OR (jobid=? AND status=?) OR (jobid=? AND status=?) OR (jobid=? AND status=?)
OR (jobid=? AND status=?)
(identical index exists: jobqueue: (jobid,status) )
SELECT parentidhash,linktype,distance FROM hopcount WHERE (jobid=? AND
linktype=? AND parentidhash=?) OR (jobid=? AND linktype=? AND parentidhash=?)
OR (jobid=? AND linktype=? AND parentidhash=?) OR (jobid=? AND linktype=? AND
parentidhash=?) OR (jobid=? AND linktype=? AND parentidhash=?) OR (jobid=? AND
linktype=? AND parentidhash=?) OR (jobid=? AND linktype=? AND parentidhash=?)
OR (jobid=? AND linktype=? AND parentidhash=?) OR (jobid=? AND linktype=? AND
parentidhash=?) OR (jobid=? AND linktype=? AND parentidhash=?) OR (jobid=? AND
linktype=? AND parentidhash=?) OR (jobid=? AND linktype=? AND parentidhash=?)
OR (jobid=? AND linktype=? AND parentidhash=?) OR (jobid=? AND linktype=? AND
parentidhash=?) OR (jobid=? AND linktype=? AND parentidhash=?) OR (jobid=? AND
linktype=? AND parentidhash=?) OR (jobid=? AND linktype=? AND parentidhash=?)
OR (jobid=? AND linktype=? AND parentidhash=?) OR (jobid=? AND linktype=? AND
parentidhash=?) OR (jobid=? AND linktype=? AND parentidhash=?)
(identical index exists: hopcount: (jobid,linktype,parentidhash) )
FWIW, all of these queries use the corresponding index when run under
PostgreSQL.
I'm happy to create a new ticket for this problem, if appropriate. Comments?
> 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
