Thank you for the detailed analysis, Jeff! I think your analysis sounds reasonable. I suggest you file this as a bug in JIRA, even if we have no repro yet. You could invalidate the prepared statement by dropping (maybe even adding) an index from/to the table, I think.
Thanks, Dag Jeff Stuckman <[email protected]> writes: > Hello, > > I have spent the past three days trying to find the cause of a seemingly > "impossible" deadlock in my Derby application. This was hard to debug > because the deadlock only occurs under heavy load. I think I have found the > cause -- 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: > > Thread 1: > java.lang.Object.wait(Native Method) > java.lang.Object.wait(Object.java:485) > org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) > org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) > org.apache.derby.impl.sql.GenericPreparedStatement.rePrepare(Unknown Source) > org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source) > org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source) > org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown > Source) > org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(Unknown > Source) > > Thread 2 (some frames omitted): > org.apache.derby.impl.services.locks.ActiveLock.waitForGrant(Unknown Source) > org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(Unknown > Source) > org.apache.derby.impl.services.locks.AbstractPool.lockObject(Unknown Source) > ..... > org.apache.derby.impl.sql.compile.ResultColumnList.generateHolderMethod(Unkn > own Source) > org.apache.derby.impl.sql.compile.FromBaseTable.getScanArguments(Unknown > Source) > ..... > org.apache.derby.impl.sql.compile.IndexToBaseRowNode.generate(Unknown > Source) > org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown > Source) > .... > org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(Un > known Source) > ..... > org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) > org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) > org.apache.derby.impl.sql.GenericPreparedStatement.rePrepare(Unknown Source) > org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source) > org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown > Source) > org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(Unknown > Source) > > I discovered an undocumented property that allows me to disable the > statement cache. Setting derby.language.statementCacheSize=0 will disable > the statement cache and prevent the deadlock, because the > GenericPreparedStatement objects are no longer shared between sessions/JDBC > connections. > > In my situation, this deadlock is impossible to prevent through careful > database design. You are only safe if all of your transactions are very > simple and cannot be interleaved in a sequence that causes the deadlock. > (For the sake of simplicity, I used LOCK TABLE in my example, but any UPDATE > statement would fit.) > > I'm asking any knowledgeable parties to help me with the following: > 1. Read my deadlock scenario and see if my analysis of the situation is > correct. > 2. Help me write a test case, so I can post this as an issue. (I'm hoping > that we can fix this, so nobody else spends 3 days debugging this like I > just did.) I'm not sure how to invalidate the cache entry so the issue can > be reproduced. > 3. If someone is familiar with the statement caching code, maybe we could > find possible solutions. Maybe we could wait until all the locks for > compilation have been acquired before "grabbing" the > GenericPreparedStatement object (compilingStatement=true). At the very > least, the statement complier could recognize the significance of the lock > timeout and suggest a solution in the exception text. > 4. Document the property derby.language.statementCacheSize > > Thanks, > Jeff > > > -- Dag H. Wanvik, staff engineer Sun Microsystems, Databases (JavaDB/Derby) Haakon VII gt. 7b, N-7485 Trondheim, Norway Tel: x43496/+47 73842196, Fax: +47 73842101
