[
https://issues.apache.org/jira/browse/DERBY-5073?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13007756#comment-13007756
]
Karl Wright commented on DERBY-5073:
------------------------------------
Hey, it looks like it unwedged itself after some 30 minutes. ManifoldCF then
complained about a lot of long-running queries. Some were long running because
they were blocked, obviously, but at least one of them is the cause.
So this is cleared up; it's not a deadlock apparently, just a bad plan.
Found a query that took more than a minute (1533032 ms): [UPDATE hopcount SET
deathmark=?,distance=? WHERE id IN(SELECT ownerid FROM hopdeletedeps t0 WHERE
((t0.jobid=? AND t0.childidhash=?)) AND EXISTS(SELECT 'x' FROM intrinsiclink t1
WHERE t1.linktype=t0.linktype AND t1.jobid=t0.jobid AND
t1.parentidhash=t0.parentidhash AND t1.childidhash=t0.childidhash))]
Parameter 0: 'D'
Parameter 1: '-1'
Parameter 2: '1300315252437'
Parameter 3: 'F1C0F5CC2ED7A5D0B4AB58A8F202F025AA3BD133'
Found a query that took more than a minute (1521069 ms): [UPDATE intrinsiclink
SET isnew=? WHERE jobid=? AND linktype=? AND parentidhash=? AND childidhash=?]
Parameter 0: 'E'
Parameter 1: '1300315252437'
Parameter 2: 'link'
Parameter 3: '5AD2BBC595BA81D08B9C0FBF7C435692E255154B'
Parameter 4: 'F75CCB0A5B55E281A506712D331CDD44F0D10F04'
Found a query that took more than a minute (1531593 ms): [SELECT
t0.id,t0.dochash,t0.docid FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM
carrydown t1 WHERE parentidhash IN (?) AND t1.childidhash=t0.dochash AND
t0.jobid=t1.jobid AND t1.isnew=?) AND t0.jobid=?]
Parameter 0: 'F13204EB47B99ACE783AC62341763D4DEA64DAD3'
Parameter 1: 'B'
Parameter 2: '1300315252437'
Found a query that took more than a minute (1535056 ms): [SELECT
t0.id,t0.dochash,t0.docid FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM
carrydown t1 WHERE parentidhash IN (?) AND t1.childidhash=t0.dochash AND
t0.jobid=t1.jobid AND t1.isnew=?) AND t0.jobid=?]
Parameter 0: 'C4FA1B2511EB16B47891A614290D7EFDC542EFB3'
Parameter 1: 'B'
Parameter 2: '1300315252437'
Found a query that took more than a minute (1529958 ms): [SELECT
t0.id,t0.dochash,t0.docid FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM
carrydown t1 WHERE parentidhash IN (?) AND t1.childidhash=t0.dochash AND
t0.jobid=t1.jobid AND t1.isnew=?) AND t0.jobid=?]
Parameter 0: '01A734A295128E67F823F4371F084312EF9DBDFD'
Parameter 1: 'B'
Parameter 2: '1300315252437'
Found a query that took more than a minute (1529928 ms): [SELECT
t0.id,t0.dochash,t0.docid FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM
carrydown t1 WHERE parentidhash IN (?) AND t1.childidhash=t0.dochash AND
t0.jobid=t1.jobid AND t1.isnew=?) AND t0.jobid=?]
Parameter 0: 'F27D1DB1C2E2927F7DD5BD7B8B613008CF0FEA52'
Parameter 1: 'B'
Parameter 2: '1300315252437'
Found a query that took more than a minute (1530203 ms): [SELECT
t0.id,t0.dochash,t0.docid FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM
carrydown t1 WHERE parentidhash IN (?) AND t1.childidhash=t0.dochash AND
t0.jobid=t1.jobid AND t1.isnew=?) AND t0.jobid=?]
Parameter 0: 'F20EB42B4BDDBD5FE94C20873B5F06DC285F04D0'
Parameter 1: 'B'
Parameter 2: '1300315252437'
Found a query that took more than a minute (1514744 ms): [SELECT
t0.id,t0.dochash,t0.docid 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=?]
Parameter 0: 'A36704A5DEB0591D5044DA8C0AF92997B8DCDA12'
Parameter 1: '1300315252437'
Found a query that took more than a minute (1526832 ms): [SELECT
t0.id,t0.dochash,t0.docid FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM
carrydown t1 WHERE parentidhash IN (?) AND t1.childidhash=t0.dochash AND
t0.jobid=t1.jobid AND t1.isnew=?) AND t0.jobid=?]
Found a query that took more than a minute (1524210 ms): [SELECT
t0.id,t0.dochash,t0.docid FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM
carrydown t1 WHERE parentidhash IN (?) AND t1.childidhash=t0.dochash AND
t0.jobid=t1.jobid AND t1.isnew=?) AND t0.jobid=?]
Parameter 0: 'F8848399A2AE8C7C613E55AAC80054ED6D4C4996'
Parameter 1: 'B'
Parameter 2: '1300315252437'
Parameter 0: '037C78E60C534C68924C36E80D6A469B7371987C'
Parameter 1: 'B'
Parameter 2: '1300315252437'
Found a query that took more than a minute (1627441 ms): [UPDATE hopcount SET
deathmark=?,distance=? WHERE id IN(SELECT ownerid FROM hopdeletedeps t0 WHERE
((t0.jobid=? AND t0.childidhash=?)) AND EXISTS(SELECT 'x' FROM intrinsiclink t1
WHERE t1.linktype=t0.linktype AND t1.jobid=t0.jobid AND
t1.parentidhash=t0.parentidhash AND t1.childidhash=t0.childidhash AND
t1.isnew=?))]
Found a query that took more than a minute (1627444 ms): [UPDATE hopcount SET
deathmark=?,distance=? WHERE id IN(SELECT ownerid FROM hopdeletedeps t0 WHERE
((t0.jobid=? AND t0.childidhash=?)) AND EXISTS(SELECT 'x' FROM intrinsiclink t1
WHERE t1.linktype=t0.linktype AND t1.jobid=t0.jobid AND
t1.parentidhash=t0.parentidhash AND t1.childidhash=t0.childidhash))]
Parameter 0: 'D'
Parameter 0: 'D'
Parameter 1: '-1'
Parameter 1: '-1'
Parameter 2: '1300315252437'
Parameter 2: '1300315252437'
Parameter 3: '01A734A295128E67F823F4371F084312EF9DBDFD'
Parameter 3: 'A36704A5DEB0591D5044DA8C0AF92997B8DCDA12'
Parameter 4: 'B'
Found a query that took more than a minute (1627445 ms): [UPDATE hopcount SET
deathmark=?,distance=? WHERE id IN(SELECT ownerid FROM hopdeletedeps t0 WHERE
((t0.jobid=? AND t0.childidhash=?)) AND EXISTS(SELECT 'x' FROM intrinsiclink t1
WHERE t1.linktype=t0.linktype AND t1.jobid=t0.jobid AND
t1.parentidhash=t0.parentidhash AND t1.childidhash=t0.childidhash AND
t1.isnew=?))]
Parameter 0: 'D'
Parameter 1: '-1'
Parameter 2: '1300315252437'
Parameter 3: 'F8848399A2AE8C7C613E55AAC80054ED6D4C4996'
Parameter 4: 'B'
Found a query that took more than a minute (1627427 ms): [UPDATE hopcount SET
deathmark=?,distance=? WHERE id IN(SELECT ownerid FROM hopdeletedeps t0 WHERE
((t0.jobid=? AND t0.childidhash=?)) AND EXISTS(SELECT 'x' FROM intrinsiclink t1
WHERE t1.linktype=t0.linktype AND t1.jobid=t0.jobid AND
t1.parentidhash=t0.parentidhash AND t1.childidhash=t0.childidhash AND
t1.isnew=?))]
Parameter 0: 'D'
Parameter 1: '-1'
Parameter 2: '1300315252437'
Parameter 3: 'F27D1DB1C2E2927F7DD5BD7B8B613008CF0FEA52'
Parameter 4: 'B'
Found a query that took more than a minute (1627444 ms): [UPDATE hopcount SET
deathmark=?,distance=? WHERE id IN(SELECT ownerid FROM hopdeletedeps t0 WHERE
((t0.jobid=? AND t0.childidhash=?)) AND EXISTS(SELECT 'x' FROM intrinsiclink t1
WHERE t1.linktype=t0.linktype AND t1.jobid=t0.jobid AND
t1.parentidhash=t0.parentidhash AND t1.childidhash=t0.childidhash AND
t1.isnew=?))]
Parameter 0: 'D'
Parameter 1: '-1'
Parameter 2: '1300315252437'
Parameter 3: 'F13204EB47B99ACE783AC62341763D4DEA64DAD3'
Parameter 4: 'B'
Found a query that took more than a minute (1627445 ms): [UPDATE hopcount SET
deathmark=?,distance=? WHERE id IN(SELECT ownerid FROM hopdeletedeps t0 WHERE
((t0.jobid=? AND t0.childidhash=?)) AND EXISTS(SELECT 'x' FROM intrinsiclink t1
WHERE t1.linktype=t0.linktype AND t1.jobid=t0.jobid AND
t1.parentidhash=t0.parentidhash AND t1.childidhash=t0.childidhash AND
t1.isnew=?))]
Parameter 0: 'D'
Parameter 1: '-1'
Parameter 2: '1300315252437'
Parameter 3: 'C4FA1B2511EB16B47891A614290D7EFDC542EFB3'
Parameter 4: 'B'
Found a query that took more than a minute (1627429 ms): [UPDATE hopcount SET
deathmark=?,distance=? WHERE id IN(SELECT ownerid FROM hopdeletedeps t0 WHERE
((t0.jobid=? AND t0.childidhash=?)) AND EXISTS(SELECT 'x' FROM intrinsiclink t1
WHERE t1.linktype=t0.linktype AND t1.jobid=t0.jobid AND
t1.parentidhash=t0.parentidhash AND t1.childidhash=t0.childidhash AND
t1.isnew=?))]
Parameter 0: 'D'
Parameter 1: '-1'
Parameter 2: '1300315252437'
Parameter 3: '037C78E60C534C68924C36E80D6A469B7371987C'
Parameter 4: 'B'
Found a query that took more than a minute (3095263 ms): [SELECT
jobid,CAST(COUNT(dochash) AS BIGINT) AS doccount FROM jobqueue t1 GROUP BY
jobid]
Found a query that took more than a minute (1622113 ms): [SELECT
t0.id,t0.dochash,t0.docid FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM
carrydown t1 WHERE parentidhash IN (?) AND t1.childidhash=t0.dochash AND
t0.jobid=t1.jobid AND t1.isnew=?) AND t0.jobid=?]
Parameter 0: '035F0D4F860F38F039F3C0D4D642D0CA2F83D349'
Parameter 1: 'B'
Parameter 2: '1300315252437'
Found a query that took more than a minute (1627657 ms): [SELECT
t0.id,t0.dochash,t0.docid FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM
carrydown t1 WHERE parentidhash IN (?) AND t1.childidhash=t0.dochash AND
t0.jobid=t1.jobid AND t1.isnew=?) AND t0.jobid=?]
Parameter 0: 'F75CCB0A5B55E281A506712D331CDD44F0D10F04'
Parameter 1: 'B'
Parameter 2: '1300315252437'
Found a query that took more than a minute (1622790 ms): [SELECT
t0.id,t0.dochash,t0.docid FROM jobqueue t0 WHERE EXISTS(SELECT 'x' FROM
carrydown t1 WHERE parentidhash IN (?) AND t1.childidhash=t0.dochash AND
t0.jobid=t1.jobid AND t1.isnew=?) AND t0.jobid=?]
Parameter 0: '01595CFF50BEE46E068F5AEBB539483F73C672AC'
Parameter 1: 'B'
Parameter 2: '1300315252437'
> 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
