Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Thu, 17 Mar 2011 07:52:29 +0000 (UTC)
From: "Karl Wright (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <456783611.8457.1300348349722.JavaMail.tomcat@hel.zones.apache.org>
In-Reply-To: 
 <308707942.121.1298824360053.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] Commented: (DERBY-5073) Derby deadlocks without recourse on
 simultaneous correlated subqueries
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/DERBY-5073?page=3Dcom.atlassian=
.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1300=
7843#comment-13007843 ]=20

Karl Wright commented on DERBY-5073:
------------------------------------

Turning on query plan output had some interesting results.  The following q=
ueries are doing full table scans.  In all cases, I cannot see why the plan=
ner 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 quer=
ies appeared in my list of "long running queries" above, so they were not i=
nvolved in the 30-minute stall recorded there.  But maybe the underlying ca=
use is similar?

SELECT parentidhash FROM intrinsiclink WHERE (jobid=3D? AND linktype=3D? AN=
D parentidhash=3D? AND childidhash=3D?) OR(jobid=3D? AND linktype=3D? AND p=
arentidhash=3D? AND childidhash=3D?) OR(jobid=3D? AND linktype=3D? AND pare=
ntidhash=3D? AND childidhash=3D?) OR(jobid=3D? AND linktype=3D? AND parenti=
dhash=3D? AND childidhash=3D?) OR(jobid=3D? AND linktype=3D? AND parentidha=
sh=3D? AND childidhash=3D?) OR(jobid=3D? AND linktype=3D? AND parentidhash=
=3D? AND childidhash=3D?) OR(jobid=3D? AND linktype=3D? AND parentidhash=3D=
? AND childidhash=3D?) OR(jobid=3D? AND linktype=3D? AND parentidhash=3D? A=
ND childidhash=3D?) OR(jobid=3D? AND linktype=3D? AND parentidhash=3D? AND =
childidhash=3D?) OR(jobid=3D? AND linktype=3D? AND parentidhash=3D? AND chi=
ldidhash=3D?) OR(jobid=3D? AND linktype=3D? AND parentidhash=3D? AND childi=
dhash=3D?) OR(jobid=3D? AND linktype=3D? AND parentidhash=3D? AND childidha=
sh=3D?) OR(jobid=3D? AND linktype=3D? AND parentidhash=3D? AND childidhash=
=3D?) OR(jobid=3D? AND linktype=3D? AND parentidhash=3D? AND childidhash=3D=
?) OR(jobid=3D? AND linktype=3D? AND parentidhash=3D? AND childidhash=3D?) =
OR(jobid=3D? AND linktype=3D? AND parentidhash=3D? AND childidhash=3D?) OR(=
jobid=3D? AND linktype=3D? AND parentidhash=3D? AND childidhash=3D?) OR(job=
id=3D? AND linktype=3D? AND parentidhash=3D? AND childidhash=3D?) OR(jobid=
=3D? AND linktype=3D? AND parentidhash=3D? AND childidhash=3D?) OR(jobid=3D=
? AND linktype=3D? AND parentidhash=3D? AND childidhash=3D?)
(identical index exists: intrinsiclink: (jobid,linktype,parentidhash,childi=
dhash) )

SELECT id,distance,linktype FROM hopcount WHERE (jobid=3D? AND linktype=3D?=
 AND parentidhash=3D?) OR (jobid=3D? AND linktype=3D? AND parentidhash=3D?)
(identical index exists: hopcount: (jobid,linktype,parentidhash) )

SELECT id FROM jobqueue WHERE (jobid=3D? AND status=3D?) OR (jobid=3D? AND =
status=3D?) OR (jobid=3D? AND status=3D?) OR (jobid=3D? AND status=3D?) OR =
(jobid=3D? AND status=3D?) OR (jobid=3D? AND status=3D?)
(identical index exists: jobqueue: (jobid,status) )

SELECT parentidhash,linktype,distance FROM hopcount WHERE  (jobid=3D? AND l=
inktype=3D? AND parentidhash=3D?) OR (jobid=3D? AND linktype=3D? AND parent=
idhash=3D?) OR (jobid=3D? AND linktype=3D? AND parentidhash=3D?) OR (jobid=
=3D? AND linktype=3D? AND parentidhash=3D?) OR (jobid=3D? AND linktype=3D? =
AND parentidhash=3D?) OR (jobid=3D? AND linktype=3D? AND parentidhash=3D?) =
OR (jobid=3D? AND linktype=3D? AND parentidhash=3D?) OR (jobid=3D? AND link=
type=3D? AND parentidhash=3D?) OR (jobid=3D? AND linktype=3D? AND parentidh=
ash=3D?) OR (jobid=3D? AND linktype=3D? AND parentidhash=3D?) OR (jobid=3D?=
 AND linktype=3D? AND parentidhash=3D?) OR (jobid=3D? AND linktype=3D? AND =
parentidhash=3D?) OR (jobid=3D? AND linktype=3D? AND parentidhash=3D?) OR (=
jobid=3D? AND linktype=3D? AND parentidhash=3D?) OR (jobid=3D? AND linktype=
=3D? AND parentidhash=3D?) OR (jobid=3D? AND linktype=3D? AND parentidhash=
=3D?) OR (jobid=3D? AND linktype=3D? AND parentidhash=3D?) OR (jobid=3D? AN=
D linktype=3D? AND parentidhash=3D?) OR (jobid=3D? AND linktype=3D? AND par=
entidhash=3D?) OR (jobid=3D? AND linktype=3D? AND parentidhash=3D?)
(identical index exists: hopcount: (jobid,linktype,parentidhash) )

FWIW, all of these queries use the corresponding index when run under Postg=
reSQL.
I'm happy to create a new ticket for this problem, if appropriate.  Comment=
s?


> 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.di=
ff
>
>
> When the following two queries are run against tables that contain the ne=
cessary fields, using multiple threads, Derby deadlocks and none of the que=
ries ever returns.  Derby apparently detects no deadlock condition, either.
> SELECT t0.* FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM carrydown t1 WH=
ERE t1.parentidhash IN (?) AND t1.childidhash=3Dt0.dochash AND t0.jobid=3Dt=
1.jobid) AND t0.jobid=3D?
> SELECT t0.* FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM carrydown t1 WH=
ERE t1.parentidhash IN (?) AND t1.childidhash=3Dt0.dochash AND t0.jobid=3Dt=
1.jobid AND t1.newField=3D?) AND t0.jobid=3D?
> This code comes from Apache ManifoldCF, and has occurred when there are f=
ive 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