db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4279) Statement cache deadlock
Date Sun, 27 Jun 2010 19:43:50 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4279?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Mike Matrigali updated DERBY-4279:
----------------------------------


I remember when a lot of the original Statement Cache work was being done the original multi
stress test was particularly good at showing up issues.  There is a version that I don't think
gets 
run nightly that is even more of a load - StressMulti50x59.java, it runs 50 users for 59 minutes.
Even then just passing this test once did not really prove anything as the issues often are
very
subtle and timing dependent.  Often we would run this test over and over again as machine
resources were available.  This week I will see if we can find a machine to do this but I
don't think
we have anything more than a couple of processors.

Running this test on a machine with the most possible processors would be good to verify this
fix.  

Does this fix mean that we never wait for an existing query plan?   Does anyone understand
what this means about performance for queries that take extremely long to compile.  Unfortunately
I have seen plans take many minutes to compile depending on how complex, and some customers
often "pre-load" the plans at application startup, set the cache size such that the plan is
expected to be in cache.

> Statement cache deadlock
> ------------------------
>
>                 Key: DERBY-4279
>                 URL: https://issues.apache.org/jira/browse/DERBY-4279
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.0.2.1, 10.1.3.1, 10.2.2.0, 10.3.3.0, 10.4.2.0, 10.5.1.1
>         Environment: Windows Vista
>            Reporter: Jeff Stuckman
>            Assignee: Brett Wooldridge
>             Fix For: 10.7.0.0
>
>         Attachments: Derby4279.java, patch4279.txt, stacktrace.txt
>
>
> Due to a design flaw in the statement cache, a deadlock can occur if a prepared statement
becomes out-of-date.
> I will illustrate this with the following example:
> The application is using the embedded Derby driver. The application has two threads,
and each thread uses its own connection.
> There is a table named MYTABLE with column MYCOLUMN.
> 1. A thread prepares and executes the query SELECT MYCOLUMN FROM MYTABLE. The prepared
statement is stored in the statement cache (see org.apache.derby.impl.sql.GenericStatement
for this logic)
> 2. After some time, the prepared statement becomes invalid or out-of-date for some reason
(see org.apache.derby.impl.sql.GenericPreparedStatement)
> 3. Thread 1 begins a transaction and executes LOCK TABLE MYTABLE IN EXCLUSIVE MODE
> 4. Thread 2 begins a transaction and executes SELECT MYCOLUMN FROM MYTABLE. The statement
is in the statement cache but it is out-of-date. The thread begins to recompile the statement.
To compile the statement, the thread needs a shared lock on MYTABLE. Thread 1 already has
an exclusive lock on MYTABLE. Thread 2 waits.
> 5. Thread 1 executes SELECT MYCOLUMN FROM MYTABLE. The statement is in the statement
cache but it is being compiled. Thread 1 waits on the statement's monitor.
> 6. We have a deadlock. Derby eventually detects a lock timeout, but the error message
is not descriptive. The stacks at the time of the deadlock are:
> This deadlock is unique because it can still occur in a properly designed database. You
are only safe if all of your transactions are very simple and cannot be interleaved in a sequence
that causes the deadlock, or if your particular statements do not require a table lock to
compile. (For the sake of simplicity, I used LOCK TABLE in my example, but any UPDATE statement
would fit.)

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message