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] [Commented] (DERBY-4279) Statement cache deadlock
Date Mon, 25 Jun 2012 17:19:44 GMT

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

Mike Matrigali commented on DERBY-4279:

I have not been following this issue, so not sure of the details and am not an expert in this
area of the code but here is what I think.  I assume that Derby is getting a table level "INTENT"
shared lock vs a table level "SHARED" lock.  This
is a very different thing, but for your purposes is causing the same issue.  Intent locks
conflict with other table level SHARED AND EXCLUSIVE locks but not with other
intent locks.

I think prepare gets these intent locks on the table to insure it gets a consistent view of
all the ddl associated with the table that it is compiling.  The main goal is to block other
from happening during the prepare, the assumption is that ddl and table level share and exclusive
locking is rare (obviously this assumption is not working in the case of your
application.)  I assume more people are not seeing this because most applications do not require
table level shared and exclusive access.  Someone with more expertise
is this area of the code should comment, but I wonder if we could either eliminate this lock
or make it much shorter term if we guaranteed to check if ddl had happened
during the prepare at the very end - a lot of this information is cached so I wonder if the
locks are actually doing the work I describe above or if you just need the locks
short term to consistently populate the caches.  

 Because all the information for a single table is spread across multiple catalogs one may
need to do multiple probes to get all the information for a single prepare.
An example of the kind of bug that has happened in the past is that a prepare produces a plan
that is not aware of a new index just added, and a subsequent insert using that
plan does not update the index and thus results in a corrupt database.

Sharing plans across connections was a big performance improvement added to derby based on
many customer applications and benchmarks.  Derby compile tiime is often very
slow so anything that can be done to reduce/eliminate that compile time is important. There
are a lot of applications out there that are getting performance boosts from the
shared query cache without even knowing it, and yes they may be able to get similar results
with application changes but instead now are gettting it automatically.  So I would
not support eliminating altogether, but Derby is built on modular concept.  If you wanted
to add a change that allowed derby to boot in a mode that did not have a shared
cache (while still supporting default of a shared cache), that might be a reasonable approach.
 Similar to we default to derby working disk based, but allow it to booted with
a different module that allows it to be memory based.  I know at least the disk page cache
is implemented in a module that was designed to be easily replaced, not sure about 
current state of query 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:,,,,,,
>         Environment: Windows Vista, OS X 10.5+
>            Reporter: Jeff Stuckman
>              Labels: derby_triage10_5_2
>         Attachments: Derby4279.java, client_stacktrace_activation_closed.txt, patch4279.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.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message