db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Pavel Bortnovskiy <pbortnovs...@jefferies.com>
Subject RE: ERROR 40XL1: A lock could not be obtained within the time requested
Date Mon, 21 May 2012 15:05:39 GMT
Libor, thank you for your response.

The lock timeout occurs in this function:

    public static boolean tableExists(final Connection connection, final String tableName)
throws SQLException {
        try {
            final DatabaseMetaData databaseMetaData = connection.getMetaData();
            final ResultSet resultSet = databaseMetaData.getTables(null, null, null, new String[]{"TABLE"});
            try {
                while (resultSet.next()) {
                    if (resultSet.getString("TABLE_NAME").equalsIgnoreCase(tableName)) {
                        return true;
                    }
                }
            } finally {
                connection.commit();
                resultSet.close();
            }
            return false;
        } catch (SQLException e) {
            Statement statement = null;
            try {
                final Connection c = Components.getMemoryDb().getConnection(true);
                statement = c.createStatement();
                final String select = "SELECT * FROM SYSCS_DIAG.LOCK_TABLE";
                final ResultSet resultSet = statement.executeQuery(select);
                final int cnt = resultSet.getMetaData().getColumnCount();
                final StringBuilder builder = new StringBuilder("Results of \"" + select +
"\":");
                while (resultSet.next()) {
                    builder.append("\n\t");
                    for (int i = 1; i <= cnt; i++) {
                        if (i > 1) {
                            builder.append(",");
                        }
                        builder.append(resultSet.getObject(i));
                    }
                }
                logger.info(builder.toString());
            } finally {
                if (statement != null) {
                    statement.close();
                }
            }
            throw e;
        }
    }

There are many threads in the app (in this particular configuration 33 to be precise) which
call this function before they start populating (by doing insert and update batches) their
corresponding in-memory tables. I’ve read some of the docs that you referred me to and produced
this diagnostic information:

Results of "SELECT * FROM SYSCS_DIAG.LOCK_TABLE":
                814,ROW,S,SYSCOLUMNS,(1,16),GRANT,S,2,null
                814,ROW,S,SYSCOLUMNS,(1,15),GRANT,S,2,null
                814,ROW,S,SYSCONGLOMERATES,(1,11),GRANT,S,2,null
                814,TABLE,IS,SYSCONGLOMERATES,Tablelock,GRANT,S,4,null
                814,ROW,S,SYSCONGLOMERATES,(1,10),GRANT,S,2,null
                814,ROW,S,SYSTABLES,(1,8),GRANT,S,2,null
                814,ROW,S,SYSCONGLOMERATES,(1,15),GRANT,S,2,null
                814,TABLE,IS,SYSTABLES,Tablelock,GRANT,S,4,null
                814,ROW,S,SYSCONGLOMERATES,(1,14),GRANT,S,2,null
                814,TABLE,IS,SYSDEPENDS,Tablelock,GRANT,S,2,null
                814,ROW,S,SYSTABLES,(1,10),GRANT,S,2,null
                814,ROW,X,SYSSTATEMENTS,(15,8),GRANT,S,3,null
                770,ROW,S,SYSSTATEMENTS,(15,8),WAIT,S,0,null
                814,ROW,S,SYSCOLUMNS,(1,29),GRANT,S,2,null
                814,ROW,S,SYSCOLUMNS,(1,30),GRANT,S,2,null
                814,ROW,S,SYSCOLUMNS,(1,31),GRANT,S,2,null
                814,TABLE,IX,SYSSTATEMENTS,Tablelock,GRANT,S,3,null
                770,TABLE,IS,SYSSTATEMENTS,Tablelock,GRANT,S,2,null
                814,ROW,S,SYSCONGLOMERATES,(1,19),GRANT,S,2,null
                814,TABLE,IS,SYSCOLUMNS,Tablelock,GRANT,S,4,null
                814,ROW,S,SYSCONGLOMERATES,(1,17),GRANT,S,2,null
                814,ROW,S,SYSCOLUMNS,(1,17),GRANT,S,2,null
                814,ROW,S,SYSCOLUMNS,(1,18),GRANT,S,2,null
                814,ROW,S,SYSCOLUMNS,(1,19),GRANT,S,2,null

Could you (or anyone else on this discussion forum) help me shed some light as to where the
problem may exist and how to remedy it?

Much appreciated,
Pavel.

From: Libor Jelinek [mailto:ljelinek@virtage.com]
Sent: Sunday, May 20, 2012 3:51 PM
To: Derby Discussion
Subject: Re: ERROR 40XL1: A lock could not be obtained within the time requested

Hard to say without deeper analysis but I guess that it can be caused by two simultaneously
running transaction in which first one is waiting to second one to get finished (to release
a lock) but reserved timeout already passed (effectively it means a deadlock).

Detailed description this can be found in in Dev Guide at http://db.apache.org/derby/docs/10.8/devguide/cdevconcepts28436.html.

Libor
On Fri, May 18, 2012 at 6:07 PM, Pavel Bortnovskiy <pbortnovskiy@jefferies.com<mailto:pbortnovskiy@jefferies.com>>
wrote:
What’s the cause of this error and how can it be avoided?

Here is a snapshot of the stack trace:

[cid:image001.png@01CD3741.78ED1E20]

And here is the text copy:

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.executeQuery(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedDatabaseMetaData.getTables(Unknown Source)
        at SqlSyntaxStatement.tableExists(SqlSyntaxStatement.java:402)
        at DatabaseTable.exists(DatabaseTable.java:85)
        at DatabaseTable.create(DatabaseTable.java:160)
        at DataSource.createMemoryDatabaseTable(DataSource.java:134)
        at FileDataSource.run(FileDataSource.java:275)
        at java.lang.Thread.run(Thread.java:619)
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)
        ... 16 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.RowLocking3.lockRecordForRead(Unknown Source)
        at org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown Source)
        at org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown Source)
        at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3.lockRowOnPage(Unknown
Source)
        at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3._lockScanRow(Unknown
Source)
        at org.apache.derby.impl.store.access.btree.index.B2IRowLockingRR.lockScanRow(Unknown
Source)
        at org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows(Unknown Source)
        at org.apache.derby.impl.store.access.btree.BTreeScan.fetchNext(Unknown Source)
        at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIndexMinion(Unknown
Source)
        at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIndex(Unknown
Source)
        at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSPSDescriptorIndex1Scan(Unknown
Source)
        at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSPSDescriptor(Unknown Source)
        at org.apache.derby.impl.sql.compile.ExecSPSNode.bindStatement(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.GenericPreparedStatement.rePrepare(Unknown Source)
        at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
        at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
        ... 10 more
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.

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