db-derby-dev mailing list archives

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

    [ 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

Mime
View raw message