[
https://issues.apache.org/jira/browse/DERBY-5073?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13007741#comment-13007741
]
Karl Wright commented on DERBY-5073:
------------------------------------
Doesn't act like a very long running query; just doesn't make any progress at
all, but nevertheless still uses a CPU.
Here's the locks table now, apologies for the formatting:
ij> select * from syscs_diag.lock_table;
XID |TYPE |MODE|TABLENAME
|LOCK
NAME |STATE|TABLETYPE|LOCK&|INDEXNAME
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------
43048 |ROW |S |INTRINSICLINK
|(409
,16) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
48) |GRANT|T |1 |NULL
43704 |ROW |S |JOBQUEUE
|(65,
48) |WAIT |T |0 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,60) |GRANT|T |1 |NULL
43048 |ROW |S |INTRINSICLINK
|(409
,24) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,61) |GRANT|T |1 |NULL
43048 |ROW |S |INTRINSICLINK
|(409
,23) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,58) |GRANT|T |1 |NULL
43629 |ROW |X |JOBQUEUE
|(36,
73) |GRANT|T |11 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,59) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,62) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,63) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
60) |GRANT|T |1 |NULL
43048 |TABLE|IS |INTRINSICLINK
|Tabl
elock |GRANT|T |1 |NULL
43619 |TABLE|IX |INTRINSICLINK
|Tabl
elock |GRANT|T |3 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,53) |GRANT|T |1 |NULL
43048 |ROW |S |INTRINSICLINK
|(409
,32) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
59) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,52) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,51) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
58) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,50) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
57) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,57) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,56) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,55) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,54) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
61) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
52) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
51) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
50) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
49) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,49) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
56) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(288
,48) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
55) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
54) |GRANT|T |1 |NULL
43619 |ROW |X |JOBQUEUE
|(65,
53) |GRANT|T |1 |NULL
43629 |ROW |X |PREREQEVENTS
|(160
,11) |GRANT|T |2 |NULL
43619 |ROW |X |PREREQEVENTS
|(289
,9) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(289
,10) |GRANT|T |1 |NULL
43048 |TABLE|IX |HOPCOUNT
|Tabl
elock |GRANT|T |2 |NULL
43619 |ROW |X |PREREQEVENTS
|(289
,11) |GRANT|T |1 |NULL
43629 |ROW |X |PREREQEVENTS
|(160
,12) |GRANT|T |2 |NULL
43619 |ROW |X |PREREQEVENTS
|(289
,12) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(289
,13) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(289
,14) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(289
,15) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(289
,16) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(289
,6) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(289
,7) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(289
,8) |GRANT|T |1 |NULL
43048 |ROW |S |INTRINSICLINK
|(410
,21) |GRANT|T |1 |NULL
43048 |ROW |X |JOBQUEUE
|(46,
42) |GRANT|T |11 |NULL
43127 |ROW |S |JOBQUEUE
|(46,
42) |WAIT |T |0 |NULL
43258 |ROW |S |JOBQUEUE
|(46,
42) |WAIT |T |0 |NULL
43268 |ROW |S |JOBQUEUE
|(46,
42) |WAIT |T |0 |NULL
43283 |ROW |S |JOBQUEUE
|(46,
42) |WAIT |T |0 |NULL
43284 |ROW |S |JOBQUEUE
|(46,
42) |WAIT |T |0 |NULL
43290 |ROW |S |JOBQUEUE
|(46,
42) |WAIT |T |0 |NULL
43542 |ROW |S |JOBQUEUE
|(46,
42) |WAIT |T |0 |NULL
43629 |ROW |S |JOBQUEUE
|(46,
42) |WAIT |T |0 |NULL
43048 |ROW |X |PREREQEVENTS
|(200
,70) |GRANT|T |2 |NULL
43048 |ROW |X |PREREQEVENTS
|(200
,71) |GRANT|T |2 |NULL
43048 |TABLE|IX |CARRYDOWN
|Tabl
elock |GRANT|T |4 |NULL
43127 |TABLE|IS |CARRYDOWN
|Tabl
elock |GRANT|T |2 |NULL
43258 |TABLE|IS |CARRYDOWN
|Tabl
elock |GRANT|T |2 |NULL
43268 |TABLE|IS |CARRYDOWN
|Tabl
elock |GRANT|T |2 |NULL
43283 |TABLE|IS |CARRYDOWN
|Tabl
elock |GRANT|T |2 |NULL
43284 |TABLE|IS |CARRYDOWN
|Tabl
elock |GRANT|T |2 |NULL
43290 |TABLE|IS |CARRYDOWN
|Tabl
elock |GRANT|T |2 |NULL
43542 |TABLE|IS |CARRYDOWN
|Tabl
elock |GRANT|T |2 |NULL
43629 |TABLE|IS |CARRYDOWN
|Tabl
elock |GRANT|T |1 |NULL
43048 |TABLE|IX |PREREQEVENTS
|Tabl
elock |GRANT|T |2 |NULL
43619 |TABLE|IX |PREREQEVENTS
|Tabl
elock |GRANT|T |56 |NULL
43629 |TABLE|IX |PREREQEVENTS
|Tabl
elock |GRANT|T |2 |NULL
43048 |ROW |S |INTRINSICLINK
|(411
,24) |GRANT|T |1 |NULL
43619 |ROW |X |INTRINSICLINK
|(411
,24) |WAIT |T |0 |NULL
43048 |ROW |U |HOPCOUNT
|(52,
16) |GRANT|T |1 |NULL
43619 |ROW |X |PREREQEVENTS
|(289
,17) |GRANT|T |1 |NULL
43048 |ROW |S |INTRINSICLINK
|(408
,7) |GRANT|T |1 |NULL
43048 |ROW |S |INTRINSICLINK
|(411
,15) |GRANT|T |1 |NULL
43048 |TABLE|IX |JOBQUEUE
|Tabl
elock |GRANT|T |11 |NULL
43127 |TABLE|IS |JOBQUEUE
|Tabl
elock |GRANT|T |2 |NULL
43258 |TABLE|IS |JOBQUEUE
|Tabl
elock |GRANT|T |2 |NULL
43268 |TABLE|IS |JOBQUEUE
|Tabl
elock |GRANT|T |2 |NULL
43283 |TABLE|IS |JOBQUEUE
|Tabl
elock |GRANT|T |2 |NULL
43284 |TABLE|IS |JOBQUEUE
|Tabl
elock |GRANT|T |2 |NULL
43290 |TABLE|IS |JOBQUEUE
|Tabl
elock |GRANT|T |2 |NULL
43542 |TABLE|IS |JOBQUEUE
|Tabl
elock |GRANT|T |2 |NULL
43619 |TABLE|IX |JOBQUEUE
|Tabl
elock |GRANT|T |174 |NULL
43629 |TABLE|IX |JOBQUEUE
|Tabl
elock |GRANT|T |11 |NULL
43629 |TABLE|IS |JOBQUEUE
|Tabl
elock |GRANT|T |2 |NULL
43704 |TABLE|IS |JOBQUEUE
|Tabl
elock |GRANT|T |1 |NULL
94 rows selected
> 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
