db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brett Wooldridge <brett.wooldri...@gmail.com>
Subject Re: Guidance/Help/Book/References?
Date Sat, 23 Jun 2012 10:31:57 GMT
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
<pbortnovskiy@jefferies.com> 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.

Mime
View raw message