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 Wed, 16 Mar 2011 23:36:29 GMT

    [ 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

Mime
View raw message