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, 02 Mar 2011 11:53:37 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5073?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13001385#comment-13001385
] 

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

The locks table at the time of a hang looks like this:

ij> select * from syscs_diag.lock_table;


XID            |TYPE |MODE|TABLENAME
303942         |ROW  |X   |JOBQUEUE

303956         |ROW  |S   |JOBQUEUE

303959         |ROW  |S   |JOBQUEUE

303962         |ROW  |S   |JOBQUEUE

304018         |ROW  |X   |JOBQUEUE

303974         |ROW  |X   |JOBQUEUE

303987         |TABLE|IX  |JOBS

303962         |ROW  |X   |JOBQUEUE

303965         |ROW  |S   |JOBQUEUE

303968         |ROW  |S   |JOBQUEUE

303971         |ROW  |S   |JOBQUEUE

303974         |ROW  |S   |JOBQUEUE

303977         |ROW  |S   |JOBQUEUE

303987         |ROW  |S   |JOBQUEUE

304018         |ROW  |S   |JOBQUEUE

303942         |TABLE|IS  |CARRYDOWN

303956         |TABLE|IS  |CARRYDOWN

303959         |TABLE|IS  |CARRYDOWN

303962         |TABLE|IS  |CARRYDOWN

303965         |TABLE|IS  |CARRYDOWN

303968         |TABLE|IS  |CARRYDOWN

303971         |TABLE|IS  |CARRYDOWN

303974         |TABLE|IS  |CARRYDOWN

303977         |TABLE|IS  |CARRYDOWN

304018         |TABLE|IS  |CARRYDOWN

303942         |TABLE|IX  |PREREQEVENTS

303956         |TABLE|IX  |PREREQEVENTS

303959         |TABLE|IX  |PREREQEVENTS

303962         |TABLE|IX  |PREREQEVENTS

303965         |TABLE|IX  |PREREQEVENTS

303968         |TABLE|IX  |PREREQEVENTS

303971         |TABLE|IX  |PREREQEVENTS

303974         |TABLE|IX  |PREREQEVENTS

303977         |TABLE|IX  |PREREQEVENTS

304018         |TABLE|IX  |PREREQEVENTS

303956         |ROW  |X   |JOBQUEUE

303977         |ROW  |X   |JOBQUEUE

303968         |ROW  |X   |JOBQUEUE

303965         |ROW  |X   |JOBQUEUE

303959         |ROW  |X   |JOBQUEUE

303971         |ROW  |X   |JOBQUEUE

303942         |ROW  |S   |JOBQUEUE

303942         |TABLE|IX  |JOBQUEUE

303942         |TABLE|IS  |JOBQUEUE

303956         |TABLE|IX  |JOBQUEUE

303956         |TABLE|IS  |JOBQUEUE

303959         |TABLE|IX  |JOBQUEUE

303959         |TABLE|IS  |JOBQUEUE

303962         |TABLE|IX  |JOBQUEUE

303962         |TABLE|IS  |JOBQUEUE

303965         |TABLE|IX  |JOBQUEUE

303965         |TABLE|IS  |JOBQUEUE

303968         |TABLE|IX  |JOBQUEUE

303968         |TABLE|IS  |JOBQUEUE

303971         |TABLE|IX  |JOBQUEUE

303971         |TABLE|IS  |JOBQUEUE

303974         |TABLE|IX  |JOBQUEUE

303974         |TABLE|IS  |JOBQUEUE

303977         |TABLE|IX  |JOBQUEUE

303977         |TABLE|IS  |JOBQUEUE

303987         |TABLE|IS  |JOBQUEUE

304018         |TABLE|IX  |JOBQUEUE

304018         |TABLE|IS  |JOBQUEUE


63 rows selected

Since the process is still active, other threads that the ones that get hung may well be active.
 I have confirmed that all blocked threads are blocked due to database, and for no other reason.



> 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
>            Reporter: Karl Wright
>
> 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