[
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 ddl
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: 10.0.2.1, 10.1.3.1, 10.2.2.0, 10.3.3.0, 10.4.2.0,
> 10.5.1.1, 10.8.1.2
> 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, 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.
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
