Exceptions with multiple connections

[Florian Brunner <fb...@gmx.ch>]

Hi,

I'm testing various JDBC drivers. My unit tests (see 
AbstractSqlTest.java) work with the SQLite db and the SQLite JDBC driver 
from http://www.ch-werner.de/javasqlite/ (see JDBCDriverTest.java).

With Derby there seems to be a problem when a second statement tries to 
read its result set while a first statement did not commit/ rollback yet.

Using the Derby network server and the ClientDriver (see 
ClientDriverTest.java) I get the following exceptions:
---------------------------------------------------------------------------------------------
Testsuite: org.apache.derby.jdbc.ClientDriverTest
Tests run: 2, Failures: 0, Errors: 2, Time elapsed: 131.199 sec

Testcase: 
testUpdateSelectCommitSelect(org.apache.derby.jdbc.ClientDriverTest):   
 Caused an ERROR
DERBY SQL error: SQLCODE: -1, SQLSTATE: 40XL1, SQLERRMC: 40XL1
org.apache.derby.client.am.SqlException: DERBY SQL error: SQLCODE: -1, 
SQLSTATE: 40XL1, SQLERRMC: 40XL1
        at org.apache.derby.client.am.ResultSet.completeSqlca(Unknown 
Source)
        at 
org.apache.derby.client.net.NetResultSetReply.parseFetchError(Unknown 
Source)
        at 
org.apache.derby.client.net.NetResultSetReply.parseCNTQRYreply(Unknown 
Source)
        at 
org.apache.derby.client.net.NetResultSetReply.readFetch(Unknown Source)
        at org.apache.derby.client.net.ResultSetReply.readFetch(Unknown 
Source)
        at org.apache.derby.client.net.NetResultSet.readFetch_(Unknown 
Source)
        at org.apache.derby.client.am.ResultSet.flowFetch(Unknown Source)
        at org.apache.derby.client.net.NetCursor.getMoreData_(Unknown 
Source)
        at org.apache.derby.client.am.Cursor.next(Unknown Source)
        at org.apache.derby.client.am.ResultSet.nextX(Unknown Source)
        at org.apache.derby.client.am.ResultSet.next(Unknown Source)
        at common.AbstractSqlTest.assertAllFromTbl1(AbstractSqlTest.java:82)
        at 
common.AbstractSqlTest.testUpdateSelectCommitSelect(AbstractSqlTest.java:94)


Testcase: 
testUpdateSelectRollbackSelect(org.apache.derby.jdbc.ClientDriverTest):   
 Caused an ERROR
DERBY SQL error: SQLCODE: -1, SQLSTATE: 40XL1, SQLERRMC: 40XL1
org.apache.derby.client.am.SqlException: DERBY SQL error: SQLCODE: -1, 
SQLSTATE: 40XL1, SQLERRMC: 40XL1
        at org.apache.derby.client.am.ResultSet.completeSqlca(Unknown 
Source)
        at 
org.apache.derby.client.net.NetResultSetReply.parseFetchError(Unknown 
Source)
        at 
org.apache.derby.client.net.NetResultSetReply.parseCNTQRYreply(Unknown 
Source)
        at 
org.apache.derby.client.net.NetResultSetReply.readFetch(Unknown Source)
        at org.apache.derby.client.net.ResultSetReply.readFetch(Unknown 
Source)
        at org.apache.derby.client.net.NetResultSet.readFetch_(Unknown 
Source)
        at org.apache.derby.client.am.ResultSet.flowFetch(Unknown Source)
        at org.apache.derby.client.net.NetCursor.getMoreData_(Unknown 
Source)
        at org.apache.derby.client.am.Cursor.next(Unknown Source)
        at org.apache.derby.client.am.ResultSet.nextX(Unknown Source)
        at org.apache.derby.client.am.ResultSet.next(Unknown Source)
        at common.AbstractSqlTest.assertAllFromTbl1(AbstractSqlTest.java:82)
        at 
common.AbstractSqlTest.testUpdateSelectRollbackSelect(AbstractSqlTest.java:103)
---------------------------------------------------------------------------------------------

I also tried to set timeSlice to 2000, but it didn't help.

Using the embedded Derby db and the EmbeddedDriver (see 
EmbeddedDriverTest.java) I get the following exceptions:

---------------------------------------------------------------------------------------------
There were 2 errors:
1) 
testUpdateSelectCommitSelect(org.apache.derby.jdbc.EmbeddedDriverTest)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.LockSet.lockObject(Unknown Source)
        at 
org.apache.derby.impl.services.locks.SinglePool.zeroDurationlockObject(Unknown 
Source)
        at 
org.apache.derby.impl.store.raw.xact.RowLocking2nohold.lockRecordForRead(Unknown 
Source)
        at 
org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.lockPositionForRead(Unknown 
Source)
        at 
org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(Unknown 
Source)
        at 
org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(Unknown 
Source)
        at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(Unknown 
Source)
        at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(Unknown 
Source)
        at 
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown 
Source)
        at 
org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
        at common.AbstractSqlTest.assertAllFromTbl1(AbstractSqlTest.java:82)
        at 
common.AbstractSqlTest.testUpdateSelectCommitSelect(AbstractSqlTest.java:94)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
2) 
testUpdateSelectRollbackSelect(org.apache.derby.jdbc.EmbeddedDriverTest)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.LockSet.lockObject(Unknown Source)
        at 
org.apache.derby.impl.services.locks.SinglePool.zeroDurationlockObject(Unknown 
Source)
        at 
org.apache.derby.impl.store.raw.xact.RowLocking2nohold.lockRecordForRead(Unknown 
Source)
        at 
org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.lockPositionForRead(Unknown 
Source)
        at 
org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(Unknown 
Source)
        at 
org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(Unknown 
Source)
        at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(Unknown 
Source)
        at 
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(Unknown 
Source)
        at 
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown 
Source)
        at 
org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
        at common.AbstractSqlTest.assertAllFromTbl1(AbstractSqlTest.java:82)
        at 
common.AbstractSqlTest.testUpdateSelectRollbackSelect(AbstractSqlTest.java:103)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

FAILURES!!!
Tests run: 2,  Failures: 0,  Errors: 2
---------------------------------------------------------------------------------------------

Can somebody help me to get it work?

Thanks!

-Florian

Re: Exceptions with multiple connections

[Bryan Pendleton <bp...@amberpoint.com>]

> Can somebody help me to get it work?

Perhaps you are using a different transaction isolation level with
Derby than you are with the other databases you're testing, causing
you to experience lock contention in Derby but not in other databases.

http://db.apache.org/derby/docs/dev/devguide/cdevconcepts15366.html

You could try setting your program up so that it explicitly specifies
the isolation level, to avoid different results on different databases
which may have differing default isolation levels.

thanks,

bryan