Hi Pavel, Unfortunately, I'm not writing with any answers. My company's software also makes use of Derby in a high-concurrency setting, and we have encountered similar issues. For us, issues around dead-locks when simply preparing statements has been a major pain. We have indeed had to put in code-level synchronization in various places to try to deal with the issues.
Unfortunately, I think you've run into bug 4279... https://issues.apache.org/jira/browse/DERBY-4279 "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.)" I had proposed a patch to 4279, but ultimately had to abandon it due to complex and non-obvious synchronization issues. Maybe I'll take another cut at it... -Brett On Sat, Jun 23, 2012 at 1:18 AM, Pavel Bortnovskiy <[email protected]> wrote: > Hello, all: > > > > Derby is used heavily in my project and its tables are frequently accessed > concurrently by multiple threads. Some threads update one or several tables, > while other threads perform run select statements against those. I’ve > written to this group several times whenever errors occurred, but some of > those message have either been ignored or contained references to fairly > short explanations on the website. So, the problems continue and it is > difficult to find the proper solution. > > > > All problems happen due to the concurrent access to tables. For instance, > today’s problem had to do with one thread updating one table (the table is > first truncated and then re-populated to stay in sync with the source of > data), while the other thread was trying to create a PreparedStatement. So, > while one thread was truncating a table, another threw this exception > (probably caused by nested select statements): > > > > java.sql.SQLException: The conglomerate (1,280) requested does not exist. > > at > org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown > Source) > > at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown > Source) > > at > org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown > Source) > > at > org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown > Source) > > at > org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) > > at > org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) > > at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown > Source) > > at > org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source) > > at > org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source) > > at > org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source) > > at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown > Source) > > at > org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) > > at > org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) > > at > com.jefco.fi.commons.database.SqlSyntaxStatement.<init>(SqlSyntaxStatement.java:76) > > at > com.jefco.fi.commons.database.SqlSyntaxStatement.<init>(SqlSyntaxStatement.java:44) > > ... > > Caused by: java.sql.SQLException: The conglomerate (1,280) requested does > not exist. > > at > org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown > Source) > > at > org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown > Source) > > ... 19 more > > Caused by: ERROR XSAI2: The conglomerate (1,280) requested does not exist. > > at > org.apache.derby.iapi.error.StandardException.newException(Unknown Source) > > at > org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.readConglomerate(Unknown > Source) > > at > org.apache.derby.impl.store.access.RAMAccessManager.conglomCacheFind(Unknown > Source) > > at > org.apache.derby.impl.store.access.RAMTransaction.findExistingConglomerate(Unknown > Source) > > at > org.apache.derby.impl.store.access.RAMTransaction.getStaticCompiledConglomInfo(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.BaseJoinStrategy.fillInScanArgs1(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.HashJoinStrategy.getScanArgs(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.FromBaseTable.getScanArguments(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.FromBaseTable.generateResultSet(Unknown > Source) > > at org.apache.derby.impl.sql.compile.FromBaseTable.generate(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown > Source) > > at org.apache.derby.impl.sql.compile.GroupByNode.generate(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.SubqueryNode.generateExpression(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.BinaryOperatorNode.generateExpression(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown > Source) > > at org.apache.derby.impl.sql.compile.UnionNode.generate(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.NormalizeResultSetNode.generate(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown > Source) > > at org.apache.derby.impl.sql.compile.DistinctNode.generate(Unknown > Source) > > at org.apache.derby.impl.sql.compile.UnionNode.generate(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.NormalizeResultSetNode.generate(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(Unknown > Source) > > at org.apache.derby.impl.sql.compile.DistinctNode.generate(Unknown > Source) > > at > org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(Unknown > Source) > > at org.apache.derby.impl.sql.compile.CursorNode.generate(Unknown > Source) > > at org.apache.derby.impl.sql.compile.StatementNode.generate(Unknown > Source) > > at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown > Source) > > at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown > Source) > > at > org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown > Source) > > ... 13 more > > > > Sometimes I get these: > > > > java.sql.SQLTransactionRollbackException: A lock could not be obtained > within the time requested > > at > org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown > Source) > > at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown > Source) > > at > org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown > Source) > > at > org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown > Source) > > at > org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) > > at > org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) > > at > org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source) > > at > org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown > Source) > > at org.apache.derby.impl.jdbc.EmbedPreparedStatement.execute(Unknown > Source) > > at > com.jefco.fi.commons.database.SqlSyntaxStatement.execute(SqlSyntaxStatement.java:296) > > at > com.jefco.fi.commons.database.DatabaseTable.truncate(DatabaseTable.java:547) > > ... > > Caused by: java.sql.SQLException: A lock could not be obtained within the > time requested > > at > org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown > Source) > > at > org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown > Source) > > ... 14 more > > Caused by: ERROR 40XL1: A lock could not be obtained within the time > requested > > at > org.apache.derby.iapi.error.StandardException.newException(Unknown Source) > > at > org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(Unknown > Source) > > at > org.apache.derby.impl.services.locks.AbstractPool.lockObject(Unknown Source) > > at > org.apache.derby.impl.services.locks.ConcurrentPool.lockObject(Unknown > Source) > > at > org.apache.derby.impl.store.raw.xact.ContainerLocking3.lockContainer(Unknown > Source) > > at > org.apache.derby.impl.store.raw.data.BaseContainerHandle.useContainer(Unknown > Source) > > at > org.apache.derby.impl.store.raw.data.BaseDataFileFactory.openContainer(Unknown > Source) > > at > org.apache.derby.impl.store.raw.data.BaseDataFileFactory.openContainer(Unknown > Source) > > at org.apache.derby.impl.store.raw.xact.Xact.openContainer(Unknown > Source) > > at > org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.init(Unknown > Source) > > at org.apache.derby.impl.store.access.heap.Heap.open(Unknown Source) > > at > org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(Unknown > Source) > > at > org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(Unknown > Source) > > at > org.apache.derby.impl.sql.execute.DDLConstantAction.lockTableForDDL(Unknown > Source) > > at > org.apache.derby.impl.sql.execute.AlterTableConstantAction.executeConstantActionBody(Unknown > Source) > > at > org.apache.derby.impl.sql.execute.AlterTableConstantAction.executeConstantAction(Unknown > Source) > > at org.apache.derby.impl.sql.execute.MiscResultSet.open(Unknown > Source) > > at > org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown > Source) > > at > org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source) > > > > Sometimes it takes an extraordinary long time to walk through the ResultSet > (literally each next() takes several minutes). > > > > I must resolve all these errors ASAP and I would like to ask some guidance > as to how to do it most properly. I don’t think that writing my own > synchronizing or locking algorithms is the right way to go. > > > > Perhaps you could recommend some books (on Derby or even general DB) which > discuss well and in detail all the locking mechanisms and proper way of > interfacing Java with a database in an environment with a highly and > frequently concurrent access to the tables. > > > > Any of your help would be greatly appreciated, > > > > Regards, > > Pavel. > > Jefferies archives and monitors outgoing and incoming e-mail. The contents > of this email, including any attachments, are confidential to the ordinary > user of the email address to which it was addressed. If you are not the > addressee of this email you may not copy, forward, disclose or otherwise use > it or any part of it in any form whatsoever. This email may be produced at > the request of regulators or in connection with civil litigation. Jefferies > accepts no liability for any errors or omissions arising as a result of > transmission. Use by other than intended recipients is prohibited. In the > United Kingdom, Jefferies operates as Jefferies International Limited; > registered in England: no. 1978621; registered office: Vintners Place, 68 > Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is > authorised and regulated by the Financial Services Authority.
